This directory contains configuration and scripts for PostgreSQL streaming replication and high availability setup.
Overview#
The high availability setup provides:
- Streaming Replication: Real-time data replication from primary to standby
- Hot Standby: Read-only queries on standby servers
- Automatic Failover Scripts: Promote standby to primary when needed
- Replication Monitoring: Health checks and alerting
Architecture#
┌─────────────────┐
│ Application │
└────────┬────────┘
│
┌────────▼────────┐
│ PgBouncer │
│ (connection │
│ pooler) │
└───┬─────────┬───┘
│ │
┌──────────▼──┐ ┌──▼──────────┐
│ Primary │ │ Standby │
│ PostgreSQL │──│ PostgreSQL │
│ (R/W) │ │ (R/O) │
└─────────────┘ └─────────────┘
│
┌──────▼──────┐
│ WAL Archive │
└─────────────┘
Quick Start#
Using Docker Compose#
cd infrastructure/postgres-ha
# Start HA cluster
docker compose -f docker-compose.ha.yml up -d
# Check status
docker compose -f docker-compose.ha.yml ps
# View logs
docker compose -f docker-compose.ha.yml logs -f postgres-primary postgres-standby
# Enable monitoring (optional)
docker compose -f docker-compose.ha.yml --profile monitoring up -d
Verify Replication#
# On primary - check connected standbys
docker exec yemaya-postgres-primary psql -U yemaya -c "SELECT * FROM pg_stat_replication;"
# On standby - verify recovery mode
docker exec yemaya-postgres-standby psql -U yemaya -c "SELECT pg_is_in_recovery();"
# Check replication lag
docker exec yemaya-postgres-standby psql -U yemaya -c "
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int AS lag_seconds;
"
Configuration Files#
primary.conf#
Primary server configuration with:
wal_level = replica- Enable WAL for replicationmax_wal_senders = 5- Maximum concurrent standby connectionswal_keep_size = 512MB- WAL retention for standbysarchive_mode = on- Enable WAL archiving for PITR
standby.conf#
Standby server configuration with:
hot_standby = on- Allow read-only querieshot_standby_feedback = on- Prevent vacuum conflicts
pg_hba.conf#
Authentication configuration allowing:
- Local connections
- Docker network connections
- Replication connections from replicator user
- Application connections
Scripts#
setup-primary.sh#
Configures a PostgreSQL server as the replication primary:
- Creates replication user
- Creates replication slot
- Sets up WAL archiving
REPLICATOR_PASSWORD=secure_password ./setup-primary.sh
setup-standby.sh#
Configures a PostgreSQL server as a streaming replica:
- Takes base backup from primary
- Configures recovery settings
- Starts streaming replication
PRIMARY_HOST=postgres-primary \
REPLICATOR_PASSWORD=secure_password \
./setup-standby.sh
failover.sh#
Manages failover from primary to standby:
# Check current status
./failover.sh status
# Check if primary is healthy
./failover.sh check-primary --primary-host postgres-primary
# Promote standby to primary
./failover.sh promote
# Full failover procedure
./failover.sh failover --primary-host postgres-primary
check-replication.sh#
Monitors replication health:
# Single health check (returns exit codes for monitoring)
./check-replication.sh check
# Continuous monitoring
./check-replication.sh monitor
# Get current replication lag
./check-replication.sh lag
Failover Procedures#
Planned Failover (Switchover)#
Use for maintenance or upgrades:
-
Stop application traffic
bash# Pause PgBouncer docker exec yemaya-pgbouncer pgbouncer -R -
Wait for replication to catch up
bash./check-replication.sh lag # Should be 0 -
Promote standby
bash./failover.sh promote -
Update PgBouncer configuration
bash# Edit pgbouncer.ini to point to new primary docker restart yemaya-pgbouncer -
Resume traffic
Unplanned Failover (Emergency)#
When primary fails unexpectedly:
-
Verify primary is unavailable
bash./failover.sh check-primary --primary-host postgres-primary -
Run failover procedure
bash./failover.sh failover --primary-host postgres-primary -
Update DNS/PgBouncer
-
Investigate failed primary
Post-Failover Tasks#
After any failover:
- Update application configuration if using direct connections
- Set up old primary as new standby (if recovered)
- Review monitoring and alerting
- Document the incident
Monitoring#
Key Metrics#
| Metric | Warning | Critical | Description |
|---|---|---|---|
| Replication Lag | > 30s | > 120s | Delay in data replication |
| WAL Senders | < 1 | = 0 | Active standby connections |
| Replay Lag (bytes) | > 16MB | > 64MB | Data not yet applied |
Prometheus Metrics#
Add to your monitoring:
# Example Prometheus queries
pg_replication_lag_seconds > 30 # Warning
pg_replication_lag_seconds > 120 # Critical
pg_stat_replication_count == 0 # No standbys connected
Alerting Example#
groups:
- name: postgres-ha
rules:
- alert: PostgresReplicationLag
expr: pg_replication_lag_seconds > 30
for: 5m
labels:
severity: warning
annotations:
summary: 'PostgreSQL replication lag is high'
- alert: PostgresNoStandby
expr: pg_stat_replication_count == 0
for: 1m
labels:
severity: critical
annotations:
summary: 'No PostgreSQL standby connected'
Best Practices#
Production Recommendations#
-
Synchronous Replication for critical data:
textsynchronous_commit = on synchronous_standby_names = 'standby1' -
Multiple Standbys: Run 2+ standbys in different availability zones
-
WAL Archiving: Enable for point-in-time recovery
textarchive_mode = on archive_command = 'aws s3 cp %p s3://bucket/wal/%f' -
Connection Pooling: Always use PgBouncer in production
-
Automated Failover: Consider Patroni or pg_auto_failover for automatic failover
Security#
- Use SSL for replication connections
- Restrict replication user permissions
- Use strong passwords
- Network segmentation between primary and standbys
Troubleshooting#
Replication Not Starting#
- Check network connectivity
- Verify pg_hba.conf allows replication
- Check replication slot exists
- Review PostgreSQL logs
High Replication Lag#
- Check network bandwidth
- Verify standby has sufficient resources
- Look for long-running queries on standby
- Check for heavy write load on primary
Standby Not Accepting Connections#
- Verify
hot_standby = on - Check if recovery is in progress
- Review standby logs
WAL Segments Accumulating#
- Check if standbys are connected
- Verify replication slots are active
- Consider dropping inactive slots:sql
SELECT pg_drop_replication_slot('inactive_slot');
Files#
| File | Description |
|---|---|
primary.conf |
Primary server PostgreSQL configuration |
standby.conf |
Standby server PostgreSQL configuration |
pg_hba.conf |
Client authentication configuration |
setup-primary.sh |
Initialize primary for replication |
setup-standby.sh |
Initialize standby from primary |
failover.sh |
Failover management script |
check-replication.sh |
Replication health monitoring |
init-primary.sh |
Docker init script for primary |
init-standby.sh |
Docker init script for standby |
docker-compose.ha.yml |
Docker Compose for HA setup |