Sev1: PostgreSQL primary is gone and there's no automatic failover. Promoting a standby safely

#Before you promote: decide

Promote if the primary will not be back quickly (hardware failure, lost VM, data corruption) and the business wants service back now. If the primary will be back in minutes after a reboot, waiting may lose less data.

#Step 1: check the standby

-- on the standby
SELECT pg_is_in_recovery();                                   -- must be true
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

• receive_lsn = how far WAL arrived; replay_lsn = how far it's applied. Promotion replays what's received first.
• With asynchronous replication, transactions committed on the primary after the last received WAL are lost. Note the last replay timestamp for the incident record.

If you have several standbys, promote the one with the highest pg_last_wal_receive_lsn().

#Step 2: fence the old primary

Two primaries accepting writes (split-brain) is worse than the outage. Make sure the old primary can't come back as primary:

• Stop it if reachable: systemctl stop postgresql-16 and disable auto-start.
• If unreachable: power it off via the hypervisor/cloud console, or block its port at the firewall/security group.
• Remove it from the load balancer / DNS / connection pooler.

#Step 3: promote

SELECT pg_promote(wait => true, wait_seconds => 60);

or

sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data

Check:

SELECT pg_is_in_recovery();   -- false
SELECT timeline_id FROM pg_control_checkpoint();

The timeline increases by one after promotion.

#Step 4: repoint applications

Restart application pools if they cache connections or DNS.

#Step 5: protect the new primary

• Take a base backup (or check your backup tool, e.g. pgBackRest, now runs against the new primary with the new timeline).
• Archiving: confirm archive_command works on the new primary.
• Other standbys: point their primary_conninfo to the new primary. They follow the new timeline if recovery_target_timeline = 'latest' (the default since PG 12).

#Step 6: rebuild the old primary as a standby

When the old server is back, don't just start it. Rewind it to the new timeline:

# old primary stopped cleanly; requires wal_log_hints=on or data checksums
sudo -u postgres pg_rewind --target-pgdata=/var/lib/pgsql/16/data \
  --source-server="host=db2 port=5432 user=postgres dbname=postgres" --progress
touch /var/lib/pgsql/16/data/standby.signal
# set primary_conninfo in postgresql.auto.conf to point to db2, then start

If pg_rewind isn't possible (no checksums/wal_log_hints, or the server crashed hard), recreate it with pg_basebackup.

Caution

If the old primary accepted writes after the standby was promoted (fencing failed), those writes exist only there. Before rewinding, extract them (pg_waldump, or dump the changed tables) and reconcile with the application team. pg_rewind throws them away.

#Prevent it next time

• Use a failover manager (Patroni, or a managed service) with fencing built in.
• Synchronous replication (synchronous_standby_names) where data loss must be zero.
• wal_log_hints = on or data checksums, so pg_rewind always works.
• A written and practised manual promotion runbook for environments without automation.