engineering writeup
Running highly-available Postgres in a home lab
The problem
Why run HA Postgres at home? To practice the enterprise-grade shape for real, and to give every stateful app a database that survives a node dying.
The shape
app → HAProxy VIP → current primary 3× Patroni nodes ── etcd quorum (leader election) failover: etcd elects, Patroni promotes, HAProxy re-routes
The hard parts
Consensus. etcd provides the quorum that decides who is primary; Patroni watches it and promotes/demotes nodes accordingly.
Automatic failover. Kill the primary and a replica is promoted without manual intervention — I kill-tested this deliberately rather than trusting it on faith, covering both a clean manual failover and an ungraceful kill of the leader.
Single connection endpoint. Apps connect to an HAProxy VIP, never a node directly, so a failover is invisible to the application: HAProxy watches Patroni's REST health checks and re-routes writes to whichever node holds the lock, without the app changing a connection string.
Backups. Streaming replication keeps the two standbys hot, but replication isn't a backup — a bad write replicates just as fast as a good one. A separate physical backup job runs against the cluster on a schedule and was verified restorable, so recovery doesn't depend solely on "a replica is still up."
What I'd do differently
Today it's a single 3-node cluster in one location — real protection against a node or a disk dying, none against the site itself going down. A proper next step is a cross-site standby: an async replica outside the local failure domain, accepting a bit of replication lag in exchange for actual disaster recovery instead of just high availability.
Replication is synchronous enough to keep the standbys current for fast failover, but I haven't pushed on the synchronous-vs-asynchronous tradeoff explicitly — a stricter synchronous_commit setting would guarantee zero data loss on failover at the cost of write latency, and I'd rather make that tradeoff on purpose per-tenant than inherit whatever the default happened to be.
Every app currently opens its own connections straight through the HAProxy VIP. Under real concurrent load that's a lot of idle connections fighting Postgres's per-connection overhead — a pooler (something like PgBouncer) in front of HAProxy, or between HAProxy and the nodes, would be the next thing I'd add before this cluster took on more tenants.