PostgreSQL High Availability and Failover with Patroni
On this page
Automatic failover: what streaming replication lacks
Day 24 gave you standbys that stay in sync. But nothing in core PostgreSQL decides that the primary is dead, picks a standby, promotes it, stops the old primary from coming back as a second primary, and moves clients over. In SQL Server, Windows Server Failover Clustering (WSFC) and the AG listener do all of that.
In PostgreSQL you add it yourself. The standard tool is Patroni.
| Job | SQL Server Always On | PostgreSQL with Patroni |
|---|---|---|
| Copy the data | AG log transport | Streaming replication |
| Decide who's primary | WSFC quorum | Leader lock in a consensus store (etcd, Consul or ZooKeeper) |
| Promote a replica | WSFC with the AG resource | Patroni agent calls promote |
| Stable client endpoint | AG listener | HAProxy, a virtual IP or DNS, steered by Patroni's REST API |
| Planned role change | Manual failover in SSMS | patronictl switchover |
The SQL Server Failover Cluster Instance (FCI) model, with one copy of the data on shared storage, has no common PostgreSQL equivalent. PostgreSQL HA nearly always means separate copies of the data, like an AG.
How Patroni elects a leader
A Patroni agent runs next to PostgreSQL on every node. The agents share one fact through a consensus store, usually a three-node etcd cluster: who holds the leader lock.
- The primary's agent renews the leader lock every few seconds.
- If the primary crashes or loses its network, the lock expires after a configured time-to-live, typically about 30 seconds.
- The standbys' agents race for the lock. The store guarantees only one can win, and Patroni prefers the healthiest, most up-to-date standby.
- The winner promotes its PostgreSQL. The other standbys follow the new primary.
- If the old primary comes back, its agent sees it no longer holds the lock and restarts it as a standby, using
pg_rewindif it needs to discard writes that never reached the new primary.
That last step is what prevents split brain, two nodes accepting writes at once. A node may only act as primary while it holds the lock, and taking the lock requires a majority of the consensus store. That's why etcd runs on 3 or 5 nodes: an odd number means a majority survives one or two failures. It's the same idea as WSFC quorum with a witness.
A minimal Patroni configuration
Each node gets a patroni.yml. This is a sketch to show the shape, not a complete production file:
scope: sales-cluster
name: pg-node1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.0.0.21:8008
etcd3:
hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.0.0.21:5432
data_dir: /var/lib/postgresql/18/main
authentication:
replication: {username: replicator, password: change-me}
superuser: {username: postgres, password: change-me}Patroni then manages PostgreSQL itself. It runs initdb on the first node, builds the standbys, and holds settings shared across the cluster in the consensus store. You stop editing postgresql.conf on each node by hand and use patronictl edit-config instead.
Day-to-day commands:
patronictl -c /etc/patroni.yml list # which node is leader, lag of each replica
patronictl -c /etc/patroni.yml switchover # planned move, for example before patching
patronictl -c /etc/patroni.yml failover # forced move when the leader is unhealthyReplacing the AG listener: HAProxy, VIP or multi-host
Applications must never connect to a node's own address, because the primary moves. Common setups:
- HAProxy health-checks each node's Patroni REST API.
GET /primaryreturns 200 only on the leader, so HAProxy sends write traffic there. A second port can route to/replicafor read traffic. - A virtual IP that moves with the leader, managed with vip-manager or keepalived.
- Multi-host connection strings, which most drivers support:
host=node1,node2,node3 target_session_attrs=read-writemakes the client try each host until it finds the primary.
Managed services such as Amazon RDS Multi-AZ, Aurora and Azure Database for PostgreSQL with HA do all of this behind a single endpoint. On a managed service, you test failover rather than build it.
The older alternative you may meet is repmgr. It's simpler, with no external consensus store, but its protection against split brain is weaker. Most new self-managed deployments choose Patroni.
Failover drills
Run failover drills on a staging cluster: stop the primary, cut its network, kill the etcd leader. Time how long the application is unavailable, check that no committed data was lost, especially with asynchronous replication, and confirm the old primary rejoins as a standby. In DataGrip, point the data source at the HAProxy or virtual IP endpoint, and SELECT pg_is_in_recovery(); tells you which role the server you reached is playing.
Check yourself
The leader lock can only change hands with agreement from a majority. With three nodes, a majority survives one failure. With two, losing either one means no majority and no failover.
Its Patroni agent finds that another node holds the leader lock, so it starts PostgreSQL as a standby, rewinding with pg_rewind if needed, instead of as a primary.
When node1 stops being primary, writes fail or the application can't connect. Point it at a stable endpoint: HAProxy using Patroni's health checks, a virtual IP, or a multi-host connection string with target_session_attrs=read-write.
That failover actually works end to end: Patroni promotes a replica in the expected time, the connection layer redirects clients, the application reconnects and retries, and nobody needs to touch anything. Drills regularly uncover hard-coded hostnames and missing retry logic.
libpq-based drivers and Npgsql support multi-host connection strings, such as Host=pg1,pg2,pg3;Target Session Attributes=read-write. The driver tries each host and connects to the one that accepts writes, which is simple but relies on driver support and timeouts.