PostgreSQL High Availability with Patroni¶

Blacklake uses Patroni with etcd for PostgreSQL high availability, providing automatic failover and synchronous replication.

Architecture¶

graph TB
    subgraph "Application Layer"
        APP[Blacklake App]
        PGB[PgBouncer]
    end

    subgraph "Load Balancer"
        HA[HAProxy VIP]
    end

    subgraph "PostgreSQL Cluster"
        PG0[Patroni PG0<br/>Primary]
        PG1[Patroni PG1<br/>Replica]
    end

    subgraph "Coordination"
        ETCD[etcd DCS]
    end

    APP --> PGB
    PGB --> HA
    HA --> PG0
    HA --> PG1
    PG0 --> PG1
    PG0 --> ETCD
    PG1 --> ETCD

Components¶

Patroni¶

• Primary/Replica: Two PostgreSQL nodes with automatic failover
• Synchronous Replication: Data consistency with synchronous_mode: true
• Automatic Failover: Detects failures and promotes replica
• Configuration Management: Centralized via etcd

etcd¶

• Distributed Consensus: Coordinates Patroni nodes
• Leader Election: Determines primary node
• Configuration Storage: Stores cluster state

HAProxy¶

• Virtual IP: Provides stable endpoint for applications
• Health Checks: Monitors Patroni REST API
• Load Balancing: Routes to healthy primary

Configuration¶

Patroni Settings¶

scope: blacklake
name: pg0

restapi:
  listen: 0.0.0.0:8008
  connect_address: patroni-pg0:8008

etcd:
  host: etcd:2379

postgresql:
  use_pg_rewind: true
  use_slots: true
  parameters:
    wal_level: replica
    max_wal_senders: 20
    max_replication_slots: 20
    hot_standby: on
    archive_mode: on
    archive_command: 'pgbackrest --stanza=blacklake archive-push %p'
    shared_buffers: 1GB
    max_connections: 200
    shared_preload_libraries: 'pg_stat_statements'
    statement_timeout: 30s
    idle_in_transaction_session_timeout: 30s
    lock_timeout: 5s

synchronous_mode: true
synchronous_mode_strict: false
maximum_lag_on_failover: 16777216
ttl: 30
loop_wait: 10
retry_timeout: 10

HAProxy Configuration¶

backend postgres_servers
    balance roundrobin
    option tcp-check
    tcp-check connect port 5432
    server pg0 patroni-pg0:5432 check
    server pg1 patroni-pg1:5432 check backup

frontend postgres_frontend
    bind *:5432
    default_backend postgres_servers

Operations¶

Starting the Cluster¶

# Start the database infrastructure
docker compose -f infra/docker-compose.db.yml up -d

# Wait for cluster to be ready
docker compose -f infra/docker-compose.db.yml logs -f patroni-pg0

Checking Cluster Status¶

# Check Patroni status
curl http://patroni-pg0:8008/health
curl http://patroni-pg1:8008/health

# Check cluster state
curl http://patroni-pg0:8008/cluster

Manual Failover¶

# Trigger switchover (planned)
curl -X POST http://patroni-pg0:8008/switchover

# Force failover (emergency)
curl -X POST http://patroni-pg1:8008/failover

Monitoring¶

Health Endpoints¶

• Application: GET /health/db - Database connectivity
• HA Cluster: GET /health/ha - Patroni cluster health

Metrics¶

• PostgreSQL: http://postgres-exporter:9187/metrics
• PgBouncer: http://pgbouncer-exporter:9128/metrics

Troubleshooting¶

Common Issues¶

1. Split Brain¶

Symptoms: Both nodes think they're primary Resolution:

# Check etcd state
etcdctl get /blacklake/leader

# Force demote one node
curl -X POST http://patroni-pg1:8008/demote

2. Replication Lag¶

Symptoms: Replica falling behind Resolution:

# Check replication status
curl http://patroni-pg1:8008/replica

# Adjust synchronous_mode_strict if needed

3. etcd Connectivity¶

Symptoms: Patroni can't connect to etcd Resolution:

# Check etcd health
docker exec blacklake-etcd etcdctl endpoint health

# Restart etcd if needed
docker restart blacklake-etcd

Recovery Procedures¶

Full Cluster Recovery¶

# Stop all Patroni nodes
docker stop blacklake-patroni-pg0 blacklake-patroni-pg1

# Clear etcd state
docker exec blacklake-etcd etcdctl del --prefix /blacklake

# Restart nodes
docker start blacklake-patroni-pg0 blacklake-patroni-pg1

Single Node Recovery¶

# Rejoin node to cluster
curl -X POST http://patroni-pg1:8008/reinitialize

Production Considerations¶

Security¶

• TLS Encryption: Enable SSL for all connections
• Authentication: Use SCRAM-SHA-256
• Network Isolation: Separate database network

Performance¶

• Connection Pooling: Use PgBouncer for connection management
• Monitoring: Set up alerts for replication lag
• Backup Strategy: Regular pgBackRest backups

Scaling¶

• Read Replicas: Add additional read-only replicas
• Geographic Distribution: Multi-region deployment
• Load Balancing: Distribute read queries

Next Steps¶

• Pooling - PgBouncer configuration and best practices
• Backups - pgBackRest setup and restore procedures
• Observability - Monitoring and alerting setup