PostgreSQL Streaming Replication and Safe Failover

Streaming replication is easy to set up and easy to get wrong at the moment of failover. Promoting a standby is a single command, but the harder question is what happens to the old primary when it comes back: its timeline has diverged, WAL has been written that the new primary never saw, and naively restarting it as a standby either fails or silently corrupts the cluster. PostgreSQL's answer is timeline-based reconciliation with pg_rewind. This article covers the replication setup, a promotion that preserves the data, and the rewind procedure that returns the old primary to service as a standby.

Replication basics

A standby needs a base backup, a standby.signal file, and connection settings for the primary. WAL is streamed through the replication protocol by default, with archiving as the fallback for gaps.

-- on the primary
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';

-- postgresql.conf on the primary
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

# pg_hba.conf on the primary
host replication replicator 10.0.0.0/24 scram-sha-256

# on the standby
pg_basebackup -h 10.0.0.10 -U replicator -D /var/lib/pgsql/16/data -Fp -Xs -P -R
# -R writes standby.signal and the primary_conninfo entry for you

wal_keep_size protects against a briefly disconnected standby falling too far behind, but the real answer for gaps is a WAL archive and a restore_command. Without either, a long outage means rebuilding the standby from a fresh base backup.

Verify replication before you need it

-- on the primary
SELECT client_addr, state, sync_state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- on the standby
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

A standby that receives WAL but replays it slowly is a different problem from one that is not receiving it, and the two columns distinguish them. Check both before any planned change, and record the lag so you can tell afterwards whether the incident was pre-existing.

Promotion

# on the standby
pg_ctl promote -D /var/lib/pgsql/16/data
# or, in PostgreSQL 12 and later
SELECT pg_promote();

SELECT pg_is_in_recovery();   -- expect f

If the old primary is still running and reachable, stop it before or immediately after promotion — split brain with both nodes accepting writes is the one failure that pg_rewind cannot repair without losing one side's transactions. Take a checkpoint on the new primary as soon as possible after promotion, so its control file reflects the new timeline information that pg_rewind will later compare.

Rejoining the old primary with pg_rewind

pg_rewind synchronizes a diverged cluster with another copy of the same cluster. It copies only the changed relation blocks plus all non-relation files, then arranges for WAL to replay from the divergence point — far cheaper than a full base backup.

# on the old primary, with postgresql stopped
pg_rewind --target-pgdata=/var/lib/pgsql/16/data \
          --source-server="host=10.0.0.11 port=5432 user=postgres dbname=postgres" \
          --write-recovery-conf \
          --dry-run

# remove --dry-run once the plan looks right

Requirements and pitfalls worth knowing before you rely on this:

• The target must have wal_log_hints enabled or have been initialised with data checksums. Without one of the two, pg_rewind refuses to run.
• WAL from the divergence point to the present must be available — on disk in pg_wal or through the archive, in which case use -c to retrieve it.
• Configuration files are copied wholesale from the source, including postgresql.conf. If the two hosts intentionally differ, fix the configuration before restarting the rewound server.
• --write-recovery-conf creates standby.signal and appends connection settings, which is what you want when the goal is to reintroduce the node as a standby.

The tool is not limited to failover: promoting a standby, running writes on it, and rewinding it back to a follower is a legitimate pattern for maintenance that requires a writable node.

Making failover repeatable

Manual promotion works, but at three in the morning it is slow and error-prone. Whichever automation you choose — Patroni, repmgr, or your own script — the value comes from the same three properties: a consensus mechanism or fencing rule so only one node promotes, a documented procedure for the rewound node, and observability. Ship the logs somewhere queryable, as described in Grafana Loki and Promtail log pipelines, and keep base backups and WAL archives on a separate failure domain using the incremental technique in rsync incremental backups with link-dest. Test the full cycle — promote, rewind, rejoin — at least once per quarter, because that path is the part nobody practises. For the caching layer that usually sits in front of a database like this, the trade-offs are covered in Redis Sentinel versus Redis Cluster.

原文链接：https://www.postgresql.org/docs/16/app-pgrewind.html