880aae9a77
Created comprehensive HA roadmap with 6 phases: - Phase 1: Warm standby (CURRENT - manual failover) - Phase 2: Database replication - Phase 3: Health monitoring - Phase 4: Reverse proxy + floating IP - Phase 5: Automated failover - Phase 6: Geographic redundancy Includes: - Decision gates based on capital and stability - Cost-benefit analysis - Scripts for healthcheck, failover, DB sync - Recommendation to defer full HA until capital > $5k Secondary server ready at 72.62.39.24 for emergency manual failover. Related: User concern about system uptime, but full HA complexity not justified at current scale (~$600 capital). Revisit in Q1 2026.
105 lines
3.2 KiB
Bash
105 lines
3.2 KiB
Bash
#!/bin/bash
|
|
#
|
|
# Database Replication Setup for PostgreSQL Streaming Replication
|
|
# Run on PRIMARY server first, then on SECONDARY
|
|
#
|
|
|
|
set -eu
|
|
|
|
MODE="${1:-help}"
|
|
PRIMARY_IP="192.168.1.100" # Update with primary server IP
|
|
SECONDARY_IP="72.62.39.24"
|
|
POSTGRES_PASSWORD="your_postgres_password" # Update from .env
|
|
REPLICATION_USER="replicator"
|
|
REPLICATION_PASSWORD="your_replication_password" # Generate strong password
|
|
|
|
log() {
|
|
echo "[$(date +'%Y-%m-%d %H:%M:%S')] $*"
|
|
}
|
|
|
|
setup_primary() {
|
|
log "🔧 Setting up PRIMARY database for replication..."
|
|
|
|
# Create replication user
|
|
docker exec trading-bot-postgres psql -U postgres -d trading_bot_v4 <<-EOF
|
|
-- Create replication user
|
|
CREATE USER ${REPLICATION_USER} WITH REPLICATION ENCRYPTED PASSWORD '${REPLICATION_PASSWORD}';
|
|
|
|
-- Grant necessary privileges
|
|
GRANT CONNECT ON DATABASE trading_bot_v4 TO ${REPLICATION_USER};
|
|
EOF
|
|
|
|
log "✅ Replication user created"
|
|
|
|
# Configure pg_hba.conf for replication
|
|
docker exec trading-bot-postgres bash -c "cat >> /var/lib/postgresql/data/pg_hba.conf <<EOF
|
|
# Replication connection from secondary
|
|
host replication ${REPLICATION_USER} ${SECONDARY_IP}/32 md5
|
|
EOF"
|
|
|
|
# Configure postgresql.conf for replication
|
|
docker exec trading-bot-postgres bash -c "cat >> /var/lib/postgresql/data/postgresql.conf <<EOF
|
|
# Replication settings
|
|
wal_level = replica
|
|
max_wal_senders = 3
|
|
wal_keep_size = 64
|
|
EOF"
|
|
|
|
log "✅ PostgreSQL configured for replication"
|
|
log "⚠️ Restart PostgreSQL container: docker restart trading-bot-postgres"
|
|
}
|
|
|
|
setup_secondary() {
|
|
log "🔧 Setting up SECONDARY database (replica)..."
|
|
|
|
# Stop secondary postgres if running
|
|
docker compose stop trading-bot-postgres || true
|
|
|
|
# Remove old data
|
|
log "⚠️ Removing old PostgreSQL data on secondary..."
|
|
docker volume rm traderv4_postgres-data || true
|
|
|
|
# Create base backup from primary
|
|
log "📦 Creating base backup from primary..."
|
|
docker run --rm \
|
|
-e PGPASSWORD="${REPLICATION_PASSWORD}" \
|
|
postgres:16-alpine \
|
|
pg_basebackup -h ${PRIMARY_IP} -U ${REPLICATION_USER} -D /backup -Fp -Xs -P -R
|
|
|
|
# Restore to secondary
|
|
# ... (needs volume mounting, complex - see simplified approach below)
|
|
|
|
log "✅ Secondary database configured as replica"
|
|
}
|
|
|
|
simplified_sync() {
|
|
log "📋 Simplified approach: Database sync via pg_dump/restore"
|
|
log "Run on PRIMARY to create backup:"
|
|
echo " docker exec trading-bot-postgres pg_dump -U postgres trading_bot_v4 > /tmp/trading_bot_backup.sql"
|
|
echo " rsync -avz /tmp/trading_bot_backup.sql ${SECONDARY_IP}:/tmp/"
|
|
log "Run on SECONDARY to restore:"
|
|
echo " docker exec -i trading-bot-postgres psql -U postgres trading_bot_v4 < /tmp/trading_bot_backup.sql"
|
|
}
|
|
|
|
case "$MODE" in
|
|
primary)
|
|
setup_primary
|
|
;;
|
|
secondary)
|
|
setup_secondary
|
|
;;
|
|
sync)
|
|
simplified_sync
|
|
;;
|
|
help|*)
|
|
echo "Usage: $0 {primary|secondary|sync}"
|
|
echo ""
|
|
echo "primary - Configure primary DB for replication"
|
|
echo "secondary - Configure secondary DB as replica"
|
|
echo "sync - Show commands for manual DB sync"
|
|
echo ""
|
|
echo "⚠️ IMPORTANT: Update IP addresses and passwords in script first!"
|
|
exit 1
|
|
;;
|
|
esac
|