PostgreSQL high availability platform

Most of the systems I build eventually depend on one database staying up. This is the platform that stops that being a single point of failure — a two-node PostgreSQL 18 cluster with automatic failover, connection pooling and backups that live off the host.

The application never talks to a database node directly. It connects to HAProxy, which routes to whichever node is currently the leader, and through PgBouncer for connection pooling — so a failover is a routing change rather than something every client has to know about. Patroni runs on both nodes and owns the promotion decision; it keeps cluster state in etcd, so leadership is agreed rather than assumed. pg02 follows pg01 by streaming replication.

Backups are handled by pgBackRest, with WAL archiving and base backups written to NAS. A replica protects against a node failing; it does not protect against a bad migration or a deletion, and those are the failures that actually happen. Restore is the point of a backup, so the archive exists to be replayed, not just to accumulate.

The part worth doing properly was the failover testing. It is easy to stand up a cluster that looks correct in patronictl list and then discover, the first time a node actually goes down, that HAProxy was health-checking the wrong thing or that the firewall never allowed the replication port from the right subnet. Killing the primary deliberately — and watching what the application sees while the promotion happens — is the only part of this that tells you whether it works.

This is infrastructure work rather than product work, so there is no interface to show. The topology above is the deliverable.