DEV Community

Diego Gil Serrano
Diego Gil Serrano

Posted on Originally published at highavailabilitydata.com Fully Autonomous

PostgreSQL high availability with only 2 servers: where the third vote comes from

#sre #devops #database #postgres

Many teams have budget for two database servers, not three. Almost every PostgreSQL high-availability guide, on the other hand, opens with "you need at least three nodes".

Both statements are true at the same time. You can run automatic failover with two database servers, but you cannot run it safely with two votes. This post explains why, and where the third vote can come from.

The question every failover has to answer

When a replica stops hearing from the primary, it cannot tell the difference between two situations:

  1. The primary is down.
  2. The network between the two nodes is down, and the primary is happily serving clients.

If the replica promotes itself in case 2, you have split-brain: two primaries accepting different writes. When the network comes back, those two histories cannot be merged automatically, and somebody loses data.

Consensus systems solve this with a majority rule: an action only happens if more than half of the voters agree. With two voters the majority is two. If they disagree, nobody has a majority and nothing happens. That is safe, but it also means no failover.

How Patroni and etcd use the majority

Patroni manages PostgreSQL on each node and stores cluster state in a distributed configuration store (DCS), most often etcd:

  • The primary holds a leader key in etcd and renews it on every HA loop, every loop_wait seconds (default 10).
  • The key expires after ttl seconds (default 30). Only when it expires can a replica take it and promote itself.
  • If the primary cannot update the leader key, Patroni demotes it. In the documentation's words: "In case the update of the leader lock fails, Postgres is immediately demoted and started as read-only." (Patroni's optional failsafe_mode relaxes this when the primary can still reach every other Patroni member.)

So Patroni itself never decides by local opinion: it delegates the "who is primary" decision to etcd. And etcd is a Raft cluster that needs a majority of its members to accept writes. The etcd FAQ gives the numbers:

etcd members Majority Failures tolerated
1 1 0
2 2 0
3 2 1
4 3 1
5 3 2

Two etcd members tolerate zero failures, exactly like one. If one etcd member is on each database server and one server dies, the survivor has 1 of 2 votes, etcd loses quorum, no leader key can be written, and the surviving node cannot become (or even stay) primary. The etcd FAQ also notes that adding a member to reach an even number "doesn't buy additional fault tolerance", which is why the usual recommendation is 3 or 5 members.

The constraint is on voters, not on PostgreSQL instances. That is the door to two-server designs.

Option 1: a third database server

Three nodes, each running PostgreSQL, Patroni and etcd. It is the simplest topology to reason about, and you get a second replica for reads or for synchronous replication. It is also the most expensive option, because the third machine needs the same disk and memory as the others.

Option 2: a witness that only runs etcd

A small VM that runs only etcd, no PostgreSQL and no Patroni. etcd is light, so a modest machine is enough. What matters is placement: the witness must not share a failure domain with either database server (different hypervisor host at a minimum, a different room or site if you have one).

A minimal sketch of the three-member etcd cluster (names and IPs are examples):

# On the witness (repeat on db1 and db2 with their own --name and IPs)
etcd --name witness \
  --initial-advertise-peer-urls http://10.0.0.13:2380 \
  --listen-peer-urls http://10.0.0.13:2380 \
  --listen-client-urls http://10.0.0.13:2379,http://127.0.0.1:2379 \
  --advertise-client-urls http://10.0.0.13:2379 \
  --initial-cluster db1=http://10.0.0.11:2380,db2=http://10.0.0.12:2380,witness=http://10.0.0.13:2380 \
  --initial-cluster-state new
Enter fullscreen mode Exit fullscreen mode

And in each patroni.yml, point Patroni at all three:

etcd3:
  hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379
Enter fullscreen mode Exit fullscreen mode

Use TLS for peer and client traffic in production; it is left out here for brevity.

Now if db1 dies, db2 and the witness still form a majority (2 of 3), the leader key expires, and db2 can take it. If the link between db1 and db2 breaks but both can reach the witness, etcd still has one consistent view and only one node holds the key.

Option 3: let the load balancers vote

Applications rarely connect to PostgreSQL directly. In the common Patroni setup they go through HAProxy, which polls each node's Patroni REST API: GET /primary returns HTTP 200 "only when the Patroni node is running as the primary with leader lock", and HAProxy sends writes to whichever node answers 200.

That makes the load balancers useful observers. They see the cluster from the application side. If most of them cannot reach the primary, the problem is not just the link between the two database nodes; clients are affected too. A design that counts load balancer opinions as votes can supply the third vote with machines you probably already run. This is not something Patroni does out of the box: you need a component that collects those observations, requires a strict majority, and then acts through Patroni (for example via its REST API) without ever bypassing etcd's view of who holds the leader key.

Things to get right with two database nodes

  • Synchronous vs asynchronous replication. With a single replica in strict synchronous mode, losing the replica stops writes on the primary. With asynchronous replication, a failover can lose the last committed transactions. Choose deliberately, and use Patroni's maximum_lag_on_failover to keep a far-behind replica out of the election.
  • The frozen primary. A primary that hangs (stuck disk, stopped process) without crashing keeps the leader key until it expires, and HAProxy will keep trying to send it writes. This is the scenario with the longest outage.
  • Fencing. After a failover, the old primary must not receive writes again until it has rejoined as a replica.
  • Test it. Kill the primary, freeze it with SIGSTOP, cut the network between the nodes, and measure how long writes stop. A failover you have never tested is an assumption.

Summary

  • Two database servers are fine. Two voters are not.
  • etcd needs 3 (or 5) members for fault tolerance; the third one does not have to run PostgreSQL.
  • Your options for the third vote: a third DB node, an etcd-only witness, or a voting scheme built on the load balancers' view.

Disclosure: I work on HighAvailabilityData, a free on-premise console from NUTRADELIS S.L. that deploys and operates Patroni + etcd + HAProxy clusters over SSH. With two PostgreSQL nodes it uses the HAProxy instances and the console itself as the missing voters instead of a third database machine. The nodes remain standard Patroni clusters. More details in the original version of this article.

Sources

Top comments (0)