PostgreSQL primary failover runbook

Use this runbook when a PostgreSQL primary using streaming replication has failed or become unreachable and you need to promote a replica. It covers confirming the outage, choosing the replica with the least data loss, fencing the old primary, promoting, repointing clients, and verifying the new topology.

When to use this

• The primary is not accepting connections, and you have ruled out a client-side or network problem.
• Replication is managed by hand: you run primary_conninfo and standbys yourself.
• Do not use this runbook if Patroni, repmgr, or a managed service (RDS, Cloud SQL) controls the cluster. Use that tool’s failover command instead. A manual promotion behind its back can cause split brain.

Before you start

• psql and SSH access to the old primary, every replica, and the PgBouncer host.
• A role that can run pg_promote(). Superusers can by default; other roles need GRANT EXECUTE ON FUNCTION pg_promote TO ....
• PostgreSQL 13 or later on the replicas. pg_promote() needs 12+, and reloading primary_conninfo without a restart needs 13+.
• A second engineer to approve each step that changes production.

Variables

• {{old_primary_host}}: hostname of the failed primary
• {{replica_host}}: the replica you will promote
• {{other_replica_host}}: each replica that will follow the new primary
• {{pg_port}}: PostgreSQL port, usually 5432
• {{pg_user}}: administrative role used for psql
• {{db_name}}: database to connect to for checks
• {{replication_user}}: role the replicas use for streaming
• {{pg_service}}: systemd unit, for example postgresql@16-main
• {{pgbouncer_host}}, {{pgbouncer_port}}, {{pgbouncer_ini}}: PgBouncer host, admin port and config path

Steps

1. Confirm the primary is down

Purpose: make sure this is a real outage and not a network or client problem.

pg_isready -h {{old_primary_host}} -p {{pg_port}} -t 5; echo "exit=$?"
psql "host={{old_primary_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}} connect_timeout=5" -Atc "SELECT pg_is_in_recovery();"

Expect: exit code 2 (no response) or 1 (rejecting connections), and psql times out. Exit 0 means the server is up. Decision: if it is up, stop and investigate the clients or the network.

2. Check replication state and lag on every replica

Purpose: find the replica that received the most WAL.

psql "host={{replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}} connect_timeout=5" -Atc "
SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_backlog_bytes,
       now() - pg_last_xact_replay_timestamp() AS since_last_replay;"
psql "host={{replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -Atc "SELECT status, sender_host FROM pg_stat_wal_receiver;"

Expect: t for recovery, and a receive LSN for each replica. With the primary gone, the WAL receiver usually shows no row or a non-streaming status. Decision: promote the replica with the highest pg_last_wal_receive_lsn. Wait until replay_backlog_bytes reaches 0 so everything received gets applied. Anything the primary committed past that LSN is lost. If that is unacceptable, escalate before continuing.

3. Fence the old primary

Purpose: make sure the old primary cannot come back as a second writable node.

Requires approval: stops and masks the database service on a production host.

ssh {{old_primary_host}} "sudo systemctl stop {{pg_service}} && sudo systemctl mask {{pg_service}} && systemctl is-active {{pg_service}}"

Expect: inactive. Decision: if the host is unreachable, fence it at the infrastructure layer (stop the instance, or detach it from the network or security group) and confirm. Do not promote until the old primary is fenced.

4. Promote the chosen replica

Purpose: turn the replica into a writable primary on a new timeline.

Requires approval: makes {{replica_host}} writable. This cannot be undone without rebuilding the node.

psql "host={{replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -Atc "SELECT pg_promote(wait => true, wait_seconds => 60);"

Expect: t. If it returns f, promotion did not finish within 60 seconds. Check the server log. The equivalent on the host is sudo -u postgres pg_ctl promote -D <data_dir>, which prints server promoting. On Debian and Ubuntu, pg_ctl lives under /usr/lib/postgresql/<version>/bin/.

5. Repoint clients through PgBouncer

Purpose: send application traffic to the new primary.

Requires approval: changes where every application connection goes.

ssh {{pgbouncer_host}} "sudo sed -i.bak 's/host={{old_primary_host}}/host={{replica_host}}/' {{pgbouncer_ini}} && grep -n 'host=' {{pgbouncer_ini}}"
psql -h {{pgbouncer_host}} -p {{pgbouncer_port}} -U pgbouncer pgbouncer -c "RELOAD;"

Expect: the [databases] entries show host={{replica_host}}, and RELOAD returns without error. Decision: if clients connect through DNS instead, update the record and wait out its TTL. Clients that cache resolved addresses may need a restart.

6. Point the remaining replicas at the new primary

Purpose: restore redundancy.

Requires approval: changes replication on each production replica.

psql "host={{other_replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -c "ALTER SYSTEM SET primary_conninfo = 'host={{replica_host}} port={{pg_port}} user={{replication_user}}';" -c "SELECT pg_reload_conf();"
psql "host={{other_replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -Atc "SELECT status, sender_host FROM pg_stat_wal_receiver;"

Expect: after a few seconds, streaming|{{replica_host}}. The default recovery_target_timeline = 'latest' lets replicas follow the new timeline. Keep passwords in .pgpass, not in primary_conninfo. Decision: if a replica never reaches streaming and its log reports a timeline fork, it replayed past the promotion point. Rebuild it (see below).

Verify

psql "host={{replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -Atc "SELECT pg_is_in_recovery(), current_setting('transaction_read_only'), (SELECT timeline_id FROM pg_control_checkpoint());"
psql "host={{replica_host}} port={{pg_port}} user={{pg_user}} dbname={{db_name}}" -Atc "SELECT client_addr, state, sync_state FROM pg_stat_replication;"
psql "host={{pgbouncer_host}} port=<app_port> user=<app_user> dbname={{db_name}}" -Atc "SELECT inet_server_addr(), pg_is_in_recovery();"

Expect: f|off| with a higher timeline ID than before, a streaming row for each follower, and application connections reaching the new primary’s address with f. Watch application error rates until they return to baseline.

Roll back or escalate

You cannot undo a promotion. Do not restart the old primary as a writable server. To bring it back as a replica, run pg_rewind --target-pgdata=<data_dir> --source-server="host={{replica_host}} ...". This only works if wal_log_hints or data checksums were enabled. Otherwise, rebuild it with pg_basebackup. Unmask the service only after it is configured as a standby.

Escalate right away if:

• Two nodes report pg_is_in_recovery() = f (split brain). Stop writes to one of them first.
• pg_promote fails.
• The old primary cannot be fenced.
• The data-loss window from step 2 is larger than your recovery point objective allows.

Running this runbook in Runspace

In Runspace, each step runs inline and its output streams under the step. A value captured in step 2, such as the chosen replica, can fill {{replica_host}} in later steps. Each “Requires approval” step needs a separate reviewer, and the approval is pinned to the exact command and runbook revision. A server-side audit log records who ran what and who approved it. Runspace is in private pilots. Request a pilot and we’ll reach out to set it up with your team.