Control-plane PostgreSQL loss and restore

The control-plane database is a primary with one synchronous streaming replica, a WAL archive and daily base backups (deploy/postgres, DR statement). Everything durable lives in it: deployments, events, jobs and leases, fleet nodes and certificates, network evidence and allocations. Losing one node is a failover, not a restore. This runbook covers what a replica cannot fix: both nodes lost, or bad data replicated to both (a wrong migration, an operator UPDATE). It covers point-in-time recovery from the base backup and WAL archive, taking and restoring a dump, and checking the restored state before anything is allowed to run against it.

$OWNER_URL is the migration-owner connection string, $RESTORED_URL the same for the new database pushleft_restored, and $OPS_URL is the read-only pushleft_ops role (deploy/README.md).

Symptoms

• service_not_ready fires for every /readyz: the API returns 500 from /readyz, and the scheduler, controller and gateway health listeners return 503.
• Journal lines such as placement reconciliation failed, controller reconciliation failed or pool timed out.
• Agents log agent lease request was rejected: 500 Internal Server Error or agent lease request failed: ... every 2 to 5 s.

What agents do while the gateway returns 5xx

From crates/pushleft-agent/src/main.rs:

• Lease loop: a transport error retries after 2 s and a non-2xx answer after 5 s, indefinitely. No new work starts.
• Running job: heartbeats fail and are logged. When the 45 s lease deadline passes without a renewal, the agent cancels local execution. Application containers that were already healthy, or that this attempt adopted, keep running. Only an unfinished rollout is stopped (application::stop_on_lease_loss).
• Completion: a failed completion is logged and dropped. The job stays leased in the database until its lease expires, and then it is retried under the same fencing. The work may run twice: delivery is at-least-once.
• Running workloads and their Caddy routes are not touched. Traffic keeps flowing.

Taking a dump (do this on a schedule, before every migration, and before a restore)

pg_dump --format=custom --file=/var/backups/pushleft/pushleft-$(date -u +%Y%m%dT%H%M%SZ).dump "$OWNER_URL"
pg_restore --list /var/backups/pushleft/pushleft-*.dump | head   # the archive is readable

Pushleft does not schedule this dump; the scheduled protection is the base backup and WAL archive below. A dump is still the portable format for a major-version change or a copy to another cluster. Copy the file off the host. The dump contains token and session hashes, enrollment secret hashes and network evidence, but no secret values. Vault references are stored, values are not.

WAL archive and PITR

archive_command (pushleft-pg-ha.sh archive) copies every completed WAL segment into /var/lib/pushleft/wal-archive atomically; archive_timeout = 60 bounds the archive’s lag on a quiet primary. pushleft-postgres-basebackup.timer runs pushleft-pg-ha.sh base-backup daily: a compressed tar base backup under /var/backups/pushleft/postgres/<stamp>/, the newest 7 kept, and archived WAL older than the oldest kept backup pruned with pg_archivecleanup. Both directories are local: mount remote storage there or copy them off the host (gap: no object-store copy or encryption is shipped). The archive is only as good as its last successful copy; control_plane_wal_archive_stale pages when archiving fails after its last success, or when completed segments have waited over 5 minutes.

Recover to a timestamp (just before the bad write) into a fresh data directory, on any host with PostgreSQL 17 and read access to the backup and archive:

set -a; . /etc/pushleft/postgres-ha.env; set +a
export PGDATA=/var/lib/postgresql/17/pitr          # empty, owned by postgres, 0700
/etc/pushleft/postgres/pushleft-pg-ha.sh pitr-restore /var/backups/pushleft/postgres/<stamp> '2026-10-17 03:17:13+00'
# Debian/Ubuntu keep postgresql.conf and pg_hba.conf in /etc, outside the backup:
cp /etc/postgresql/17/main/{postgresql,pg_hba,pg_ident}.conf "$PGDATA"/
# ...and they name the live cluster's directories: drop those lines from the copy.
sed -i '/^\(data_directory\|hba_file\|ident_file\|external_pid_file\)/d' "$PGDATA/postgresql.conf"
pg_ctl start -D "$PGDATA" -o "-p 5433"
psql -p 5433 -Atc "SELECT pg_is_in_recovery()"     # f once it reached the target and promoted

Use the newest backup taken before the target time. pitr-restore unpacks the base backup, writes recovery.signal, restore_command, recovery_target_time and recovery_target_action = 'promote', and sets archive_mode = off so the copy never writes into the production archive (its new timeline would collide with the real cluster’s next promotion). Recovery stops at the first commit after the target. Run the consistency checks below against the copy, then either dump the rows you need from it, or make it the new primary: stop and fence both old nodes, point the services at it, and build a new replica from it with bootstrap-replica. npm run test:control-plane-ha restores to a timestamp between two API writes in a fresh container and checks that exactly the earlier one is present.

Restore from a dump

1. Stop every control-plane writer so nothing writes to the old or the new database during the restore:

   systemctl stop pushleft-api pushleft-scheduler pushleft-agent-gateway pushleft-controller pushleft-mesh pushleft-telemetry-gateway pushleft-ops-alerts.timer

2. Create an empty database owned by the owner role, and restore into it:

   createdb --owner=pushleft_owner pushleft_restored
   pg_restore --exit-on-error --dbname="$RESTORED_URL" /var/backups/pushleft/pushleft-<stamp>.dump

   The cluster-wide roles (pushleft_api, pushleft_agent_gateway and the others) are not in a database dump. Recreate them first on a new cluster, using the SQL in deploy/README.md. The GRANTs are in the dump.
3. Bring the schema to the running release: DATABASE_URL="$RESTORED_URL" pushleft-api migrate.
4. Run the consistency checks below against pushleft_restored, then point the services at it: rename the databases, or change DATABASE_URL in each /etc/pushleft/*.env.
5. Start the gateway first, then the scheduler and the rest. Start pushleft-ops-alerts.timer last.

Consistency checks after restore

Run as $OPS_URL against the restored database. Each query should return no rows, unless the note says otherwise.

-- Restore point: the newest durable writes. Everything after this is lost.
SELECT max(occurred_at) AS last_event FROM deployment_events;
SELECT max(reported_at) AS last_result FROM agent_job_results;

-- Leases from before the restore. These expired long ago and are reclaimed by
-- the owning node's next lease (attempt < max_attempts requeues, otherwise
-- the job fails). Expect rows; they are not corruption.
SELECT id, kind, target_node_id, attempt, max_attempts, lease_expires_at FROM jobs
WHERE status = 'leased' ORDER BY lease_expires_at;

-- Results for jobs that do not exist, or results reported by another node (must be empty).
SELECT r.id FROM agent_job_results r LEFT JOIN jobs j ON j.id = r.job_id
WHERE j.id IS NULL OR (j.target_node_id IS NOT NULL AND j.target_node_id <> r.node_id);

-- Certificates: each active node has exactly the certificates you expect.
-- Certificates issued after the dump are missing from it. Those nodes now get
-- 401, because their fingerprint is unknown.
SELECT n.id, n.name, n.status, count(c.id) FILTER (WHERE c.revoked_at IS NULL AND c.not_after > now()) AS usable_agent_certs
FROM fleet_nodes n LEFT JOIN fleet_node_certificates c ON c.node_id = n.id AND c.purpose = 'agent'
WHERE n.status IN ('active','unhealthy','draining') GROUP BY n.id ORDER BY usable_agent_certs, n.name;

-- Revocations made after the dump are LOST. Re-apply them from your incident notes before starting the gateway:
--   UPDATE fleet_node_certificates SET revoked_at = now() WHERE fingerprint_sha256 = '<fp>' AND revoked_at IS NULL;

-- Allocations must not exceed capacity (must be empty).
SELECT n.id, sum(a.cpu_millis) AS cpu, n.capacity_cpu_millis, sum(a.memory_mb) AS mem, n.capacity_memory_mb
FROM fleet_nodes n JOIN fleet_allocations a ON a.node_id = n.id AND a.status IN ('reserved','active')
GROUP BY n.id HAVING sum(a.cpu_millis) > n.capacity_cpu_millis OR sum(a.memory_mb) > n.capacity_memory_mb;

-- One active ingress route per host and node (must be empty; a unique index enforces it).
SELECT lower(hostname), node_id, count(*) FROM ingress_analytics_routes WHERE status = 'active' GROUP BY 1, 2 HAVING count(*) > 1;

-- Event sequences have no gaps per deployment (must be empty).
SELECT deployment_id FROM deployment_events GROUP BY deployment_id HAVING max(sequence) <> count(*);

Then compare with reality. These are drift checks, and the database cannot answer them:

• Containers and routes created after the restore point: on each application node, docker ps --filter label=com.pushleft.organization and the Caddy route IDs pl-ingress-* are compared with application_instances and ingress_analytics_routes. A container or route the database does not know about is not adopted or removed automatically (gap). Record it, and remove it only through a fenced teardown once the deployment is known again. Do not delete it by name.
• Tokens and sessions created after the restore point no longer work. Revocations made after it are undone. Users must log in again, and you must re-revoke any token you revoked during that window.
• Subnet sequence: network_subnet_slots is restored to the dump’s value. Subnets allocated after the dump could be handed out again (gap). Before starting the scheduler and mesh, advance the sequence past every allocation that exists on hosts: SELECT setval('network_subnet_slots', <highest slot seen on hosts> + 1);.

Verification

• curl -fsS http://127.0.0.1:8080/readyz and every health listener return 2xx, and scripts/ops-alerts.sh exits 0 or fires only known items.
• pushleft-controller fleet list shows heartbeat age under 60 s for every active node within a minute of starting the gateway.
• One test deployment goes from pl up to live.

Do not

• Do not delete jobs, deployment_events, agent_job_results or auth_events rows to “clean up” after a restore. The append-only triggers refuse it, and the rows are the audit trail.
• Do not set jobs.target_node_id or status by hand to get work moving. Leases are reclaimed by the owning node.
• Do not run two databases with the services split between them, and never let an old primary come back as a writer after the restore. Fence old nodes (pushleft-pg-ha.sh fence) before the restored database takes writes.