Building a Self-Healing PostgreSQL Cluster That Woke Me Up Zero Times at 3 AM

Most database outages don’t happen because of bad code or poor planning. They happen because the infrastructure was never designed to handle failure and in distributed systems, failure is inevitable.

A primary database crashes unexpectedly. Replication is running, but there is no automatic failover in place. Someone has to wake up, log in manually, investigate the issue, promote a standby server, reconfigure connections, restart services, and hope recovery happens fast enough to avoid major impact.

Meanwhile, transactions pile up, monitoring alerts explode, and every passing minute increases pressure from both users and management.

That reality pushed me to research High Availability solutions for PostgreSQL not just replication itself, but the entire ecosystem around automated failover, cluster management, split-brain prevention, connection routing, and recovery orchestration.

A single PostgreSQL instance in production is a countdown timer, not a highly available system. You don’t know when it will fail; you only know that eventually it will. And when it does, someone ends up SSHing into a server at 3 AM, promoting a replica, updating connection strings, and explaining downtime to management.

That’s what led me to explore how modern PostgreSQL High Availability stacks are designed to handle failures automatically and recover in seconds instead of minutes.

PostgreSQL | The Foundation & Why PostgreSQL Alone Is Not Enough

PostgreSQL is a rock-solid, ACID-compliant database engine used in many large-scale production environments. But one thing it does not provide out of the box is automatic failover.

What it does provide is streaming replication a continuous flow of WAL (Write-Ahead Log) records from the primary node to standby nodes. Every committed transaction on the primary is shipped to replicas in near real time.

To enable this, a key parameter on the primary side is 🔧

synchronous_standby_names = 'FIRST 1 (pgnode2, pgnode3)'
wal_level = replica

This helps achieve nearly zero data loss during failover, which is extremely important in financial and regulated systems.

But there’s still one major problem: when the primary server fails, who promotes the standby?

PostgreSQL alone does not handle that automatically. That’s where the rest of the HA stack comes in.

etcd | The Brain That Prevents Split-Brain

Before Patroni can manage your cluster, it needs a distributed coordination layer. That's etcd a key-value store built on the Raft consensus algorithm.

In simple terms: etcd holds a single lock key that says who the current PostgreSQL primary is:

/service/pg-prod-cluster/leader = "pgnode1"

Only one node can hold that lock at a time 🔒. The leader must renew it every few seconds. If it stops renewing because it crashed, lost network, or froze, the lock expires and a replica races to acquire it and promote itself.

This is what prevents split-brain: the scenario where two nodes both believe they are the primary, both accept writes, and your data diverges in two incompatible directions. Without etcd, that scenario is not hypothetical it’s inevitable under certain failure conditions.

Critical rule: Always run etcd with 3 or 5 nodes. Never 2 ❌. etcd requires a quorum (majority) to function. With 2 nodes, losing one means losing quorum your entire cluster freezes exactly when you need it most. With 3 nodes, you survive the loss of one. With 5, you survive two.

We learned this the hard way when a junior engineer ran a 2-node etcd to "save resources." One node was rebooted for patching and the Patroni cluster paused for the entire reboot duration. With 3 nodes, that patch would have been invisible.

$ etcdctl endpoint health \
  --endpoints=http://10.0.1.11:2379,http://10.0.1.12:2379,http://10.0.1.13:2379

http://10.0.1.11:2379 is healthy: successfully committed proposal: took = 2.1ms
http://10.0.1.12:2379 is healthy: successfully committed proposal: took = 1.9ms
http://10.0.1.13:2379 is healthy: successfully committed proposal: took = 2.3ms

All three must show healthy. If any node shows unhealthy, you are one failure away from a frozen cluster.

Patroni | The Cluster Orchestrator

Patroni is a Python daemon that runs alongside PostgreSQL on every node. It is the glue that connects everything together.

On startup, all Patroni nodes race to acquire the etcd lock. The winner starts PostgreSQL in read-write mode as the primary. The rest start PostgreSQL in recovery mode as replicas, streaming from the leader.

Every 10 seconds (configurable), Patroni checks if the primary is healthy and renews its lock in etcd. If it cannot the replica with the least lag and within the allowed threshold acquires the lock and promotes itself automatically.

One configuration option I consider non-negotiable in production:

maximum_lag_on_failover: 1048576   # 1MB
use_pg_rewind: true

maximum_lag_on_failover means: do not promote a replica that is more than 1MB behind the primary. Because promoting a heavily lagged replica means data loss, and in a financial system that is not acceptable. Patroni will hold off rather than promote a stale node.

use_pg_rewind means: after the old primary comes back online, instead of rebuilding it from scratch with a full base backup (which can take hours on a large database), pg_rewind rewinds it to the point of divergence and catches it up via streaming. In one incident, this saved us roughly 4 hours of rebuild time on a 2TB database.

Cluster check in one command:

$ patronictl -c /etc/patroni/patroni.yml list

+ Cluster: pg-prod-cluster ----+----+-----------+
| Member  | Host           | Role    | State   | TL | Lag in MB |
+---------+----------------+---------+---------+----+-----------+
| pgnode1 | 10.0.1.11:5432 | Leader  | running |  4 |           |
| pgnode2 | 10.0.1.12:5432 | Replica | running |  4 |         0 |
| pgnode3 | 10.0.1.13:5432 | Replica | running |  4 |         0 |
+---------+----------------+---------+---------+----+-----------+

Lag in MB = 0 across all replicas. That is the output you want to see every morning.

PGBouncer | The Connection Pool

PostgreSQL forks a backend OS process for every single connection. Each one consumes roughly 5–10MB of RAM before running a single query. A typical fintech application with 20 app servers and 100 threads each sends 2,000 direct connections to the database that's up to 20GB of RAM just for connection overhead.

PGBouncer sits between your application and PostgreSQL and maintains a small pool of real database connections, multiplexing thousands of application connections through them.

The mode that matters for most production workloads is transaction pooling: a real database connection is held only for the duration of a transaction, then returned to the pool. This means 50 real PostgreSQL connections can comfortably serve 3,000 application clients simultaneously.

One setting I always add that most guides skip:

server_lifetime = 300
server_idle_timeout = 30

After a Patroni failover, existing PGBouncer backend connections may still point to the old primary. Parameters like server_lifetime and server_idle_timeout help recycle stale connections faster, allowing applications to reconnect cleanly to the new leader after failover.

$ psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

 database | user    | cl_active | cl_waiting | sv_active | sv_idle | maxwait
----------+---------+-----------+------------+-----------+---------+---------
 appdb    | appuser |        44 |          0 |        44 |       6 |       0

cl_waiting = 0 and maxwait = 0 is your green light. If maxwait climbs above 1 second consistently, your pool is undersized or you have slow queries holding connections.

HAProxy | The Traffic Router

Your application connects to one host and one port. It should not know or care which PostgreSQL node is currently the primary. HAProxy handles that invisibly.

Patroni exposes a REST API on port 8008. On the current primary, GET /master returns HTTP 200. On replicas, it returns HTTP 503. HAProxy polls this endpoint every 2 seconds and routes accordingly:

• Port 5000 → write traffic → only the node returning 200 on /master
• Port 5001 → read traffic → all nodes returning 200 on /replica

backend primary_backend
    option httpchk GET /master
    http-check expect status 200
    server pgnode1 10.0.1.11:5432 check port 8008 inter 2000 fall 3 rise 2
    server pgnode2 10.0.1.12:5432 check port 8008 inter 2000 fall 3 rise 2
    server pgnode3 10.0.1.13:5432 check port 8008 inter 2000 fall 3 rise 2

fall 3 means: mark a node DOWN after 3 failed checks (6 seconds). rise 2 means: mark it UP after 2 successful checks (4 seconds). So within roughly 6 seconds of a Patroni failover, HAProxy stops sending writes to the old primary and starts sending them to the new one. Your application sees a brief pause not a crash.

This also gives you read scaling for free. Reporting queries, analytics, dashboards point them at port 5001 and distribute read load across all replicas. In our setup, this reduced primary CPU load by around 30% just by offloading reporting traffic.

Keepalived | The Last Mile

At this point you might notice: HAProxy itself is now a single point of failure. If the server running HAProxy goes down, everything behind it is unreachable even if your PostgreSQL cluster is perfectly healthy.

Keepalived solves this by managing a Virtual IP (VIP) a floating IP address that moves between two HAProxy servers automatically.

Both HAProxy servers run Keepalived. One is MASTER, one is BACKUP. The MASTER holds the VIP. Your application always connects to the VIP never to a specific HAProxy IP.

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass securepass
    }

    virtual_ipaddress {
        10.0.1.100/24
    }
}

If the MASTER HAProxy node goes down, Keepalived on the BACKUP detects the missed VRRP advertisement within 1–3 seconds and brings the VIP up on the backup node. Applications reconnect to the same IP they never see a configuration change.

Putting It All Together | The Full Failover Flow

Here is exactly what happens when your primary database server loses power at 3 AM, in sequence:

1. pgnode1 (primary) crashes. PostgreSQL process dies.
2. Patroni on pgnode1 stops renewing its etcd lock.
3. After TTL seconds (30 in our config), the etcd lock expires.
4. Patroni on pgnode2 and pgnode3 detect the expired lock.
5. Both race to acquire it. Raft consensus ensures only one wins say pgnode2.
6. Patroni on pgnode2 runs pg_ctl promote PostgreSQL exits recovery and enters read-write mode.
7. Patroni on pgnode3 detects pgnode2 is the new leader, reconfigures its recovery.conf to stream from pgnode2.
8. Patroni on pgnode2 hits the /master endpoint returning HTTP 200.
9. HAProxy detects pgnode2 is UP for primary, pgnode1 is DOWN. Traffic rerouted.
10. PGBouncer reconnects its server pool to the new primary through HAProxy.
11. Your application resumes. Total elapsed time: 25-35 seconds.

Connection Flow

One Final Check | The Full Stack Health in 60 Seconds

# Patroni cluster
patronictl -c /etc/patroni/patroni.yml list

# etcd quorum
etcdctl endpoint health --endpoints=<node1>:2379,<node2>:2379,<node3>:2379

# HAProxy primary routing
curl -o /dev/null -s -w "%{http_code}" http://<node1>:8008/master

# PGBouncer pool
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# Replication lag
psql -U postgres -c "SELECT client_addr, state, (sent_lsn - replay_lsn) AS lag_bytes FROM pg_stat_replication;"

# Keepalived VIP check
ip addr show eth0 | grep 10.0.1.100

Run these after every deployment, every maintenance window, every OS patch. If all six return clean output, your stack is healthy end to end.

If you’re working with PostgreSQL high availability setups, there’s always more than one way to design resilience, failover, and recovery strategies in production systems.

If this helped you, share it with someone who’s still running single-node PostgreSQL in production. 👇

Signing off for today.

PostgreSQL #Patroni #HighAvailability #OpenSource #DatabaseEngineering #DBA #HAProxy #PGBouncer #etcd #DevOps #Fintech #DataEngineering #BackendEngineering