How to detect PostgreSQL split-brain with two commands (and what actually prevents it)

Split-brain is the PostgreSQL high-availability failure that nobody notices until it is too late: two nodes accept writes at the same time, applications write different data to each one, and there is no clean way to merge them afterwards.

The good news is that detecting it takes two commands, and preventing it comes down to three design rules.

Detect it: two commands

Run this on every node of the cluster:

SELECT inet_server_addr(), pg_is_in_recovery(), timeline_id
FROM pg_control_checkpoint();

• pg_is_in_recovery() = f means the node thinks it is a primary. If more than one node returns f, you have two primaries.
• timeline_id tells you which one was promoted later: every promotion starts a new timeline, so the node on the higher timeline is the one that diverged.

If you run Patroni, the cluster view is one command away:

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

It shows the leader and the timeline (TL) of every member. A member on an old timeline that still reports itself as running and writable is your red flag.

Why it happens

In practice almost every split-brain comes from one of these:

1. A network partition plus a failover script that does not check quorum. The replica cannot see the primary, assumes it is dead and promotes itself, while the primary is alive on the other side of the partition.
2. A virtual IP that moved while clients kept their connections. The VIP now points to the new primary, but long-lived connections are still open against the old one, and it keeps accepting writes.
3. A manual promotion during an incident (pg_ctl promote or pg_promote()) while the old primary was only frozen, not dead.

What actually prevents it

1. Decide the leader by majority, not by opinion. Patroni keeps a leader key in a consensus store (etcd, Consul or ZooKeeper) that can only be written with a majority. By default, a primary that cannot renew its leader key demotes itself. That is what removes the network-partition case.

2. Fence writes at the load balancer. Applications should never connect to a node directly. HAProxy can ask the Patroni REST API which node is the leader and send writes only there:

listen primary
    bind *:5000
    option httpchk OPTIONS /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 10.0.0.11:5432 maxconn 100 check port 8008
    server pg2 10.0.0.12:5432 maxconn 100 check port 8008

on-marked-down shutdown-sessions matters: it kills the existing connections to a node that stops being the leader, which removes the "VIP moved but clients did not" case.

3. Have an odd number of votes. Quorum needs a majority, and two nodes can never form one after losing contact with each other. With two database servers the third vote has to come from somewhere: a small witness node running etcd, or another component that already sees both nodes, such as the load balancers.

The two-server case

Two database servers is the most common setup and the one where split-brain is most likely. We wrote up the options in detail, including how the load balancers and a management console can provide the missing vote without a third database machine:

• PostgreSQL split-brain: causes, detection and prevention
• PostgreSQL high availability with only 2 servers

Disclosure: we build HighAvailabilityData, a free on-premise console that deploys and operates Patroni, etcd, HAProxy, PgBouncer and pgBackRest. Everything above applies to any Patroni cluster, with or without it.