PostgreSQL streaming replication (primary + standby)

Adds one or more hot standbys to an installed primary with pg_basebackup (replication user, pg_hba, replication slot, asynchronous or synchronous ANY/FIRST mode, lag monitoring) and produces the operations runbooks for this Patroni-less cluster: planned switchover, manual failover (pg_promote), rejoining the old primary with pg_rewind, rebuilding a standby, cascading and delayed standbys, slot cleanup, changing the synchronous settings. Failover is not automatic, the DBA performs it; for automatic failover use pg-patroni, for table-level or cross-version data flow use pg-logical-repl.

Topology: 1 primary + 1..N standbys

Scenarios

• Setup: add standbys to the primary — One or more hot standbys are added to an installed primary with pg_basebackup (slot, synchronous/asynchronous, monitoring). The primary stays open; the initial copy puts disk and network load on the primary.
• Planned switchover (role change, no data loss) — Planned maintenance: the primary is shut down cleanly, the first standby is promoted, the old primary comes back as a standby. No data loss; the write outage is usually under a minute.
• Manual failover (primary crashed, promote the standby) — The primary has crashed or is unreachable: the old primary is isolated (fencing) and the most up-to-date standby is promoted by hand. In asynchronous mode the last transactions can be lost.
• Rejoin the old primary as a standby with pg_rewind — After a failover the old primary is resynchronized to the new primary with pg_rewind and rejoins as a standby; much faster than a full copy.
• Add a new standby fed by a standby (cascading) — The new standby receives WAL from the first standby instead of the primary: in a remote data center or a setup with many standbys this reduces the primary's network/WAL sender load. The primary is not affected.
• Set up a delayed standby — A standby that applies WAL hours later (recovery_min_apply_delay): to recover accidentally deleted data without restoring from backup. It is not a failover candidate.
• Rebuild a standby from scratch — The standby has fallen behind, its WAL was removed or it is corrupt: the data directory is moved aside and copied again from the primary. The primary is not affected.
• pg_wal growing, disk filling up (cleaning up unused slots) — The cause of WAL accumulation is found (inactive slot, archive failure, wal_keep_size); the unused replication slot is dropped and WAL space is reclaimed. The primary stays open.
• Add / remove a synchronous standby (synchronous_standby_names) — Rewrites the synchronous list according to the mode in the form (ANY/FIRST, k) or reverts to asynchronous; applied with a reload, no restart needed. A wrong setting can leave commits hanging; it is checked first.

Before you start

• The primary … is installed with the pg-install guide and running (PostgreSQL …, PGDATA …, port …).
• On the standby servers only the operating system preparation phase of the pg-install guide (hostname, chrony, sysctl, THP) was done. The cluster was not created; the software is installed in this guide.
• The same operating system family, the same architecture (x86_64/aarch64) and the same PostgreSQL major version (…) on all servers. A standby is an exact physical copy of the primary.
• On the standby there is at least 20% more free space for … than the data size on the primary.
• The servers can reach each other on port …; hostnames resolve and clocks are synchronized with NTP.
• Port … is open between the servers in the firewall (if pg-install opened only the application network, add the replication IPs too).
• On Debian/Ubuntu the configuration files are outside the data directory (/etc/postgresql), so pg_basebackup does not copy them; you have scp access from the standby to the primary as root (or a user with sudo), or you will move the files by hand.
• The old primary's data directory is intact and the server can start (after a disk failure, rebuilding with pg_basebackup is needed, not pg_rewind).
• …A suitable time has been chosen for the initial copy (pg_basebackup), which puts load on the primary.…The maintenance window is open; the application teams know about the short write outage.…It has been confirmed that the primary is really unreachable; it is guaranteed that it will not come back and accept writes as a second primary (fencing).…The new primary is healthy and accepting writes.…The new server (…) is ready: operating system preparation is done, … is empty and has enough space; the initial copy will put load on the source (…).…While … is rebuilt, its read load can be moved to the other servers; the copy will put load on the primary.…Disk usage is being monitored; before dropping the slot, the owner of the standby using it has been consulted.…The synchronous change is in a maintenance window; the application teams are informed because commit latency will change.…

Phases of the guide

• Preparation (all servers) — Add the server names to /etc/hosts · Add the PGDG repository · Add the PGDG repository · Install the PostgreSQL server software · Prepare the data directory · Create the cluster configuration and empty the data directory
• Replication preparation on the primary — Check the pg_rewind prerequisite · Write the replication settings file · Load the settings · Create the replication user · Set the replication user's password · Add the replication rules to pg_hba.conf …
• Standby setup (pg_basebackup) — Test the replication connection to the primary · Take a copy of the primary: … · Copy the configuration files from the primary · Start the standby
• Verification — Check the replication status on the primary · Check the WAL receiver on the standby · Write test data on the primary · Read the test data on the standby · Drop the test table
• Enable synchronous replication — Write the synchronous settings file · Load the setting on all servers · Verify the synchronous status
• Monitoring and post-installation — Lag monitoring query (primary) · Lag monitoring query (standby) · Slot usage monitoring · Application connection string · Adapt the backup to the standby
• Pre-switchover checks — Check the status of the standbys · Verify that the new primary is ready · Check .pgpass and pg_hba on the old primary
• Switchover — Shut down the old primary cleanly · Verify that the new primary received the last WAL · Promote … · Create the slots on the new primary · Configure the old primary as a standby · Start the old primary as a standby …
• Manual failover — Stop and disable the old primary (fencing) · Identify the most up-to-date standby · Promote … · Temporarily remove the synchronous wait · Create the slots on the new primary · Point the other standbys to the new primary: … …
• Rejoin the old primary with pg_rewind — Check the slot and connection rule on the new primary · Verify that the old primary is stopped · pg_rewind dry run · Run pg_rewind · Complete the standby settings · Start the old primary as a standby …
• pg_wal bloat and slot cleanup — Why is pg_wal growing? · Find the slot's owner · Drop the unused slot: … · Dropping a slot: fill in the form · Verify that WAL space has been reclaimed · Prevent a recurrence: upper limit and monitoring
• Synchronous standby change — Current synchronous status · Safety check: are enough standbys connected? · Write the synchronous settings file · Remove the synchronous setting (revert to asynchronous) · Load the setting on all servers · Verify the new status