Eve library

Yemaya PostgreSQL High Availability

Other

Revision 1

On this page

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#

text
                    ┌─────────────────┐
                    │   Application   │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │    PgBouncer    │
                    │  (connection    │
                    │    pooler)      │
                    └───┬─────────┬───┘
                        │         │
             ┌──────────▼──┐  ┌──▼──────────┐
             │   Primary   │  │   Standby   │
             │ PostgreSQL  │──│ PostgreSQL  │
             │   (R/W)     │  │   (R/O)     │
             └─────────────┘  └─────────────┘
                    │
             ┌──────▼──────┐
             │ WAL Archive │
             └─────────────┘

Quick Start#

Using Docker Compose#

bash
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#

bash
# 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 replication
  • max_wal_senders = 5 - Maximum concurrent standby connections
  • wal_keep_size = 512MB - WAL retention for standbys
  • archive_mode = on - Enable WAL archiving for PITR

standby.conf#

Standby server configuration with:

  • hot_standby = on - Allow read-only queries
  • hot_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
bash
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
bash
PRIMARY_HOST=postgres-primary \
REPLICATOR_PASSWORD=secure_password \
./setup-standby.sh

failover.sh#

Manages failover from primary to standby:

bash
# 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:

bash
# 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:

  1. Stop application traffic

    bash
    # Pause PgBouncer
    docker exec yemaya-pgbouncer pgbouncer -R
    
  2. Wait for replication to catch up

    bash
    ./check-replication.sh lag  # Should be 0
    
  3. Promote standby

    bash
    ./failover.sh promote
    
  4. Update PgBouncer configuration

    bash
    # Edit pgbouncer.ini to point to new primary
    docker restart yemaya-pgbouncer
    
  5. Resume traffic

Unplanned Failover (Emergency)#

When primary fails unexpectedly:

  1. Verify primary is unavailable

    bash
    ./failover.sh check-primary --primary-host postgres-primary
    
  2. Run failover procedure

    bash
    ./failover.sh failover --primary-host postgres-primary
    
  3. Update DNS/PgBouncer

  4. Investigate failed primary

Post-Failover Tasks#

After any failover:

  1. Update application configuration if using direct connections
  2. Set up old primary as new standby (if recovered)
  3. Review monitoring and alerting
  4. 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:

yaml
# 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#

yaml
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#

  1. Synchronous Replication for critical data:

    text
    synchronous_commit = on
    synchronous_standby_names = 'standby1'
    
  2. Multiple Standbys: Run 2+ standbys in different availability zones

  3. WAL Archiving: Enable for point-in-time recovery

    text
    archive_mode = on
    archive_command = 'aws s3 cp %p s3://bucket/wal/%f'
    
  4. Connection Pooling: Always use PgBouncer in production

  5. 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#

  1. Check network connectivity
  2. Verify pg_hba.conf allows replication
  3. Check replication slot exists
  4. Review PostgreSQL logs

High Replication Lag#

  1. Check network bandwidth
  2. Verify standby has sufficient resources
  3. Look for long-running queries on standby
  4. Check for heavy write load on primary

Standby Not Accepting Connections#

  1. Verify hot_standby = on
  2. Check if recovery is in progress
  3. Review standby logs

WAL Segments Accumulating#

  1. Check if standbys are connected
  2. Verify replication slots are active
  3. 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