How do I prevent PostgreSQL split-brain with quorum and consensus?

Short answer: the only cure for split-brain is quorum — a node may only become or stay primary if it holds a majority. That’s exactly what was missing in your scenario.

Short answer

The root of the problem: a replica promoted itself based on local information alone (“I can’t reach the Master, so it must be dead”). But the Master wasn’t dead, it was just network-partitioned. Two primaries, diverged data. I covered the decisions made by the layer that routes the app to a server in the PgBouncer pooling modes answer; a failover changes the topology underneath that layer.

Why

1. Local information is not proof of failure. A single node cannot tell “I can’t reach it” apart from “it’s dead”; only a majority can make that distinction.

2. A hand-rolled failover script produces exactly this bug. “If the Master doesn’t ping, promote” logic yields two primaries under a network partition. This is a solved problem.

3. An isolated old primary that keeps writing diverges the data. Blocking the promotion isn’t enough; the old primary has to stop itself too.

What to do

1. Nobody becomes primary without a majority. Use a failover manager that decides by consensus: Patroni + etcd/Consul. Raft holds the leader lock; to become the new primary, a node has to take that lock from the quorum.

2. Let the old primary demote itself (fencing). Patroni’s default is explicit: the moment the leader-lock update fails, Postgres is immediately demoted and started read-only. So the network-isolated old Master demotes itself to a replica and “two primaries writing at once” becomes impossible. (With DCS Failsafe Mode on, the primary may keep running as long as it can reach every known member over the Patroni REST API; if one member doesn’t respond, it demotes anyway.)

3. Use an odd number of voting members. Build the consensus layer (etcd) with an odd number of members spread across failure domains — 3 or 5. When the network partitions, there’s a clear majority side.

4. Add synchronous replication if you can’t lose the last transactions. With synchronous_commit and quorum-based synchronous replication, a commit doesn’t return until at least one replica acknowledges. The cost is latency, the gain is near-zero data loss.

5. Retire the hand-rolled failover script. Don’t reinvent the solution.

Bottom line: I’d move to Patroni + etcd quorum + fencing, and not manage failover by hand. I go deeper on DB operations on sade.dev; but the one line is this: a promotion decision is made by the majority, not by local information.