PostgreSQL High Availability with Patroni and HAProxy

Patroni coordinates PostgreSQL primary election and member lifecycle through a distributed configuration store (DCS). HAProxy can query Patroni's role-aware health endpoints and route new PostgreSQL connections to the current primary or eligible replicas. Together they automate important steps, but they do not define the business recovery point, make in-flight sessions survive, or replace backups and rehearsed recovery.

Separate the database and consensus designs

Patroni runs on each PostgreSQL member, while the DCS provides consensus and the leader lock. Two PostgreSQL nodes can provide a primary and replica, but a third data node gives more options during maintenance and failures. The DCS should have an independent odd-sized quorum—commonly three or five members—placed across the failure domains the service must tolerate. Counting a PostgreSQL replica as “the quorum node” confuses two different availability decisions.

If PostgreSQL and DCS processes share hosts, a host failure removes capacity from both layers. Model that explicitly. Secure DCS client and peer traffic with TLS and authentication, limit network reachability, maintain tested snapshots where the DCS supports them, and monitor quorum health.

Patroni timing is a constraint, not an SLA

Patroni's dynamic defaults are ttl=30, loop_wait=10, and retry_timeout=10 seconds. Patroni requires:

loop_wait + 2 * retry_timeout <= ttl

The minimum supported TTL is 20 seconds. A requested ttl=15 cannot become the effective value; Patroni's configuration handling enforces its hard-coded minima and timing relationship. Lowering timing values can increase sensitivity to network or DCS pauses and does not guarantee an equivalent application recovery time. In some failure paths, primary_start_timeout can also delay failover while Patroni waits for a crashed primary to recover. Change timing only after fault injection under realistic latency and load.

A security-first configuration skeleton

The following shows the relevant structure, not a deployable universal file. Replace addresses and paths, inject passwords through a protected deployment secret, set restrictive file permissions, and validate the result with the exact Patroni version being deployed.

scope: commerce
namespace: /service/
name: pg1

restapi:
  listen: 10.0.1.11:8008
  connect_address: 10.0.1.11:8008
  certfile: /etc/patroni/tls/rest-server.crt
  keyfile: /etc/patroni/tls/rest-server.key
  cafile: /etc/patroni/tls/ca.crt
  verify_client: optional
  allowlist:
    - 10.0.1.50/32
  authentication:
    username: patroni_api
    password: read-from-protected-deployment-secret

ctl:
  cacert: /etc/patroni/tls/ca.crt
  certfile: /etc/patroni/tls/admin-client.crt
  keyfile: /etc/patroni/tls/admin-client.key

etcd3:
  hosts:
    - etcd1.internal:2379
    - etcd2.internal:2379
    - etcd3.internal:2379
  protocol: https
  cacert: /etc/patroni/tls/ca.crt
  cert: /etc/patroni/tls/etcd-client.crt
  key: /etc/patroni/tls/etcd-client.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    postgresql:
      use_pg_rewind: true
      use_slots: true
  initdb:
    - encoding: UTF8
    - data-checksums
  pg_hba:
    - local all all peer
    - hostssl replication replicator 10.0.1.0/24 scram-sha-256
    - hostssl all all 10.0.1.0/24 scram-sha-256

postgresql:
  listen: 10.0.1.11:5432
  connect_address: 10.0.1.11:5432
  data_dir: /var/lib/postgresql/18/main
  authentication:
    replication:
      username: replicator
      password: read-from-protected-deployment-secret
    superuser:
      username: postgres
      password: read-from-protected-deployment-secret

verify_client: optional applies client-certificate validation to unsafe REST endpoints while allowing health reads without a client certificate; the allowlist and API authentication still need to be enforced. Where every health-check client can present a certificate, required validates all endpoints and is stronger. Never expose the REST port publicly. Patroni documents which REST methods are health reads and which state-changing requests require authentication; network and TLS controls should protect both.

Do not restore the removed bootstrap.users block from old tutorials. Provision application and monitoring roles after bootstrap through an audited process. Keep the Patroni identity separate from application roles and grant only required database and DCS permissions.

Validate and bootstrap once

patroni --validate-config /etc/patroni/patroni.yml
patronictl -c /etc/patroni/patroni.yml list commerce

Use the package's service unit and distribution-specific PostgreSQL layout. Avoid allowing a separate unmanaged PostgreSQL service to start the same data directory. Bootstrap one intended member, verify its DCS registration and PostgreSQL role, then start replicas one at a time. Confirm streaming state and retained WAL before declaring the cluster ready.

HAProxy should route by Patroni role

Patroni 4.1 exposes role-specific health checks:

Configure an HAProxy read/write backend to check each member's /primary endpoint while forwarding PostgreSQL traffic to port 5432. Configure a separate read-only backend only if the application tolerates replica lag and read consistency differences. The health-check leg must validate the Patroni REST TLS certificate and, when required, present a client certificate. Restrict the REST network to HAProxy and administrators.

HAProxy routes new connections. When a primary fails, existing sessions to it break, open transactions are lost, and clients must reconnect. Use bounded retries with backoff and idempotency appropriate to the transaction. Do not retry an ambiguous commit blindly: first determine whether the write may already have committed.

Durability and failover eligibility

In asynchronous mode, a newly committed transaction can be absent from all replicas when the primary disappears. maximum_lag_on_failover limits eligibility based on Patroni's sampled lag, but it is not a zero-loss guarantee. Synchronous mode can reduce the loss window by requiring a synchronous standby, with an availability and latency tradeoff. PostgreSQL sessions that set synchronous_commit=local or off can still acknowledge work before a synchronous replica has made it durable.

Choose mode and settings from an explicit recovery-point objective. Then prove the result by killing processes, isolating networks, losing a DCS member, and measuring committed records—not merely the time until a new primary appears.

Planned switchover and emergency failover

For maintenance, use switchover after confirming the candidate is healthy and replay is current:

patronictl -c /etc/patroni/patroni.yml switchover commerce --primary pg1 --candidate pg2

For an actual incident where the primary is unavailable, the current failover syntax accepts a candidate but not a primary argument:

patronictl -c /etc/patroni/patroni.yml failover commerce --candidate pg2

Rewind and rejoin safety

pg_rewind can make a former primary follow the new timeline when the cluster meets its prerequisites. The target cluster must have had either data checksums enabled at initialization or wal_log_hints=on, and full_page_writes must be on. Required WAL must still be available. Patroni's use_pg_rewind setting does not bypass those requirements.

There is no built-in “pg_rewind role.” PostgreSQL documents the specific functions that may be granted to a non-superuser source connection. A failed rewind can leave the target data directory unrecoverable; take a new base backup when its safety or result is uncertain.

Watchdog and split-brain protection

Patroni can use a Linux watchdog so a node resets if Patroni cannot update the watchdog before the leader lock expires. In required mode, Patroni refuses to become leader when the watchdog cannot be used. The device, permissions, timeout, virtualization behavior, and fencing path must be tested. Calling the watchdog a guarantee without those tests is unsafe.

Operations checklist

• Monitor DCS quorum, Patroni /metrics, member roles, timeline changes, PostgreSQL replication state, WAL retention, disk space, HAProxy backend state, and client reconnect errors.
• Maintain physical backups and WAL archives independently of streaming replication, and rehearse point-in-time recovery.
• Do not pause Patroni casually: pause mode disables automatic failover and transfers responsibility to the operator until resume.
• Test credential and certificate rotation before expiry.
• Record observed recovery time and transaction loss for each fault scenario against the service objectives.

Official primary sources

• Patroni documentation
• Patroni release notes, including 4.1 removals
• Patroni dynamic configuration and timing constraints
• Patroni REST API
• Patroni security guidance
• Patroni replication modes
• Patroni watchdog support
• PostgreSQL pg_rewind
• etcd transport security

Working with JusDB on PostgreSQL HA

JusDB helps teams validate Patroni and DCS topology, harden management paths, test failover and fencing, and measure application recovery and data-loss outcomes.

Explore JusDB PostgreSQL services →  |  Talk to a PostgreSQL engineer