Failover, Patroni, and Split Brain

Replication gives you a copy. High availability is the process built on top of it: decide the primary is gone, promote a replica, move the application, and guarantee the old primary can never accept another write. Postgres supplies exactly one of those four steps. The other three are yours, or a tool's.

Done by hand, the whole procedure takes a few minutes and a clear head. Done automatically, it takes a consensus store and a fencing story, and the second one is the difference between a design that works and a script that has not caused an incident yet. Cartwheel does it by hand first, at 22:00 on a Tuesday, with the numbers written down, and that rehearsal is the end of this chapter.

The Manual Procedure

Every step below has a command and a check that must pass before the next one runs. Stop the writes. Confirm the standby has everything. Promote. Repoint the application. Verify with a real transaction rather than with a connection test.

-- 1. stop writes at the pooler, not in the application
$ psql -h pgbouncer-01 -p 6432 pgbouncer -c "PAUSE cartwheel"

-- 2. on pg-primary, then on pg-replica-b
SELECT pg_current_wal_flush_lsn();     -- 6F/AB12C480
SELECT pg_last_wal_replay_lsn();       -- 6F/AB12C480  equal: nothing left behind

-- 3. promote pg-replica-b
SELECT pg_promote();                   -- t
SELECT pg_is_in_recovery();            -- f

-- 4. repoint pgbouncer-01 at the new primary, then let traffic back in
$ psql -h pgbouncer-01 -p 6432 pgbouncer -c "RESUME cartwheel"

The whole procedure rests on the second step and the third. Comparing the primary's flush position with the standby's replay position is what makes a planned failover lossless: equal LSNs mean every committed transaction is already on the machine about to take over. That check exists only when there is still a primary to ask, which is the honest difference between a planned failover and an incident. And pg_promote(), which waits up to 60 seconds by default and returns true when promotion finished, is the same operation as pg_ctl promote from a shell; both are worth knowing, because one is available from inside psql and the other from a machine you can only reach over SSH. Pausing at the pooler rather than in the application matters too: client connections are held rather than dropped, so nothing upstream has to handle a wave of connection errors.

Timelines and Rejoining

Promotion starts a new timeline. The promoted server increments its timeline identifier, writes a history file recording the LSN where it branched, and every WAL record it generates from then on belongs to a line of history the old primary knows nothing about. This is why the old pg-primary cannot simply be restarted as a standby: both machines have written different content at overlapping LSNs, and there is no arithmetic that reconciles that. Other standbys, though, follow along automatically, because recovery_target_timeline defaults to latest and they pick up the new timeline's history file when they reconnect.

pg_rewind is what makes the old primary reusable. It finds the divergence point from the two timeline histories and copies only the blocks that actually differ, plus configuration and WAL files in full. On Cartwheel's 700 GB, where a few minutes of divergence changed a few gigabytes, that is minutes instead of the hours a fresh base backup would cost during an already degraded window. It is gated by three prerequisites. The target needs either wal_log_hints on or data checksums, which are enabled by default from 18 onward, so most 18 clusters qualify without anyone having planned for it. full_page_writes must be on, which it is by default. And the target must have been shut down cleanly, which is the requirement that catches people: a machine that was powered off or fenced has to be started and stopped properly before pg_rewind will look at it.

Split Brain and Fencing

The failure that homemade failover produces is a network partition rather than a node dying. pg-primary is alive and healthy and still serving app-01; the monitoring on the other side of the partition cannot reach it, concludes it is dead, and promotes pg-replica-b; app-02 reconnects there. Now two servers accept orders. Both are internally consistent, both are wrong, and there is no merge; reconciling them means going through orders row by row with money attached to the outcome, and choosing which customers to disappoint.

Fencing is the guarantee that the demoted node cannot accept a write, and Postgres does not provide it. A Postgres server has no way to know that another server believes it is the primary. Every mechanism comes from outside the database: a virtual IP that only one node can hold, so losing the address means losing the clients; a proxy that routes strictly according to what a consensus store says and refuses to send traffic to a node that no longer holds the lock; a hard power-off or a disabled switch port; or a demote that runs while the node still holds its lease and before anyone else can take it. A health check plus a promote command has none of this. That combination works perfectly in testing, where failures are simulated by stopping a process, and produces split brain the first time the failure is a network rather than a machine.

Patroni and the Alternatives

None of the tools named here ship with Postgres or appear in its manual, so each is described from its own documentation. Patroni runs an agent beside Postgres on every node and keeps a leader lock in a distributed configuration store, etcd or Consul or ZooKeeper, with a time-to-live the leader has to keep renewing. A leader that cannot reach the store cannot renew its key, so it demotes itself; a node that can acquire the key promotes. The consensus store, not any single node's opinion, is what decides who is primary, which is exactly the property a ping-and-promote script lacks. Patroni also exposes an HTTP interface built for load balancers: GET / and /primary answer 200 only on the node holding the leader lock, /replica answers 200 on a replica and accepts a ?lag= bound so a lagging node drops out of rotation, and /health answers 200 whenever Postgres is running.

Two lighter options solve the same problem with less machinery. repmgr runs a repmgrd daemon on each node and adds a witness, a small separate Postgres instance placed beside the primary, whose reachability lets a standby distinguish "the primary is down" from "I am the one who is isolated". pg_auto_failover uses a monitor node, itself implemented as a Postgres extension, with a pg_autoctl agent on every node driving a shared state machine; the monitor removes an unhealthy secondary from synchronous_standby_names before allowing the primary to carry on, which is the quorum problem from the previous topic handed to software. All three answer the same question the same way, by requiring more than one node's opinion before anyone is declared dead.

Where the Application Points

The traffic is moved by one of three mechanisms, and they differ mostly in who else has to know that a failover happened. A virtual IP carried by the HA tool means the connection string never changes and the fencing is inherent, since only one machine can answer for the address. A proxy, most commonly HAProxy polling Patroni's endpoints, gives one stable endpoint per role and puts the routing decision inside a component you can watch and reason about. libpq's multi-host connection string with target_session_attrs=read-write needs no extra component at all: the driver tries each host and keeps the one that reports itself writable.

Whichever you choose, pgbouncer-01 is part of the failover path. A database that promotes in fifteen seconds behind a single pooler process is a system whose availability is that process's availability, and no amount of consensus in the database tier fixes it. Three things follow: the pooler has to be redundant, its backend target has to change as part of the promotion rather than after somebody notices, and its behaviour when a backend disappears has to be something you have watched rather than something you assume. Chapter 10 covers what a pooler does and how its modes differ. Here it is a component in the availability design, and in the rehearsal below it accounts for sixteen of the thirty-one seconds.

The 22:00 Rehearsal, With Numbers

Cartwheel rehearses on a Tuesday at 22:00, announced, with the runbook printed and someone other than Nadia reading it aloud. The output is three numbers and a list of what went wrong, which is the same discipline the restore rehearsal in Chapter 12 applied to the other failure mode.

22:00:00  PAUSE cartwheel on pgbouncer-01         writes stop, 148 clients held
22:00:04  pg-primary   flush LSN   6F/AB12C480
          pg-replica-b replay LSN  6F/AB12C480    equal - nothing to lose
22:00:06  SELECT pg_promote()  ->  t              timeline 2 begins
22:00:22  pgbouncer-01 repointed, RESUME          148 connections resumed
22:00:31  first completed checkout                RTO 31 s
22:04:10  pg-replica-a following the new primary  timeline 2 picked up
22:19:00  pg-primary rejoined via pg_rewind       4.1 GB copied, not 712 GB

Thirty-one seconds of recovery time, measured from the pause to a customer's order completing, and zero rows lost. The zero is worth being precise about, because it is a property of this rehearsal and not a promise: the LSNs matched because writes had already stopped. An unplanned failover at the same moment would have lost whatever pg-replica-b's replay lag was, around 400 milliseconds, which at the Saturday rate of 50 orders a second is about 20 orders. Thirty-one seconds and twenty orders are the two figures the rehearsal produced.

The list of what did not work is the actual product of the exercise. pg_rewind refused on the first attempt because the old primary had been powered off rather than stopped, so it had to be started and shut down cleanly first: six minutes that were in nobody's estimate, and now a numbered step in the runbook. The monitoring check for "database reachable" was pinned to the hostname pg-primary and paged continuously for nineteen minutes after the site was healthy. And app-02's driver held nine connections to the old backend for four minutes because no TCP keepalive was configured, so a small fraction of requests kept failing after everything else was fine. None of those three is a Postgres problem, and all three were found at 22:00 on a Tuesday for the price of one announced hour. Chapter 14 turns from keeping the database running to who is allowed to connect to it at all.