PostgreSQL Logical Replication Test Environment with pg_readonly Extension

docker-compose.yml

version: '3.8'

services:
  producer:
    image: 997601596833.dkr.ecr.us-east-1.amazonaws.com/branch/postgres:17.5-20250915-ps-054be73-pg_readonly_pscale1-debian12
    container_name: pg_producer
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGDATA: /var/lib/postgresql/data
    ports:
      - "5432:5432"
    volumes:
      - producer_data:/var/lib/postgresql/data
      - ./init-db.sh:/usr/local/bin/init-db.sh
    entrypoint: ["/usr/local/bin/init-db.sh"]
    command: ["-c", "wal_level=logical", "-c", "max_replication_slots=10", "-c", "max_wal_senders=10", "-c", "shared_preload_libraries=pg_readonly", "-c", "listen_addresses=*"]
    networks:
      - pg_network

  consumer:
    image: 997601596833.dkr.ecr.us-east-1.amazonaws.com/branch/postgres:17.5-20250915-ps-054be73-pg_readonly_pscale1-debian12
    container_name: pg_consumer
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGDATA: /var/lib/postgresql/data
    ports:
      - "5433:5432"
    volumes:
      - consumer_data:/var/lib/postgresql/data
      - ./init-db.sh:/usr/local/bin/init-db.sh
    entrypoint: ["/usr/local/bin/init-db.sh"]
    command: ["-c", "shared_preload_libraries=pg_readonly", "-c", "listen_addresses=*"]
    networks:
      - pg_network

volumes:
  producer_data:
  consumer_data:

networks:
  pg_network:
    driver: bridge

init-db.sh

#!/bin/bash
set -e

# Switch to postgres user and set up ownership
chown -R postgres:postgres "$PGDATA" 2>/dev/null || true

# Initialize database if it doesn't exist
if [ ! -s "$PGDATA/PG_VERSION" ]; then
    echo "Initializing database..."

    # Create password file
    echo "$POSTGRES_PASSWORD" > /tmp/pwfile
    chown postgres:postgres /tmp/pwfile
    chmod 600 /tmp/pwfile

    runuser -u postgres -- initdb --username="$POSTGRES_USER" --pwfile=/tmp/pwfile

    # Start postgres temporarily for setup
    runuser -u postgres -- pg_ctl -D "$PGDATA" -o "-c listen_addresses=''" -w start

    # Create database if specified
    if [ "$POSTGRES_DB" != "postgres" ]; then
        runuser -u postgres -- createdb "$POSTGRES_DB"
    fi

    # Stop temporary postgres
    runuser -u postgres -- pg_ctl -D "$PGDATA" -m fast -w stop

    # Clean up password file
    rm /tmp/pwfile
fi

# Start postgres with the provided arguments as postgres user
exec runuser -u postgres -- postgres "$@"

psql

#!/bin/bash

# Database connection parameters
DB_NAME="testdb"
DB_USER="postgres"

case "$1" in
    "producer")
        if [ $# -eq 1 ]; then
            # Interactive mode when no additional arguments
            exec docker exec -it pg_producer psql -U $DB_USER -d $DB_NAME
        else
            # Non-interactive mode when running commands
            exec docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME "${@:2}"
        fi
        ;;
    "consumer")
        if [ $# -eq 1 ]; then
            # Interactive mode when no additional arguments
            exec docker exec -it pg_consumer psql -U $DB_USER -d $DB_NAME
        else
            # Non-interactive mode when running commands
            exec docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME "${@:2}"
        fi
        ;;
    *)
        echo "Usage: $0 {producer|consumer} [additional psql options]"
        echo "Examples:"
        echo "  $0 producer                    # Connect to producer database"
        echo "  $0 consumer                    # Connect to consumer database"
        echo "  $0 producer -c 'SELECT 1;'     # Run command on producer"
        echo "  $0 consumer -f script.sql      # Run SQL file on consumer"
        exit 1
        ;;
esac

send-data.sh

#!/bin/bash

set -e

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Database connection parameters
DB_NAME="testdb"
DB_USER="postgres"

log() {
    echo -e "${BLUE}[$(date '+%Y-%m-%d %H:%M:%S')]${NC} $1"
}

success() {
    echo -e "${GREEN}✓${NC} $1"
}

error() {
    echo -e "${RED}✗${NC} $1"
}

warn() {
    echo -e "${YELLOW}⚠${NC} $1"
}

# Send test data to producer
send_test_data() {
    log "Sending test data to producer..."

    for i in {1..10}; do
        docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME -c "
            INSERT INTO test_data (name, value) VALUES ('test_record_$i', $(($RANDOM % 1000)));
        "
        log "Inserted test_record_$i"
        sleep 1
    done

    success "Test data sent to producer"
}

# Verify replication
verify_replication() {
    log "Verifying replication..."

    # Give some time for replication to catch up
    sleep 5

    producer_count=$(docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM test_data;")
    consumer_count=$(docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM test_data;")

    producer_count=$(echo $producer_count | tr -d ' ')
    consumer_count=$(echo $consumer_count | tr -d ' ')

    log "Producer record count: $producer_count"
    log "Consumer record count: $consumer_count"

    if [ "$producer_count" = "$consumer_count" ]; then
        success "Replication is working correctly! Both databases have $producer_count records"
    else
        warn "Replication might be lagging. Producer: $producer_count, Consumer: $consumer_count"
        log "Waiting a bit more and rechecking..."
        sleep 10

        consumer_count=$(docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM test_data;")
        consumer_count=$(echo $consumer_count | tr -d ' ')

        if [ "$producer_count" = "$consumer_count" ]; then
            success "Replication caught up! Both databases have $producer_count records"
        else
            error "Replication is not working properly"
        fi
    fi

    # Show some sample data from both databases
    log "Sample data from producer:"
    docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME -c "SELECT * FROM test_data ORDER BY id DESC LIMIT 5;"

    log "Sample data from consumer:"
    docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME -c "SELECT * FROM test_data ORDER BY id DESC LIMIT 5;"
}

# Show replication status
show_replication_status() {
    log "Checking replication status..."

    log "Producer replication slots:"
    docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME -c "SELECT slot_name, plugin, slot_type, database, active FROM pg_replication_slots;"

    log "Consumer subscriptions:"
    docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME -c "SELECT subname, subenabled, subconninfo FROM pg_subscription;"
}

# Main execution
main() {
    log "Sending test data to producer database..."

    # Send test data
    send_test_data

    # Verify replication
    verify_replication

    success "Test data sent and replication verified!"
}

# Check if databases are running
if ! docker ps | grep -q "pg_producer\|pg_consumer"; then
    warn "Docker containers don't seem to be running. Make sure to run './setup.sh' first."
    exit 1
fi

# Check if logical replication is set up
if ! docker exec pg_consumer psql -U $DB_USER -d $DB_NAME -c "SELECT 1 FROM pg_subscription WHERE subname = 'test_subscription';" | grep -q "1 row"; then
    warn "Logical replication not set up. Run './setup-logrep.sh' first."
    exit 1
fi

# Run main function
main

setup-logrep.sh

#!/bin/bash

set -e

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Database connection parameters
DB_NAME="testdb"
DB_USER="postgres"
DB_PASSWORD="postgres"

log() {
    echo -e "${BLUE}[$(date '+%Y-%m-%d %H:%M:%S')]${NC} $1"
}

success() {
    echo -e "${GREEN}✓${NC} $1"
}

error() {
    echo -e "${RED}✗${NC} $1"
}

warn() {
    echo -e "${YELLOW}⚠${NC} $1"
}

# Wait for database to be ready
wait_for_db() {
    local container=$1

    log "Waiting for database in $container to be ready..."

    for i in {1..30}; do
        if docker exec $container psql -U $DB_USER -d $DB_NAME -c "SELECT 1;" >/dev/null 2>&1; then
            success "Database in $container is ready"
            return 0
        fi
        warn "Waiting for database... (attempt $i/30)"
        sleep 2
    done

    error "Database in $container failed to start"
    exit 1
}

# Setup logical replication on producer
setup_producer() {
    log "Setting up producer database..."

    docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME << 'EOF'
-- Create a test table
CREATE TABLE IF NOT EXISTS test_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    value INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create publication for logical replication
DROP PUBLICATION IF EXISTS test_publication;
CREATE PUBLICATION test_publication FOR TABLE test_data;

-- Insert some initial test data
INSERT INTO test_data (name, value) VALUES
    ('initial_record_1', 100),
    ('initial_record_2', 200),
    ('initial_record_3', 300);
EOF

    success "Producer database setup completed"
}

# Setup logical replication on consumer
setup_consumer() {
    log "Setting up consumer database..."

    docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME << 'EOF'
-- Create the same table structure
CREATE TABLE IF NOT EXISTS test_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    value INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF

    # Create subscription
    docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME << EOF
-- Drop existing subscription if it exists
DROP SUBSCRIPTION IF EXISTS test_subscription;

-- Create subscription to producer
CREATE SUBSCRIPTION test_subscription
CONNECTION 'host=pg_producer port=5432 dbname=$DB_NAME user=$DB_USER password=$DB_PASSWORD'
PUBLICATION test_publication;
EOF

    success "Consumer database setup completed"
}

# Show replication status
show_replication_status() {
    log "Checking replication status..."

    log "Producer replication slots:"
    docker exec -i pg_producer psql -U $DB_USER -d $DB_NAME -c "SELECT slot_name, plugin, slot_type, database, active FROM pg_replication_slots;"

    log "Consumer subscriptions:"
    docker exec -i pg_consumer psql -U $DB_USER -d $DB_NAME -c "SELECT subname, subenabled, subconninfo FROM pg_subscription;"
}

# Verify replication setup
verify_replication_setup() {
    log "Verifying replication setup..."

    # Check if subscription exists and is enabled
    local sub_count=$(docker exec pg_consumer psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM pg_subscription WHERE subname = 'test_subscription' AND subenabled = true;")
    sub_count=$(echo $sub_count | tr -d ' ')

    if [ "$sub_count" != "1" ]; then
        error "Subscription not created or not enabled"
        return 1
    fi

    # Check if replication slot exists on producer
    local slot_count=$(docker exec pg_producer psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM pg_replication_slots WHERE slot_name = 'test_subscription';")
    slot_count=$(echo $slot_count | tr -d ' ')

    if [ "$slot_count" != "1" ]; then
        error "Replication slot not created on producer"
        return 1
    fi

    success "Replication setup verified successfully"
    return 0
}

# Main execution
main() {
    log "Setting up logical replication between producer and consumer..."

    # Wait for both databases to be ready
    wait_for_db pg_producer
    wait_for_db pg_consumer

    # Fix network connectivity first
    log "Configuring PostgreSQL for network access..."
    docker exec pg_producer bash -c "
        echo 'host all all 192.168.0.0/16 trust' >> /var/lib/postgresql/data/pg_hba.conf
        sed -i \"s/#listen_addresses = 'localhost'/listen_addresses = '*'/g\" /var/lib/postgresql/data/postgresql.conf
        sed -i \"s/listen_addresses = 'localhost'/listen_addresses = '*'/g\" /var/lib/postgresql/data/postgresql.conf
    "

    log "Restarting producer to apply network configuration..."
    docker restart pg_producer
    sleep 10  # Wait for PostgreSQL to start

    # Test connectivity
    log "Testing network connectivity..."
    if ! docker exec pg_consumer psql -h pg_producer -p 5432 -U $DB_USER -d $DB_NAME -c "SELECT 1;" >/dev/null 2>&1; then
        error "Cannot connect from consumer to producer. Network setup failed."
        exit 1
    fi
    success "Network connectivity verified"

    # Setup replication
    setup_producer
    setup_consumer

    # Verify the setup worked
    if ! verify_replication_setup; then
        error "Logical replication setup failed!"
        exit 1
    fi

    # Show status
    show_replication_status

    success "Logical replication setup completed successfully!"
    log "You can now use './send-data.sh' to test replication"
}

# Check if databases are running
if ! docker ps | grep -q "pg_producer\|pg_consumer"; then
    warn "Docker containers don't seem to be running. Make sure to run './setup.sh' first."
    exit 1
fi

# Run main function
main

setup.sh

#!/bin/bash

set -e

# Colors for output
GREEN='\033[0;32m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

log() {
    echo -e "${BLUE}[$(date '+%Y-%m-%d %H:%M:%S')]${NC} $1"
}

success() {
    echo -e "${GREEN}✓${NC} $1"
}

# ECR configuration
ECR_REGISTRY="997601596833.dkr.ecr.us-east-1.amazonaws.com"
ECR_REGION="us-east-1"
IMAGE_REPO="branch/postgres"
IMAGE_TAG="17.5-20250915-ps-054be73-pg_readonly_pscale1-debian12"
FULL_IMAGE="${ECR_REGISTRY}/${IMAGE_REPO}:${IMAGE_TAG}"

log "Setting up PostgreSQL logical replication environment..."

# Authenticate with ECR
log "Authenticating with AWS ECR..."
aws ecr get-login-password --region $ECR_REGION --profile build | docker login --username AWS --password-stdin $ECR_REGISTRY

success "ECR authentication successful"

# Pull the image
log "Pulling PostgreSQL image with pg_readonly extension..."
docker pull $FULL_IMAGE

success "Image pulled successfully"

# Start the containers
log "Starting Docker containers..."
docker-compose up -d

success "Containers started successfully"

# Wait a moment for containers to initialize
log "Waiting for containers to initialize..."
sleep 5

success "Setup complete! You can now run './send-data.sh' to test logical replication"