← back to Watches

database/setup-replication.sh

342 lines

#!/bin/bash

################################################################################
# PostgreSQL Read Replica Setup Script
################################################################################
# This script configures PostgreSQL streaming replication for high availability
# and read scaling.
#
# Architecture:
#   - Master (Primary): Handles all writes
#   - Replica (Standby): Handles read-only queries
#   - Asynchronous streaming replication
#
# Usage:
#   ./setup-replication.sh [master|replica]
################################################################################

set -e

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

# Configuration
POSTGRES_VERSION="14"
REPLICATION_USER="replicator"
REPLICATION_PASSWORD="${REPLICATION_PASSWORD:-$(openssl rand -base64 32)}"
MASTER_HOST="${MASTER_HOST:-localhost}"
MASTER_PORT="${MASTER_PORT:-5432}"
REPLICA_DATA_DIR="/var/lib/postgresql/${POSTGRES_VERSION}/replica"
MASTER_DATA_DIR="/var/lib/postgresql/${POSTGRES_VERSION}/main"

echo -e "${GREEN}"
cat << "EOF"
╔════════════════════════════════════════════════════════════════╗
║       PostgreSQL Streaming Replication Setup                  ║
║       High Availability & Read Scaling Configuration           ║
╚════════════════════════════════════════════════════════════════╝
EOF
echo -e "${NC}"

################################################################################
# FUNCTIONS
################################################################################

function print_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

function print_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

function print_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

function check_postgres_installed() {
    if ! command -v psql &> /dev/null; then
        print_error "PostgreSQL is not installed. Please install PostgreSQL first."
        exit 1
    fi
    print_info "PostgreSQL is installed: $(psql --version)"
}

function setup_master() {
    print_info "Setting up MASTER (Primary) server..."

    # 1. Create replication user
    print_info "Creating replication user..."
    sudo -u postgres psql << EOF
-- Create replication user
CREATE USER ${REPLICATION_USER} REPLICATION LOGIN ENCRYPTED PASSWORD '${REPLICATION_PASSWORD}';

-- Grant necessary permissions
GRANT CONNECT ON DATABASE omega_watches TO ${REPLICATION_USER};
EOF

    # 2. Configure postgresql.conf
    print_info "Configuring postgresql.conf for replication..."

    sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/conf.d/replication.conf > /dev/null << EOF
# ============================================================================
# REPLICATION CONFIGURATION - MASTER
# ============================================================================

# Enable WAL archiving for replication
wal_level = replica
max_wal_senders = 5                    # Maximum concurrent replication connections
max_replication_slots = 5              # Maximum replication slots
wal_keep_size = 1024                   # Keep 1GB of WAL segments
hot_standby = on                        # Allow queries during recovery
synchronous_commit = off                # Async replication for performance
archive_mode = on                       # Enable WAL archiving
archive_command = 'test ! -f /var/lib/postgresql/${POSTGRES_VERSION}/archive/%f && cp %p /var/lib/postgresql/${POSTGRES_VERSION}/archive/%f'

# Performance tuning for replication
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 1GB

# Monitoring
wal_log_hints = on
track_commit_timestamp = on
EOF

    # 3. Create archive directory
    print_info "Creating WAL archive directory..."
    sudo mkdir -p /var/lib/postgresql/${POSTGRES_VERSION}/archive
    sudo chown postgres:postgres /var/lib/postgresql/${POSTGRES_VERSION}/archive
    sudo chmod 700 /var/lib/postgresql/${POSTGRES_VERSION}/archive

    # 4. Configure pg_hba.conf for replication
    print_info "Configuring pg_hba.conf..."

    # Add replication connection rule
    sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/pg_hba.conf > /dev/null << EOF

# Replication connections
host    replication     ${REPLICATION_USER}     0.0.0.0/0               scram-sha-256
host    replication     ${REPLICATION_USER}     ::0/0                   scram-sha-256
EOF

    # 5. Create replication slot
    print_info "Creating replication slot..."
    sudo -u postgres psql << EOF
SELECT pg_create_physical_replication_slot('replica_slot');
EOF

    # 6. Restart PostgreSQL
    print_info "Restarting PostgreSQL..."
    sudo systemctl restart postgresql

    print_info "Master setup complete!"
    echo ""
    print_info "Replication credentials:"
    echo "  User: ${REPLICATION_USER}"
    echo "  Password: ${REPLICATION_PASSWORD}"
    echo "  Replication Slot: replica_slot"
    echo ""
    print_warn "Save these credentials securely! They are needed for replica setup."
}

function setup_replica() {
    print_info "Setting up REPLICA (Standby) server..."

    # 1. Stop PostgreSQL if running
    print_info "Stopping PostgreSQL..."
    sudo systemctl stop postgresql || true

    # 2. Backup existing data (if any)
    if [ -d "${REPLICA_DATA_DIR}" ]; then
        print_warn "Backing up existing replica data..."
        sudo mv ${REPLICA_DATA_DIR} ${REPLICA_DATA_DIR}.backup.$(date +%s)
    fi

    # 3. Create replica data directory
    print_info "Creating replica data directory..."
    sudo mkdir -p ${REPLICA_DATA_DIR}
    sudo chown postgres:postgres ${REPLICA_DATA_DIR}
    sudo chmod 700 ${REPLICA_DATA_DIR}

    # 4. Prompt for replication credentials if not set
    if [ -z "${REPLICATION_PASSWORD}" ]; then
        echo -n "Enter replication password: "
        read -s REPLICATION_PASSWORD
        echo ""
    fi

    # 5. Perform base backup from master
    print_info "Performing base backup from master (${MASTER_HOST}:${MASTER_PORT})..."
    print_warn "This may take several minutes depending on database size..."

    PGPASSWORD="${REPLICATION_PASSWORD}" sudo -u postgres pg_basebackup \
        -h ${MASTER_HOST} \
        -p ${MASTER_PORT} \
        -U ${REPLICATION_USER} \
        -D ${REPLICA_DATA_DIR} \
        -Fp \
        -Xs \
        -P \
        -R \
        -S replica_slot

    # 6. Configure replica-specific settings
    print_info "Configuring replica settings..."

    sudo tee ${REPLICA_DATA_DIR}/conf.d/replica.conf > /dev/null << EOF
# ============================================================================
# REPLICATION CONFIGURATION - REPLICA
# ============================================================================

# Hot standby mode
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s

# Recovery settings
restore_command = 'cp /var/lib/postgresql/${POSTGRES_VERSION}/archive/%f %p'
recovery_target_timeline = 'latest'

# Connection settings for replica
primary_conninfo = 'host=${MASTER_HOST} port=${MASTER_PORT} user=${REPLICATION_USER} password=${REPLICATION_PASSWORD} application_name=replica_1'
primary_slot_name = 'replica_slot'
EOF

    # 7. Create standby signal file (for PostgreSQL 12+)
    print_info "Creating standby.signal..."
    sudo touch ${REPLICA_DATA_DIR}/standby.signal
    sudo chown postgres:postgres ${REPLICA_DATA_DIR}/standby.signal

    # 8. Update PostgreSQL cluster configuration
    print_info "Updating cluster configuration..."
    sudo pg_createcluster ${POSTGRES_VERSION} replica -d ${REPLICA_DATA_DIR} || true

    # 9. Start replica
    print_info "Starting replica..."
    sudo systemctl start postgresql

    print_info "Replica setup complete!"
    echo ""
    print_info "Verifying replication status..."
    sudo -u postgres psql -p 5432 -c "SELECT * FROM pg_stat_wal_receiver;" || print_warn "Could not verify replication status"
}

function verify_replication() {
    print_info "Verifying replication setup..."

    echo ""
    print_info "Master replication status:"
    sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;" 2>/dev/null || print_warn "Not on master server"

    echo ""
    print_info "Replica replication status:"
    sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;" 2>/dev/null || print_warn "Not on replica server"

    echo ""
    print_info "Replication lag:"
    sudo -u postgres psql -c "SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;" 2>/dev/null || print_warn "Could not determine lag"
}

function create_monitoring_script() {
    print_info "Creating replication monitoring script..."

    cat > /tmp/monitor-replication.sh << 'MONITOR_EOF'
#!/bin/bash

# Replication monitoring script
echo "==================================================================="
echo "PostgreSQL Replication Status - $(date)"
echo "==================================================================="

# Check if master
echo -e "\n--- MASTER STATUS ---"
sudo -u postgres psql -c "
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;
" 2>/dev/null || echo "Not on master"

# Check if replica
echo -e "\n--- REPLICA STATUS ---"
sudo -u postgres psql -c "
SELECT
    status,
    received_lsn,
    received_tli,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    NOW() - pg_last_xact_replay_timestamp() AS replication_lag
FROM pg_stat_wal_receiver;
" 2>/dev/null || echo "Not on replica"

echo -e "\n==================================================================="
MONITOR_EOF

    chmod +x /tmp/monitor-replication.sh
    sudo mv /tmp/monitor-replication.sh /usr/local/bin/monitor-replication

    print_info "Monitoring script installed: /usr/local/bin/monitor-replication"
}

################################################################################
# MAIN SCRIPT
################################################################################

MODE="${1:-help}"

check_postgres_installed

case $MODE in
    master)
        setup_master
        create_monitoring_script
        ;;
    replica)
        setup_replica
        create_monitoring_script
        verify_replication
        ;;
    verify)
        verify_replication
        ;;
    monitor)
        create_monitoring_script
        /usr/local/bin/monitor-replication
        ;;
    help|*)
        echo "Usage: $0 [master|replica|verify|monitor]"
        echo ""
        echo "Commands:"
        echo "  master  - Setup this server as replication master"
        echo "  replica - Setup this server as replication replica"
        echo "  verify  - Verify replication status"
        echo "  monitor - Create and run monitoring script"
        echo ""
        echo "Environment variables:"
        echo "  REPLICATION_PASSWORD  - Password for replication user"
        echo "  MASTER_HOST          - Master server hostname/IP (for replica setup)"
        echo "  MASTER_PORT          - Master server port (default: 5432)"
        echo ""
        exit 1
        ;;
esac

echo ""
print_info "Done!"