Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 25 · 5 min read

PostgreSQL High Availability and Failover with Patroni

On this page
  1. Automatic failover: what streaming replication lacks
  2. How Patroni elects a leader
  3. A minimal Patroni configuration
  4. Replacing the AG listener: HAProxy, VIP or multi-host
  5. Failover drills
  6. Check yourself

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.

JobSQL Server Always OnPostgreSQL with Patroni
Copy the dataAG log transportStreaming replication
Decide who's primaryWSFC quorumLeader lock in a consensus store (etcd, Consul or ZooKeeper)
Promote a replicaWSFC with the AG resourcePatroni agent calls promote
Stable client endpointAG listenerHAProxy, a virtual IP or DNS, steered by Patroni's REST API
Planned role changeManual failover in SSMSpatronictl 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.

  1. The primary's agent renews the leader lock every few seconds.
  2. If the primary crashes or loses its network, the lock expires after a configured time-to-live, typically about 30 seconds.
  3. The standbys' agents race for the lock. The store guarantees only one can win, and Patroni prefers the healthiest, most up-to-date standby.
  4. The winner promotes its PostgreSQL. The other standbys follow the new primary.
  5. If the old primary comes back, its agent sees it no longer holds the lock and restarts it as a standby, using pg_rewind if 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:

YAML
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:

Code
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 unhealthy

Replacing 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 /primary returns 200 only on the leader, so HAProxy sends write traffic there. A second port can route to /replica for 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-write makes 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.