PostgreSQL High Availability: A Production Manual for Patroni, etcd v3, HAProxy and PgBouncer

Executive Summary & Architecture Takeaways

• The Core Problem: Native PostgreSQL streaming replication has no built-in automated consensus. When an active primary crashes or drops packets during a network partition, ad-hoc failover scripts either cause split-brain (two nodes accepting writes) or fail to promote standbys automatically.
• The Reference Topology: A 3-node PostgreSQL 16 cluster orchestrated by Patroni backed by a dedicated 3-node etcd v3.5 consensus store, load-balanced via redundant HAProxy nodes using Patroni's HTTP REST API health checks, fronted by PgBouncer in transaction pooling mode.
• Recovery Targets:
  • Recovery Point Objective (RPO): 0 for committed transactions when synchronous replication is enforced (synchronous_mode with synchronous_mode_strict: true, or Patroni's quorum mode).
  • Recovery Time Objective (RTO): bounded by the leader-key TTL. With the minimum supported ttl of 20 seconds, expect unplanned failover in roughly 20 to 30 seconds; planned switchovers complete in a few seconds.
  • Throughput: depends entirely on hardware, schema and workload. Benchmark your own workload with pgbench or a replay tool before committing to numbers.

1. Why Traditional PostgreSQL HA Fails in Production

Many teams attempt to assemble PostgreSQL high availability using a mixture of DNS round-robin, floating virtual IPs (Keepalived), and custom bash heartbeat scripts. This setup tends to fail for three reasons:

1.1 The Split-Brain Disaster

If node 1 (Primary) experiences a transient 15-second kernel lockup or network drop, the monitoring script declares it dead and promotes node 2 (Standby). Seconds later, node 1 recovers and continues accepting writes from stale connection pools. You now have two primaries with diverging WAL histories (for example, one at LSN 0/19000000 and the other at 0/1A000000). Reconciling divergent write histories manually requires downtime and data forensics.

1.2 The Asynchronous Lag Trap

PostgreSQL streaming replication is asynchronous unless synchronous_standby_names is configured. An asynchronous standby can lag the primary by milliseconds to seconds under load. When an un-orchestrated failover occurs, every transaction committed in that delta is lost. For billing, financial ledgers, or inventory balances, this is usually unacceptable.

1.3 Connection Storms on Primary Promotion

When a failover occurs, every client service disconnects and reconnects at roughly the same time. Without a connection pooler, the newly promoted primary is hit by a "thundering herd" of new backend processes, which can exhaust max_connections and memory before it serves useful queries.

2. Reference Architecture: Patroni + etcd + HAProxy

To address these problems, the design uses an active-standby cluster governed by Raft-based distributed consensus.

                         +-----------------------------------+
                         |      Application Services         |
                         |   (E-Commerce, Ledger, APIs)      |
                         +-----------------+-----------------+
                                           |
                                 TCP Port 5000 (Write)
                                 TCP Port 5001 (Read)
                                           |
                         +-----------------v-----------------+
                         |   Redundant HAProxy Load Balancers|
                         |   (Patroni REST Health Checking)  |
                         +--------+--------+--------+--------+
                                  |        |        |
                  +---------------+        |        +---------------+
                  |                        |                        |
        +---------v---------+    +---------v---------+    +---------v---------+
        | pg-node-01        |    | pg-node-02        |    | pg-node-03        |
        | [Primary]         |    | [Sync Standby]    |    | [Async Standby]   |
        | Postgres 16       |    | Postgres 16       |    | Postgres 16       |
        | Patroni           |    | Patroni           |    | Patroni           |
        | PgBouncer (Local) |    | PgBouncer (Local) |    | PgBouncer (Local) |
        +---------+---------+    +---------+---------+    +---------+---------+
                  |                        |                        |
                  +---------------+        |        +---------------+
                                  |        |        |
                         +--------v--------v--------v--------+
                         |      etcd v3 Consensus Cluster    |
                         |    (3-Node Distributed Raft)      |
                         +-----------------------------------+

2.1 Component Responsibilities

1. etcd Cluster (3 Nodes): Holds the cluster lock (leader key) using Raft consensus. If one etcd node drops, the remaining two maintain quorum.
2. Patroni Agent (per DB Node): Runs alongside PostgreSQL and renews the leader key in etcd every loop_wait seconds. If the leader cannot update the key (for example because it lost contact with etcd for longer than retry_timeout), Patroni demotes the local PostgreSQL instance rather than risk running as an unfenced primary.
3. Hardware / Software Watchdog: Configured via Linux /dev/watchdog. If Patroni itself hangs or the OS freezes, the watchdog resets the machine before the leader key expires, so a stale primary cannot keep accepting writes after another node is promoted.
4. HAProxy: Polls Patroni's built-in HTTP REST health check endpoint:
   • GET http://<node-ip>:8008/primary returns HTTP 200 on the active leader, HTTP 503 on standbys.
   • GET http://<node-ip>:8008/replica returns HTTP 200 on healthy standbys, HTTP 503 on the active leader.
5. PgBouncer: Colocated on each database host, handling connection pooling in transaction mode so the number of PostgreSQL backend processes stays small even with thousands of client connections.

3. Production Configuration Playbooks

3.1 Patroni DCS Configuration (patroni.yml)

The configuration below uses the shortest ttl Patroni allows (it has a documented minimum of 20 seconds, and the values must satisfy loop_wait + 2 * retry_timeout <= ttl). Size memory settings to your hardware; the values shown assume a host with roughly 128 GB of RAM.

scope: epifive-postgres-cluster
namespace: /service
name: pg-node-01
 
etcd3:
  hosts:
    - 10.0.10.11:2379
    - 10.0.10.12:2379
    - 10.0.10.13:2379
 
restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.10.21:8008
 
bootstrap:
  dcs:
    ttl: 20
    loop_wait: 5
    retry_timeout: 7
    maximum_lag_on_failover: 1048576 # 1 MB maximum tolerable replication lag
    synchronous_mode: true
    synchronous_mode_strict: true # block writes rather than silently fall back to async
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        shared_buffers: 32GB
        effective_cache_size: 96GB
        maintenance_work_mem: 2GB
        work_mem: 64MB
        max_connections: 500
        wal_level: replica
        wal_log_hints: "on"
        max_wal_size: 64GB
        min_wal_size: 4GB
        checkpoint_completion_target: 0.9
        synchronous_commit: "on"
        archive_mode: "on"
        archive_command: "pgbackrest --stanza=epifive archive-push %p"
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.10.21:5432
  data_dir: /var/lib/postgresql/16/data
  bin_dir: /usr/lib/postgresql/16/bin
  pgpass: /var/lib/postgresql/.pgpass
  authentication:
    replication:
      username: replicator
      password: "SUPER_SECURE_REPLICATION_PASSWORD"
    superuser:
      username: postgres
      password: "SUPER_SECURE_POSTGRES_PASSWORD"
 
watchdog:
  mode: automatic
  device: /dev/watchdog
  safety_margin: 5

A note on synchronous_mode_strict: with false, Patroni drops back to asynchronous replication when no synchronous standby is available, which keeps the primary writable but means RPO=0 is no longer guaranteed. With true, writes block until a synchronous standby is back. Choose deliberately; for ledgers and balances, strict mode (or synchronous_mode: quorum with enough replicas) is usually the right trade-off. wal_log_hints (or data checksums) is required for pg_rewind to work.

3.2 HAProxy Dynamic Routing (haproxy.cfg)

HAProxy routes write traffic exclusively to the node whose Patroni REST endpoint returns 200 OK for /primary, and balances read queries across all nodes returning 200 OK for /replica:

global
    log /dev/log local0
    maxconn 10000
    user haproxy
    group haproxy
 
defaults
    log     global
    mode    tcp
    option  tcplog
    timeout connect 3000ms
    timeout client  300000ms
    timeout server  300000ms
 
# --- Primary Write Port (5000) ---
frontend postgres_write_front
    bind 0.0.0.0:5000
    default_backend postgres_write_back
 
backend postgres_write_back
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 1s fall 2 rise 2 on-marked-down shutdown-sessions
    server pg-node-01 10.0.10.21:6432 maxconn 2000 check port 8008
    server pg-node-02 10.0.10.22:6432 maxconn 2000 check port 8008
    server pg-node-03 10.0.10.23:6432 maxconn 2000 check port 8008
 
# --- Read-Only Replica Port (5001) ---
frontend postgres_read_front
    bind 0.0.0.0:5001
    default_backend postgres_read_back
 
backend postgres_read_back
    mode tcp
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 2s fall 2 rise 2
    server pg-node-01 10.0.10.21:6432 maxconn 3000 check port 8008
    server pg-node-02 10.0.10.22:6432 maxconn 3000 check port 8008
    server pg-node-03 10.0.10.23:6432 maxconn 3000 check port 8008

Notice the on-marked-down shutdown-sessions directive in the primary backend: when node 1 stops reporting itself as primary, HAProxy closes all existing client TCP connections to it. This prevents application threads from continuing to send writes to a node that is stepping down.

4. Connection Pooling: PgBouncer Tuning

In PostgreSQL, each client connection is served by a dedicated backend process. Every backend carries its own memory overhead, and thousands of them increase lock-manager and snapshot contention, so raising max_connections into the thousands is rarely a good idea.

Placing PgBouncer between HAProxy and PostgreSQL in transaction pooling mode lets a large number of client connections share a small pool of server connections. Because HAProxy runs on separate hosts, PgBouncer must listen on the node's network interface, not only on localhost:

[databases]
epifive_db = host=127.0.0.1 port=5432 dbname=epifive_db
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 120
min_pool_size = 30
reserve_pool_size = 20
reserve_pool_timeout = 2
server_idle_timeout = 60
query_wait_timeout = 15
client_idle_timeout = 30
max_prepared_statements = 100

server_reset_query is not used in transaction pooling mode, so it is omitted here. Restrict access to port 6432 with host firewall rules so only the HAProxy nodes can reach it.

Critical Production Caveat: Prepared Statements in Transaction Mode

Because transaction pooling reassigns the server connection between transactions, SQL-level PREPARE/EXECUTE does not work. Protocol-level named prepared statements (the kind most drivers use automatically) are supported from PgBouncer 1.21 when max_prepared_statements is set to a non-zero value, as above. On older PgBouncer versions, configure your driver to use unnamed prepared statements or disable automatic statement preparation.

5. Failover Simulation & Disaster Recovery Drills

A failover test you should run before go-live: generate load with pgbench, then make the primary node disappear (power it off, or isolate it from the network). Note that simply running kill -9 on the postgres process is not a failover test: Patroni is still alive, still holds the leader key, and will restart PostgreSQL locally.

With the timing configuration above, the expected sequence looks like this:

Record the actual timings in your environment, confirm that no acknowledged transaction is missing after the test, and repeat the drill for an etcd node failure and for a planned patronictl switchover.

Key Architecture Decision Matrix