PostgreSQL high availability with Patroni, etcd and HAProxy

#Architecture

          clients
             │
     HAProxy (x2 + keepalived VIP)
     ├─ :5000 → leader (read-write)
     └─ :5001 → replicas (read-only)
             │
  pg1 (Patroni) ─ pg2 (Patroni) ─ pg3 (Patroni)
        └──────── etcd cluster (3 nodes) ────────┘

Patroni manages PostgreSQL on each node, and uses etcd as the distributed configuration store (DCS) for leader election.

#1. etcd (3 nodes, separate from or together with PostgreSQL)

Use TLS between members and from clients. Three nodes tolerate one failure. Place them in three failure domains.

#2. Patroni configuration (pg1)

scope: pg-sales
name: pg1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.20.2.11:8008
etcd3:
  hosts: 10.20.2.21:2379,10.20.2.22:2379,10.20.2.23:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true          # zero data loss on automatic failover
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
  initdb: [ {encoding: UTF8}, data-checksums ]
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.20.2.11:5432
  data_dir: /var/lib/pgsql/17/data
  bin_dir: /usr/pgsql-17/bin
  authentication:
    replication: { username: replicator, password: "..." }
    superuser:   { username: postgres,   password: "..." }
tags:
  nofailover: false
  noloadbalance: false

systemctl enable --now patroni
patronictl -c /etc/patroni/patroni.yml list

#3. Synchronous mode choices

#4. HAProxy routing

listen primary
  bind *:5000
  option httpchk GET /primary
  http-check expect status 200
  default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
  server pg1 10.20.2.11:5432 check port 8008
  server pg2 10.20.2.12:5432 check port 8008
  server pg3 10.20.2.13:5432 check port 8008

listen replicas
  bind *:5001
  balance roundrobin
  option httpchk GET /replica
  server pg1 10.20.2.11:5432 check port 8008
  server pg2 10.20.2.12:5432 check port 8008
  server pg3 10.20.2.13:5432 check port 8008

Run two HAProxy nodes with keepalived for a VIP. Alternatives are a cloud load balancer, or libpq multi-host strings with target_session_attrs=read-write.

#5. Operations

patronictl list                                          # roles, lag, timeline
patronictl switchover --leader pg1 --candidate pg2       # planned
patronictl restart pg-sales pg3                          # rolling restart
patronictl edit-config                                   # change DCS-wide parameters
patronictl pause / resume                                # maintenance mode

#6. Backups still matter

Run pgBackRest from the cluster (backup-standby=y to take load off the leader), with WAL archiving from whichever node is the leader.

Warning

Never run pg_ctl promote or edit postgresql.conf by hand on Patroni nodes. Patroni will fight you. Make all changes through patronictl.