Control-plane PostgreSQL failover

The control-plane database runs as a primary with one streaming replica, a WAL archive and daily base backups (deploy/postgres, deploy/README.md). This runbook moves the writer to the replica when the primary is lost, without ever having two writers. For restoring from backup or to a point in time, see control-plane PostgreSQL restore.

Everything here is manual. Nothing detects a failed primary and promotes on its own: there is no Patroni, no witness and no virtual IP. An operator decides, fences, promotes and repoints. That is deliberate. Without a quorum, automatic promotion is how two writers happen.

scripts/control-plane-postgres-ha-e2e.sh (npm run test:control-plane-ha) runs every step below against the pinned PostgreSQL image on Docker. Measured on 2026-10-17 on a dev Mac (Docker Desktop, debug API build): 4.92–4.96 s from SIGKILL of the primary to the first API write on the promoted node over three runs (readyz red at 4.25 s, fenced at 4.5 s, promoted at 4.7 s). Most of it is the readiness probe waiting out its 2 s database timeout against the dead primary; fence, promote and API restart together take about 0.7 s. That is the scripted time. It does not include noticing the page and deciding, which is the real RTO in practice.

Symptoms

• service_not_ready for the API and every health listener (/readyz returns 503, {"database":{"reachable":false}}).
• control_plane_replication_lag on the primary, if it is still up but the replica is not streaming.
• Agents log lease failures and retry. Running workloads and routes keep serving (what agents do).

Decide

Fail over only when the primary is gone or cannot be brought back quickly. If it is a restart or a short network blip, wait: with synchronous commit, a primary that comes back has lost nothing.

Check the replica is a usable target:

psql "$REPLICA_OWNER_URL" -Atc "SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp(), pg_last_wal_replay_lsn()"

t and a replay age that matches the outage. With synchronous_standby_names = 'ANY 1 (pushleft_replica)' (set by enable-sync) every COMMIT the API acknowledged was flushed on the replica before the client saw success, so the RPO for losing one node is 0. If sync had been switched off (disable-sync, see below), anything after the replica’s last replayed LSN is lost.

1. Fence the old primary (before anything else)

The old primary must not be able to accept a write again. Use the first that applies, and record which in the incident notes:

1. Its disk is reachable (host up, PostgreSQL dead or hung): on that host, as postgres,

   set -a; . /etc/pushleft/postgres-ha.env; set +a
   /etc/pushleft/postgres/pushleft-pg-ha.sh fence
   systemctl mask --now postgresql@17-main

   fence stops a running server with -m immediate and writes standby.signal and pushleft.fenced into the data directory. If the server is started again it comes up in recovery and refuses writes (cannot execute INSERT in a read-only transaction; the e2e proves this).
2. The host is unreachable: power it off through the provider (Hetzner Robot/Cloud console or API), or remove it from the private network / block 5432 from every control-plane host in the provider firewall. Do not rely on “it is probably dead”.

Only when one of these is done:

2. Promote the replica

On the replica host, as postgres:

set -a; . /etc/pushleft/postgres-ha.env; set +a
PUSHLEFT_PG_OLD_PRIMARY_FENCED=yes /etc/pushleft/postgres/pushleft-pg-ha.sh promote

The script refuses without PUSHLEFT_PG_OLD_PRIMARY_FENCED=yes. It runs pg_ctl promote, resets synchronous_standby_names (otherwise every write would wait for the dead node), and creates the pushleft_replica slot for the future replica. The new primary now runs asynchronously until a replica rejoins.

3. Repoint the services

Change the host in DATABASE_URL in every /etc/pushleft/*.env (api, scheduler, agent-gateway, controller, mesh, telemetry-gateway, ops-alerts), then:

systemctl restart pushleft-agent-gateway pushleft-scheduler pushleft-api pushleft-controller pushleft-mesh pushleft-telemetry-gateway
curl -fsS http://127.0.0.1:8080/readyz | jq .database

/readyz is 200 with "in_recovery": false. A service still pointed at a standby (or at the fenced node) answers 503 with "in_recovery": true, and so do the health listeners: readiness refuses a standby, so a half-done repoint is visible. There is no DNS name or VIP that moves; using a DNS name in DATABASE_URL and changing the record is equivalent, but you still restart, because pools do not reconnect on their own to a different address.

4. Rejoin the old primary as the new replica

Chosen: re-base with pg_basebackup, not pg_rewind. Re-basing always works, including after timeline divergence and lost WAL, and the control-plane database is small, so the full copy is cheap. wal_log_hints = on is set, so pg_rewind --source-server=<new primary> is an option if the database grows; it is not scripted or tested.

On the old primary host (still fenced, PostgreSQL stopped), with PUSHLEFT_PG_PRIMARY_HOST set to the new primary:

PUSHLEFT_PG_REBASE=1 /etc/pushleft/postgres/pushleft-pg-ha.sh bootstrap-replica   # wipes only a fenced or standby data dir
systemctl unmask --now postgresql@17-main

On the new primary:

psql -Atc "SELECT application_name, state, sync_state FROM pg_stat_replication"   # pushleft_replica|streaming|async
/etc/pushleft/postgres/pushleft-pg-ha.sh enable-sync                             # → quorum

On the new primary’s hba, configure-primary’s replication line came across in the base backup; check that it names the other host’s address. Move pushleft-postgres-basebackup.timer to the new primary, set PUSHLEFT_OPS_REPLICA_DATABASE_URL in ops-alerts.env to the new replica, and run scripts/ops-alerts.sh: it should exit 0.

Replica lost, primary fine

With sync on, writes block while the replica is away (that is the price of RPO 0). If it will not be back within minutes:

/etc/pushleft/postgres/pushleft-pg-ha.sh disable-sync      # on the primary: writes flow again, RPO > 0

Re-base the replica (bootstrap-replica, PUSHLEFT_PG_REBASE=1 if its data dir is a standby), start it, then enable-sync. Its slot keeps up to max_slot_wal_keep_size (20 GB) of WAL; beyond that the slot is invalidated and only a re-base works.

Alerts

• control_plane_replication_lag (page): a pushleft_* slot on the primary is inactive, retains over 64 MiB, or the replica replays over 30 s behind (needs pg_read_all_stats for the time). It is also fired by ops-alerts.sh when PUSHLEFT_OPS_REPLICA_DATABASE_URL is unreachable or not in recovery, which means someone promoted it without repointing or fencing: two writers. Stop the services, find out which node has the newest writes, fence the other.
• control_plane_wal_archive_stale (page): see the restore runbook.

Do not

• Do not promote before the old primary is fenced, even if it “looks dead”.
• Do not pg_ctl start a node that has pushleft.fenced without first re-basing it.
• Do not point some services at one node and some at the other.
• Do not restore a PITR copy over the shared archive: pitr-restore turns archiving off for the copy, keep it that way.