Replication and failover
A replica only helps on the bad night if it is close behind, its WAL is not filling the primary's disk, and everyone agrees who the primary is. This lesson gives you the operator's view of the order database's replicas: how they connect, how far behind they are, and how a failover happens without two primaries.
By the end you can
- Configure a standby by hand with
primary_conninfo, a replication slot andstandby.signal, and cap what a slot may keep - Choose
synchronous_standby_namesandsynchronous_commitfor a three-node cluster and explain what each choice costs - Measure lag in bytes and in time, and read hot standby conflicts on a reporting replica
- Explain failover, switchover, split brain and fencing, and how Patroni with etcd and
pg_rewindhandle them - Route clients to the current primary and say what a managed service does for you and what stays your job
The everyday picture
A busy restaurant kitchen has a head chef and a sous-chef. Every order ticket the head chef handles goes on a spike, and the sous-chef copies each one so she could take over at any moment. The rule of the spike: no ticket is thrown away until the sous-chef has read it.
One evening the second sous-chef goes home sick and nobody tells the spike. His unread tickets stay, hour after hour, until the spike is full and the kitchen stops taking orders.
Now the head chef collapses. Only one person may wear the head chef's hat, and the hat lives on a hook that the manager watches. If the old head chef wakes up and starts shouting orders while the sous-chef also does, the kitchen cooks two different dinners for the same table. And the waiters must be told whose orders to follow now. Replication slots, fencing and client routing are these three rules for a database.
How a standby finds its primary, and the slot that keeps its place
You met streaming replication and pg_basebackup -R in Replication and read replicas. Here is what that command wrote, so you can build and repair a standby by hand. Two things turn a copy of the data directory into a standby. The first is an empty file named standby.signal in the data directory. The second is the setting primary_conninfo, a normal connection string: host, port, the replication user and an application_name, which becomes the replica's name in pg_stat_replication. Since PostgreSQL 12 the setting lives in the normal configuration files and the signal file replaces the old recovery.conf. Since PostgreSQL 13 you can change primary_conninfo with a reload instead of a restart.
The primary removes old WAL files after each checkpoint, once it no longer needs them for crash recovery. A standby that falls far behind, or is down for a few hours, comes back and asks for WAL that no longer exists. The log says requested WAL segment ... has already been removed. It can then fetch the missing files from the WAL archive with restore_command (Backups you have actually restored), or you clone it again from scratch, which takes hours for an 800 GB database.
A replication slot fixes this. The standby names its slot in primary_slot_name, and the primary keeps every WAL file from the slot's restart_lsn onward, however long that takes. That promise is also the danger. If the consumer of a slot disappears, the slot stays, and WAL piles up in pg_wal until the disk is full. A primary that cannot write WAL stops accepting writes. One forgotten slot can take the whole order database down.
max_slot_wal_keep_size draws the dashed line: past it, Postgres gives up on the slot instead of on the primary.The guard is max_slot_wal_keep_size. Its default is -1, which means no limit. Set it to a size your WAL disk can hold with room to spare, and a slot that falls further behind is invalidated: its wal_status in pg_replication_slots goes from reserved to extended, then unreserved, then lost. A lost slot means that replica must be re-cloned or fed from the archive, which is far better than a stopped primary. PostgreSQL 17 adds an inactive_since column, and PostgreSQL 18 adds idle_replication_slot_timeout to invalidate slots that sit unused too long.
Quick check
A replica with a slot is switched off for a weekend of maintenance. The primary has max_slot_wal_keep_size = -1. What happens on the primary during the weekend?
A slot promises to keep WAL until its consumer reads it, and -1 means no size limit, so the WAL stays and grows with every write. Nothing drops a slot by default. Without a slot, the first answer would be partly true: WAL would be removed, and the replica would need the archive on Monday.