PostgreSQL High Availability

The useful way to read this topology is as four separate questions, each answered by a different component, with no component answering two of them. Confusion about which layer owns which question is the source of most of the operational pain. It is worth being pedantic about it up front.

PostgreSQL sits underneath all four. It streams WAL, it accepts a promotion request, and it has no view whatsoever on cluster membership. Every piece of decision making above it is bolted on, which is why the assembly has this many moving parts.

Normal operation

Separate the traffic paths

The diagram separates application connections, replication and coordination. Repeated boxes name the same roles, not extra machines. The virtual IP (VIP) belongs to the active proxy host, rather than to the database primary. Write-ahead log (WAL) records travel between database servers, not through the application proxy. The control paths decide where a new connection should go; they do not carry its queries.

This view follows the Patroni role-check contract, Patroni architecture, PostgreSQL streaming replication and Keepalived’s address failover model. It intentionally omits backup storage, monitoring and network security controls; a deployable design still needs them. The diagram introduces no port defaults or outage-time promises.

Diagnostic exercise: imagine the application cannot connect while replication continues. Trace which lines can still be healthy and identify the first observation you would collect at the client, proxy and Patroni endpoint. Then repeat with successful connections but growing replication lag. A useful answer distinguishes address reachability, backend eligibility and WAL progress instead of treating them as one database health signal. Compare your explanation with the failure timeline below; this is a tabletop exercise, not an instruction to disrupt a production cluster.

Follow the steady-state loop

Nothing dramatic happens in steady state, and it is worth walking the loop anyway because every failure is a deviation from it.

Each Patroni instance wakes on its own cycle, reads the cluster state from etcd, looks at the local postmaster, and acts. The instance holding the leader key renews the lease; the others see a leader key that is not theirs, confirm they are streaming from the address it advertises, and go back to sleep. Each write to etcd goes through Raft, so a renewal only succeeds if a majority of the etcd members have durably accepted it. That is the whole safety property: a node runs as primary only while it can prove, to a majority, that it still holds the lease.

Meanwhile HAProxy is polling each node’s Patroni REST API on a separate port from the database traffic. Use /primary for the write backend: it requires PostgreSQL to be primary and Patroni to hold the leader lock. /leader checks the lock but also permits a standby-cluster leader, so it is not an equivalent write check. /replica requires a running replica without the noloadbalance tag. This is the mechanism that lets a TCP proxy route by role despite the PostgreSQL wire protocol being completely opaque to it. HAProxy proxies bytes; the decision comes from an HTTP question asked elsewhere.

keepalived, running on the two proxy hosts, does the least interesting and most easily forgotten thing: one of them holds the address in DNS, and it advertises over VRRP so that the other knows to stay quiet.

The failure timeline

The sequence below is what an unclean loss of the primary host looks like. The order matters more than the exact durations, which are functions of your own timing configuration.

Two consequences fall out of that table. First, the outage is roughly the lease expiry plus a loop iteration plus a health-check interval. The proxy’s polling interval is additive, and tuning Patroni’s timers while leaving a lazy health check in place buys nothing. Second, the failure detection is not the failover: HAProxy withdrew the write backend long before any node was promoted, so there is a window where clients get clean refusals rather than promotion. Applications that retry on connection failure ride through it. Applications that treat a connection error as fatal do not, and no amount of cluster tuning fixes that.

The clean case - a planned switchover - inverts the order. Patroni demotes the current leader first, waits for the candidate to catch up, then promotes, so no committed transaction is at risk. That is why the switchover and failover commands are distinct operations and not aliases, and why draining the write backend in HAProxy before a maintenance switchover turns a brief error burst into nothing at all.

The configuration decisions that actually matter

Size etcd for consensus, not for the database. Three or five members, on their own hosts, with their own disks. An etcd member acknowledges a Raft write only after an fsync, so its disk latency becomes the latency of every lease renewal, and a store that is slow to renew produces demotions of perfectly healthy primaries. It looks like a PostgreSQL problem and is not. This is the single most common way this topology fails. Co-locating etcd with the databases, or with Kubernetes’ own etcd, is how you get a correlated failure: the thing that is supposed to survive the database host dies with it.

Spend the timing budget deliberately. Patroni’s timers are bound by a documented inequality relating the loop interval, the retry timeout and the lease TTL. It is a budget, not a suggestion: the leader must be able to lose one attempt against etcd, retry, sleep a full loop, and still renew before the lease expires. Shortening the TTL shortens the write outage and simultaneously reduces your tolerance for a paused VM, a garbage-collection stall or an etcd leader election. There is no setting that is aggressive about detection and forgiving about noise.

Choose the durability contract before you need it. Asynchronous replication can lose committed transactions on promotion; that is not a bug, it is the definition. Patroni’s synchronous mode constrains the election to nodes known to hold every acknowledged commit, and its strict variant refuses to fall back when no synchronous candidate exists, which means writes block instead. Say plainly which failure you prefer. A ledger takes the outage; a session store does not. Run at least three data nodes if you choose synchronous, so losing one host does not cost write availability.

Decide pg_rewind at bootstrap. Rewinding a diverged former primary requires data checksums or WAL hints, and turning checksums on later means rewriting the cluster. Initialise with --data-checksums. The related settings that delete a data directory when rewind fails default to off, which is the conservative choice and also how you end up with a node that reports as a replica for days and never reaches streaming. Check for that after every failover.

Treat the watchdog as part of the safety argument, not a nicety. Patroni’s promise to demote when it loses the lease only holds while Patroni is being scheduled. A hardware or software watchdog armed before promotion and disarmed on demotion converts “process starved, still writing” into “host reset”. Without it, the consensus store’s correctness does not protect you, because the dangerous node is not participating in consensus at all. See fencing and split brain.

Make HAProxy’s role checks explicit. Two backends, two endpoints, mode tcp set explicitly and not inherited. Set the timeouts: HAProxy has no useful defaults for long-lived database sessions, and an unset client or server timeout produces connections that hang instead of failing. Add on-marked-down shutdown-sessions to the write backend so that a demoted node’s existing sessions are torn down, not left to discover their read-only status one failed INSERT at a time.

Do not put keepalived on the database nodes. It is tempting, because it removes a tier. It is also how you build a system in which a VRRP election, which knows nothing about WAL positions, effectively selects a primary. keepalived moves an address and runs a script; it has no concept of database health and cannot prevent two nodes from believing they are in charge. Keep it on the proxy tier, where the only thing it decides is which HAProxy answers, and give it a track script that checks HAProxy, not merely that the interface is up.

The alternative that removes a tier

Modern PostgreSQL client libraries accept a multi-host connection string with a session-attribute preference, so the driver can try each host and keep the one that is writable. That removes both the proxy and the virtual IP, and with them two failure domains, one of which - the address layer - does not work on most cloud networks anyway.

The cost is that routing policy now lives in every application’s connection string. Changing the node list means a deploy of every service. There is no central place to drain a node before maintenance, no observability on connection distribution, and no shared connection pooling. It is a good trade for a small number of first-party services and a poor one for a fleet of applications you do not all control. Where connection count, not routing, is the binding constraint, PgBouncer in front of the proxy addresses a different problem and is complementary rather than an alternative.

What to watch

Four signals, one per layer, are enough to tell you where an incident lives.

That last row is worth an alert with a page attached. Everything else in this topology degrades; two servers up in a write backend is an active correctness failure, and the cluster will not resolve it for you.