30
Backend
06
Security
12
iOS
21
Infra

Run Postgres under Patroni with Consul as its consensus store

ADR-0101 ACCEPTED · 2024-12-01
Run Postgres under Patroni with Consul as its consensus store

Decision

Run Postgres on every cluster node as a NixOS service managed by Patroni, outside Nomad. One member is the primary and the others replicate from it by asynchronous streaming. Patroni keeps its leader key and the cluster's dynamic configuration in Consul, the same Consul cluster that holds the service catalog, and clients find the primary through Consul.

Why

The cluster spans three locations so that losing a node or a location keeps the app writing, and Postgres holds all of the app's data. Patroni provides automatic failover with a consensus store behind the leader lock. Consul already ran as a three-server quorum on every node, so using it as that store added no second consensus system to run.

Patroni has a NixOS module, so it gets the same declarative configuration and deploy path as every other node service. Running it under Nomad would have meant either third-party Postgres and Patroni images or an image we build and maintain, with the scheduler between Postgres and its disk.

Rejected alternatives

  • A managed Postgres outside the cluster. We judged that a database at another provider would take every query off the private network and across the public internet.
  • Postgres and Patroni as a Nomad job. Container images to source or maintain, and a scheduler deciding where the data volume's owner runs.
  • etcd as the consensus store. A second quorum on the same three nodes beside the Consul we already need.
  • A connection proxy such as HAProxy in front of the members. Consul already knows which member leads, without another hop to run.

Consequences

Replication is asynchronous, so a failover can lose transactions the old primary committed but had not yet streamed. Postgres availability is tied to Consul's: a primary rides out a Consul leader election within Patroni's retry timeout, and a longer Consul outage demotes it. Backups follow whichever node is primary (ADR-0039), and rolling deploys move leadership off a node before touching it (ADR-0093).

Consul has since forced changes to how clients find the primary and to Patroni itself. Clients first found the primary by the role tag Patroni sets on its own registration, and after a switchover Consul's anti-entropy restored a stale tag, so the name answered with a demoted replica too; clients now follow a Consul service whose health check is Patroni's primary endpoint. Consul can also commit the write that claims a new cluster and lose the reply, which leaves unpatched Patroni waiting forever for a leader, so we carry a patch to its Consul backend and every Patroni upgrade must carry it forward.