Postgres with automatic failover: salmon provisions, Patroni decides

Generated from specs/pg-patroni.md — the repository is the canonical source, and may be ahead of this page.

Postgres with automatic failover: salmon provisions, Patroni decides

Status: draft / not implemented. A design sketch to react to, not a committed plan.

Companion: pg-switchover.md is the two-node, operator-driven design for test harnesses and low SLAs. This one is for when nobody should have to be awake for a failover.

Position

Salmon does not decide when to fail over, and does not know who the primary is. Safe automatic failover needs consensus, so that a split network cannot elect two primaries, and a leader that demotes itself when it loses the quorum, which is Patroni’s form of fencing. run serve is one process per machine with no coordination between them. Building consensus into salmon would mean writing a worse etcd.

So the split of work is:

The chain of ownership is salmon → systemd → Patroni → Postgres. Salmon’s supervision (Actions/Upkeep.hs) must stop at the Patroni unit: a salmon node that restarts Postgres is fighting its owner.

The consequence for everything that exists

The primary’s location never appears in a directive, a ref, help or notes. This is the opposite of pg-switchover.md, where the operator’s declaration is the truth. Here, a declared primary would be stale after the first automatic failover, and the next run up would try to “correct” it.

Existing builtins that must not run on a Patroni member:

Builtins that still apply, as long as they reach the leader: database, user, group, grant, adminScript, templates and clones, SreBox.PostgresMigrations. Every one of them runs sudo -u postgres psql -p <port> on the local machine today. On a replica, CREATE DATABASE fails with “cannot execute … in a read-only transaction”. See “Reaching the leader”.

New builtins

Etcd

• Renders the config and a systemd unit; the check is etcdctl endpoint health against the member itself.
• The trap is bootstrap. initial-cluster-state is new exactly once in a cluster’s life. After that, a new member is added by a runtime call (etcdctl member add) followed by starting it with existing, and it cannot be expressed as config alone. A node for “etcd cluster of these three” therefore has a seed phase (all start with new when no member answers) and a join phase (member add, then start). Its check compares the member list, and does not look at a config file.
• TLS: peer and client certificates come in pre-provisioned, per the recipes’ secret-transport rule. Certificates can mint them in a recipe that owns that choice; the builtin only takes paths.
• Several Patroni clusters can share one etcd (each has its own scope and namespace). This is how pg-ha-control-plane.md’s cross-replicated pair becomes safe: two Postgres machines plus a small third one running only etcd, with three votes between them.

Patroni

• Renders patroni.yml (an EncodeFileContents value, so its content fingerprint reaches notes and a re-declaration is Stale under run serve), plus a systemd unit.
• The check reads the local REST API on port 8008:
  • GET /health: is Postgres running under Patroni;
  • GET /patroni: state, role, pending_restart, and whether the member is in the cluster at all.
  • Role is reported, never judged: a replica is as healthy as a leader.
• Debian’s patroni package ships its own integration with the postgresql-common layout (pg_createconfig_patroni and a per-cluster patroni@ unit). Whether to build on it or render our own is an open question below.

The cluster-wide config node

This is ALTER SYSTEM’s replacement. Patroni keeps postgresql.parameters and friends in etcd, applied on every member.

• check: GET /config, comparing only the keys this node declares, so that other declarations or operators can own other keys.
• up: PATCH /config with those keys.
• A second check condition, the same move as checkService reading NeedDaemonReload: after a restart-only setting changes, /patroni on each member says pending_restart. The node is not Success until nothing is pending, and its up can trigger patronictl restart --pending, which restarts replicas before the leader. Whether salmon should restart members at all, or only report, is an open question.
• Leaning, from the Zalando operator (pkg/cluster/sync.go, syncPatroniConfig, restartInstances): it patches /config once, since the DCS is shared and any member will do, waits loop_wait plus two seconds for members to apply it, and restarts only members whose pending_restart is set. Replicas go first, except when a parameter was decreased that a standby needs to be at least the primary’s value (its requirePrimaryRestartWhenDecreased list, e.g. max_connections): then the primary restarts first, because a standby refuses to start below it. A member that answers an empty config is not initialised yet, so the patch is skipped for it, which for us is Unknown, not Failure.

Routing to the leader

Three options, from least to most machinery. The recipe should offer the first and one of the others:

1. libpq multi-host connection strings: host=a,b,c target_session_attrs=read-write. No new process; works only for libpq clients and only at connect time.
2. vip-manager: a floating IP that follows the leader key in etcd. Needs one L2 segment.
3. HAProxy with option httpchk on GET /primary (port 8008): the standard Patroni setup. One port for the leader, one for replicas (GET /replica). It sits in front of, or replaces the upstream of, the existing PgBouncer.

A Haproxy builtin would have the same shape as Nginx: a config value, a renderer, and a systemd unit. Unlike pgbouncer, HAProxy needs no reload on failover: its health checks move the traffic.

Done as Salmon.Builtin.Nodes.Haproxy (setup, renderConfig, patroniRouter): every member is listed under every listener and only the check path differs (/primary, /replica, /replica?lag=N, /read-only), so the file is the same text before and after a failover, which is T3 for the router. on-marked-down shutdown-sessions closes a demoted leader’s sessions so its clients reconnect to the new one. A member’s traffic port is its own field, so the upstream can be Postgres or a PgBouncer on the member. Changing the member set rewrites the file and restarts HAProxy, dropping connections; a graceful reload (-sf) is not done. Not done either: a recipe placing it beside patroniMember, TLS, and T1/T2/T6 through it (Layer 3).

Reaching the leader

Admin nodes need a way to reach whichever member is the leader right now, which is not known when the directive is written.

The recommendation is to address the leader by its routed endpoint (the HAProxy primary port, or the VIP) with a connection string, not by sudo -u postgres on a machine. That means connection-string variants of the admin builtins. SreBox.PostgresMigrations.remoteMigrateOpaqueSetup already works this way; database, user, templates and clones do not. This is the largest single piece of work in this spec, and it is independently useful: anything managed (Cloud SQL, see Gcp/PostgrestCloudRun) has the same “no local superuser” shape.

The alternative, running the admin nodes on every member with a check that answers Skipped when this member is not the leader, is smaller but racy:

• the leader can change between the check and the up;
• templates would be built by whichever member was the leader at the time.

Refused.

Nodes that must run on exactly one member

Backups (from a replica, by preference) and scheduled jobs (CronTask) would otherwise run on every member.

• Backups: a Patroni tag such as nofailover or a custom one marks the member dedicated to them. The scheduled command gates itself with curl -sf localhost:8008/replica, skipping when the member is not a replica.

• PITR: SreBox.PostgresBackup is pg_dump, which has no point-in-time recovery and no use to Patroni. A continuous archive (pgBackRest or WAL-G, both packaged by Debian) earns its place twice: as the disaster recovery story, and as Patroni’s create_replica_methods, which rebuilds a replica from the archive instead of loading the leader with a base backup.

  Implemented, pgBackRest first (Salmon.Builtin.Nodes.PgBackRest, Patroni.pat_archive). What writing it settled, and what it did not:

  • The repository has to be one every member reads (a shared mount, or S3): a replica is built from an archive it can reach.
  • stanza-create and backup are “exactly one member” operations too: with one pg1-path configured, pgBackRest refuses both on a standby. The nodes check the repository (which any member can read) and their up succeeds only on whichever member leads at that moment; no directive names it.
  • The scheduled backup gates on /primary, not /replica as written above. A backup taken from a standby needs backup-standby and a second pg host reached over ssh or TLS. Not done.
  • archive_mode/archive_command are local postgresql.parameters in patroni.yml, not DCS keys, so that declaring an archive on an existing cluster takes effect; archive_mode is restart-only and shows up as pending_restart for the config node.
  • Point-in-time recovery of a whole Patroni cluster is a custom bootstrap of a new scope (patroniArchiveRecovering). Rendered; never run.
  • Shown in a container (Test.PgBackRestSpec, Layer 2): recovery to a timestamp on a plain cluster, and a second Patroni member made by pgBackRest rather than by a base backup. T7 on the three VMs is not run.
  • Two defects in the Patroni builtin surfaced on the way, both from keeping the configuration outside the data directory: bootstrap.pg_hba never reaches a replica (now postgresql.pg_hba), and Patroni does not create the postgresql.conf it renames (now seeded, empty, once). And Debian’s patroni does not pull in python3-etcd.

Switchover, under Patroni

pg-switchover.md’s role node has a counterpart here, and it is opt-in: a preferred leader.

• check: GET /leader names the preferred member.
• up: POST /switchover.

Choosing the target (also from the operator, pod.go, getSwitchoverCandidate): with synchronous_mode on, the candidate is the sync standby, and salmon waits for one to exist rather than falling back to an asynchronous member, since that member may lack acknowledged writes. Otherwise it is the lowest-lag member whose state is running, streaming or in archive recovery. If there is no candidate the switchover is skipped and retried on the next pass, with the leader left alone. The state that pass needs is re-derived from /cluster each time, which is the property PostgresPair protects.

The default is no preference. With a preference declared and supervision on, salmon would move the leader back after every automatic failover, as soon as the preferred member is healthy. That is sometimes what is wanted (a preferred site or a bigger machine) and sometimes a flap. It must be a choice, never the default.

Rolling maintenance of members

Restarting or upgrading every member (a Postgres minor update, a Patroni package) is one operation in three steps, and the order is the safety:

1. replicas, one at a time, waiting for each to report streaming again;
2. a switchover of the leader to the candidate chosen as above;
3. the old leader, now a replica.

The operator’s recreatePods does exactly this, and does not touch the leader when it has no safe candidate. Salmon’s version is a node whose check is “no member is on an old version or pending a restart” and whose up walks that order from the controller. It must tolerate being killed halfway: the next pass re-observes and continues.

Gating risky operations

Two guards the operator applies to operations that cannot be undone, both expressible with what already exists:

• A maintenance window (isInMaintenanceWindow in sync.go). Outside the window the operator freezes the desired version, so the change is never seen. Our Gate on upTreeWith (“does this traversal want to touch this node at all”) is the place: a time-window gate for nodes that restart or upgrade. The gate reports a Skip, and status shows why.
• Preconditions and a failure latch (majorversionupgrade.go). It runs only when every pod is running, every replica is streaming and lag is under 16 MB, and it labels the pods critical-operation so a node drain cannot interfere. After a failure it records an annotation and does not retry automatically until an operator clears it. The equivalents are the same preconditions in the node’s check (Unknown when unmet, so nothing starts), supGiveUpAfter with the node parked, and a way to tell sibling nodes on the same cluster to hold still while it runs (a Pause, see Op/Mailbox.hs); that last one has no counterpart yet.

Disaster scenarios

These reuse pg-switchover.md’s scenario catalogue where the cause is the same, with different expected outcomes, plus the ones only consensus makes meaningful.

Machines: three VMs, each with postgresql, patroni, etcd-server and haproxy in the rootfs (the guests cannot reach the network past boot, see Test.PostgresReplicationSpec). The addresses are testVmAddr/testVmAddr2/testVmAddr3. HAProxy and the client loop run on one of them, or on a fourth VM.

T3 is the test the whole design rests on, and the reason “the primary never appears in a directive” is a rule rather than a guideline.

Phased plan

1. Connection-string admin builtins (database, user, grants, templates, clones). Useful today for managed Postgres, and required here.
2. Etcd, with the seed and join phases; Layer 3, three VMs, including a restart of one member.
3. Patroni plus the cluster-wide config node; T1, T3, T4.
4. Routing: multi-host connection strings first, then HAProxy; T2, T6.
5. Archive and PITR (pgBackRest or WAL-G), and backups on one member; T7. pgBackRest builtin and the Patroni wiring are in; T7 itself is not run.
6. The opt-in preferred leader.

Open questions

• Debian’s pg_createconfig_patroni and patroni@ units, or our own? Theirs fits postgresql-common’s layout, which the rest of Postgres.hs assumes (pg_lsclusters, /var/lib/postgresql/$version/$cluster). Ours is simpler to reason about, and portable to non-Debian hosts.
• Should the config node restart members (patronictl restart --pending), or report pending_restart and leave the restart to an operator? Restarting is what run up means everywhere else. A restart of the leader under load is also a small outage. Decided (owner, 2026-09-26): restart the members that have pending_restart set, replicas first, and the leader behind a maintenance-window gate (what the Zalando operator does; see the leaning under the config node above).
• failsafe_mode default (see T5).
• REST API authentication: Patroni’s unsafe endpoints (/switchover, PATCH /config, /restart) need restapi.authentication. The credentials come in as a pre-provisioned file.
• Which etcd API: Patroni’s etcd3: section, the v3 API, is the only sensible one with current etcd. Say so in the builtin and do not offer v2.