PostgreSQL High Availability: Build, Break & Fix Patroni + etcd

Let's build a PostgreSQL cluster that survives things going wrong, and let's build it one honest step at a time. This is where everyone starts: one application, one PostgreSQL eighteen server called db one. A write arrives, it is recorded in the write-ahead log, and the commit comes back. It works perfectly. And that is exactly the trap, because the postgres processes, the kernel, the disk, and the address your clients dial all live inside a single failure domain.

So let's break it. db one is gone. Sockets reset, transactions in flight are lost, and libpq comes back with connection refused. And notice there is nothing to fall back to: no second address, no second copy. Right now your recovery time is however long a human takes to wake up and repair the machine, and your recovery point is whatever happens to be in your last backup.

Fix number one: stop keeping a single copy. db two is cloned with pg basebackup and then follows db one continuously. On the primary, a WAL sender pushes write-ahead log records over port five four three two. On the standby, a WAL receiver writes them to disk and the startup process replays them, one record at a time. And because hot standby is on, db two will answer read-only queries while it is still catching up.

Two details decide whether this actually protects you. First, a physical replication slot. It forces db one to hold on to WAL until db two confirms it, so a slow standby can never quietly fall off the end of the log. But an uncapped slot will fill the WAL directory, so bound it with max slot wal keep size, and accept that the slot gets invalidated instead of your disk filling. Second, this is asynchronous, so pg stat replication always shows a gap. Watch the flush lag, because that number, in bytes, is your real recovery point objective.

Now the interesting part. Kill db one again. This time the data survived; it is sitting right there on db two. So send it an insert. Cannot execute INSERT in a read-only transaction. A standby that is still in recovery refuses writes by design, every single time. The copy is alive. The writable service is not. And nothing on this board has permission to change that.

Someone has to make the call. The promote command ends recovery, db two finishes replaying what it has, and it becomes writable. And at that moment PostgreSQL increments the timeline from one to two and writes a history file to record the fork. Which creates a second problem. When db one comes back, it is still on the old timeline and it has diverged, so it needs pg rewind before it can follow. This does work. But the clock running during the outage is a human clock, and humans sleep.

So let's replace the human. Patroni is one agent per node, and it owns its PostgreSQL completely. It bootstraps replicas, renders the postgres configuration, watches health, and issues promote and demote itself. It also publishes a REST API on port eight thousand eight, where a GET to slash primary returns two hundred on the leader, and only on the leader. Patroni now decides how to act. But here is the thing: it must never decide alone.

Why not? Because three agents, each convinced that it should be primary, is precisely how you corrupt a database. So we give them an outside referee. etcd: three members running Raft over one replicated log, where every write needs a majority, two out of three. Lose a member and decisions keep flowing. And be very clear about what lives in here. Member state, dynamic configuration, and one leader key. No tables. No WAL. Not one byte of your data.

Leadership is not a flag; it is a lease. The primary writes its own name into the leader key with a thirty-second time to live, then comes back every ten seconds to refresh it. Patroni insists that the lease time to live is at least loop wait plus two times retry timeout, so one slow etcd round trip can never expire a perfectly healthy leader. One key, one holder, enforced by compare and swap.

And that lease does double duty, because it is also a fence. If a node cannot refresh the key, it has to assume somebody else is about to take over, so Patroni demotes it to read-only before that can happen. Add the hardware watchdog and the kernel resets the entire box if Patroni itself hangs. Set the safety margin to minus one and the watchdog fires at half the lease time, which is the tightest timing Patroni offers. Configured properly and actually tested, this is what makes split-brain very unlikely.

Let's watch it work. db two dies. Nobody refreshes the lease, so within thirty seconds the leader key simply vanishes. Every healthy candidate compares its replay position, and the winner claims the key with a create-if-absent write. Only one can win, and only the winner promotes. Now watch what does not happen. Nobody's open connection is moved. Your clients see a reset socket, and they have to reconnect and retry. Remember that.

Two nodes buy you exactly one failover, and then you are back where we started. So db three joins as a second standby, and now the cluster can lose a node and still hold an election with a candidate to spare. But only, and this matters, if the failure domains are genuinely independent. Separate hosts, separate storage, ideally separate availability zones, with the etcd members spread out the same way. Three replicas on one hypervisor is one replica wearing a costume.

More candidates means Patroni has to choose, and it does not choose blindly. It compares each standby's received position against the leader's and disqualifies anything lagging further behind than maximum lag on failover, sixteen megabytes here. Look at db two: a hundred and twenty-five megabytes behind. Rejected. db three is two megabytes behind, and db three is promoted. Raise that limit and you fail over more often. Lower it and you sometimes refuse to. One knob, two objectives.

And if losing even two megabytes is unacceptable, you switch Patroni into quorum synchronous mode. You set synchronous node count to one, and Patroni takes over the synchronous standby list for you. That is a list you should never write by hand. Now every commit waits for at least one standby to flush the record to disk, so a normal single-node failure loses no acknowledged transaction. And you pay for that on every commit, forever, in latency. Choose it deliberately.

The database layer is solved. Your clients still have no idea where the primary is. Enter HAProxy, and notice what it health-checks. Not PostgreSQL. Patroni. An HTTP check against slash primary on port eight thousand eight, every three seconds, three consecutive failures to mark a backend down. Port five thousand carries writes to the one node that answers two hundred. Port five thousand one spreads reads across the standbys. And on marked down shutdown sessions drops stale sessions the instant a role moves.

One more component in front of each database. PgBouncer, on port six four three two, in transaction pooling mode. In this single-user, single-database example, five hundred client connections share a pool of up to thirty-two PostgreSQL server connections, so PostgreSQL is never asked to fork thousands of backends just to leave them idle. Note that the default pool size is per user and database pair, so every extra pair gets its own pool. The trade-off is real: no session-level state, and protocol-level prepared statements need max prepared statements enabled. And be clear about the job. PgBouncer pools connections. It has no idea what a primary is.

Now look closely, because we have just recreated the original problem one layer up. One HAProxy is one single point of failure. So lb two runs the identical configuration with the identical checks. Neither router holds an opinion of its own. Both read the role from Patroni and independently converge on the same answer, although their check phases can differ by a few seconds. Which leaves exactly one thing missing: a single address that survives losing either router.

Keepalived provides it. Both routers speak VRRP with virtual router id fifty-one. lb one advertises the higher priority, so lb one owns the floating address, ten, twenty, zero, thirty. A tracking script watches the local HAProxy and drops that priority the moment it dies. And notice the boundary. Keepalived moves an IP address between machines. It has no opinion whatsoever about which database is primary. Two different jobs, two different components.

Step back, because this is the whole system, and every layer owns exactly one decision. PostgreSQL stores the data and ships the WAL. Patroni operates PostgreSQL. etcd decides who is allowed to lead. HAProxy discovers where the leader is right now. PgBouncer rations connections. Keepalived keeps the address still. Zero single points of failure, and that comes from the separation of duties, not from any one product on this board.

Claims are cheap, so let's run the drills. Drill one: kill the primary. Patroni notices inside one loop wait, ten seconds. The lease is gone by thirty. Promotion itself takes about two. HAProxy marks the new backend up after two good checks. Add that up and on this configuration you should expect roughly twenty to forty seconds, an estimate derived from the timers, not a measurement. Run the drill yourself and record your own number. And every transaction that was open has been rolled back, which is exactly why your application's retry logic is part of this architecture and not an afterthought.

Drill two: kill the active router. lb two stops hearing advertisements, claims the address after roughly three intervals, and fires a gratuitous ARP so the switches relearn where it now lives. Your clients keep dialing the same address; nothing about it changed. But the sessions that were pinned through lb one still have to reconnect, because no TCP connection survives its own endpoint disappearing.

Drill three is the one people get wrong, so let's attack consensus itself. Lose one etcd member: two of three is still a majority, and nothing at all happens. Lose a second, and there is no majority left. Now the primary cannot refresh its lease, so it demotes itself to read-only. And no standby can be promoted, because no majority exists to grant a lease to anyone. The cluster has chosen to be unavailable rather than split-brained. That is the correct answer.

Bring the members back, quorum returns, and Patroni re-establishes db one as leader with db two and db three streaming behind it. Six components, six responsibilities, one stable address. And one last thing, because it is the mistake I see most often: none of this is a backup. High availability protects you from a node dying. It will not save you from a bad deployment, a corrupted page, or a dropped table. Those need real backups and point-in-time recovery. Build both.

That is the complete framework, from a single server to a cluster that survives node loss, router loss, and the loss of consensus itself. If this was useful, subscribe and hit the bell, because next we take this same design across regions: multi-region PostgreSQL high availability, where wide-area latency, quorum placement, and controlled regional failover change every trade-off you just learned. See you in the next one.