Replication and failover

Why this lesson exists

Production PostgreSQL almost always runs with at least one replica: for failover when the primary dies, for read traffic, for backups taken off the primary. Replicas bring their own pages: "the replica is 20 minutes behind", "the primary's disk is full of WAL kept for a replica that was deleted months ago", "we promoted the replica and now both think they are the primary". This lesson builds a streaming replica on the lab box, measures its lag, and walks through slots, promotion and the split-brain trap.

What you need to know already: WAL, LSNs, base backups and standby.signal / recovery.signal (the last lesson), pg_hba.conf and the replication keyword (lesson 4), clusters and ports with pg_lsclusters (lesson 2).

The words you need first

• Streaming replication - the primary sends WAL over a normal connection to the replica as it is written; the replica replays it continuously. Physical: an exact copy of the whole cluster.
• walsender / walreceiver - the process on the primary that sends WAL, and the one on the replica that receives it. The startup process on the replica replays it.
• Hot standby - a replica that accepts read-only queries while it replays.
• Replication slot - a record on the primary of how far a replica has received; the primary keeps all WAL the slot still needs, even while the replica is offline.
• Lag - how far behind the replica is: in bytes (LSN difference) or in time.
• Promotion - ending recovery on a replica so it becomes a writable primary (on a new timeline).
• Split brain - two servers both accepting writes for the same data. The one thing a failover must never produce.
• Synchronous replication - a commit waits until a standby has the WAL (synchronous_commit, synchronous_standby_names): no data loss on failover, at the cost of latency.

Building a replica

On the primary: a role that may replicate, and a pg_hba.conf rule for it (replication is a keyword - all does not match it). On this box the replica is a second cluster, 18/replica on port 5433; on a real setup it is another machine with the same steps.

$ sudo -u postgres psql -c "create role replicator with login replication password 'repl-lab-pw'"
CREATE ROLE
$ echo "host    replication     replicator      127.0.0.1/32            scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
host    replication     replicator      127.0.0.1/32            scram-sha-256
$ sudo systemctl reload postgresql@18-main
$ sudo -u postgres psql -c "select name, setting from pg_settings where name in ('wal_level', 'max_wal_senders', 'max_replication_slots', 'hot_standby')"
         name          | setting
-----------------------+---------
 hot_standby           | on
 max_replication_slots | 10
 max_wal_senders       | 10
 wal_level             | replica
(4 rows)

The defaults (wal_level = replica, 10 WAL senders, 10 slots, hot_standby = on) are ready for replication. Now the replica: create the cluster's config, replace its empty data directory with a base backup taken from the primary over the replication protocol:

$ sudo pg_createcluster 18 replica -p 5433 > /dev/null
$ sudo rm -rf /var/lib/postgresql/18/replica
$ sudo -u postgres env PGPASSWORD=repl-lab-pw pg_basebackup -h 127.0.0.1 -p 5432 -U replicator -D /var/lib/postgresql/18/replica -R -C -S replica1 -X stream -c fast -P
56452/56452 kB (100%), 1/1 tablespace
$ sudo -u postgres cat /var/lib/postgresql/18/replica/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator password=repl-lab-pw channel_binding=prefer host=127.0.0.1 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica1'
$ sudo ls /var/lib/postgresql/18/replica/ | grep signal
standby.signal

-R wrote standby.signal and the primary_conninfo the replica connects with, -C -S replica1 created a replication slot for it on the primary, -X stream brought the WAL the copy needs. (Passwords in primary_conninfo end up in a file - in production use ~postgres/.pgpass on the replica instead.) Start it:

$ sudo pg_ctlcluster 18 replica start
$ pg_lsclusters
Ver Cluster Port Status          Owner    Data directory                 Log file
18  main    5432 online          postgres /var/lib/postgresql/18/main    /var/log/postgresql/postgresql-18-main.log
18  replica 5433 online,recovery postgres /var/lib/postgresql/18/replica /var/log/postgresql/postgresql-18-replica.log
$ sudo tail -n 4 /var/log/postgresql/postgresql-18-replica.log
2026-09-22 20:05:29.500 UTC [17448] LOG:  redo starts at 0/2A0AFE0
2026-09-22 20:06:26.900 UTC [17461] LOG:  started streaming WAL from primary at 0/2000000 on timeline 1
2026-09-22 20:06:26.900 UTC [17449] LOG:  consistent recovery state reached at 0/2A0AFE0
2026-09-22 20:06:26.900 UTC [17448] LOG:  database system is ready to accept read-only connections

online,recovery: running, replaying, and (with hot_standby) accepting reads.

Watching it

On the primary, one row per connected replica:

$ sudo -u postgres psql -x -c "select application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_state from pg_stat_replication"
-[ RECORD 1 ]----+-----------
application_name | 18/replica
client_addr      | 127.0.0.1
state            | streaming
sent_lsn         | 0/2A0AFB8
write_lsn        | 0/2A0AFB8
flush_lsn        | 0/2A0AFB8
replay_lsn       | 0/2A0AFB8
write_lag        |
flush_lag        |
replay_lag       |
sync_state       | async

state = streaming is healthy (catchup while it catches up after a pause). The four LSNs follow the WAL through the replica: sent -> written -> flushed to its disk -> replayed (visible to queries). The *_lag columns are times, measured from recent commits. Lag in bytes:

$ sudo -u postgres psql -c "select application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_lag_bytes from pg_stat_replication"
 application_name | replay_lag_bytes
------------------+------------------
 18/replica       | 0 bytes
(1 row)

On the replica:

$ sudo -u postgres psql -p 5433 -c "select pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() as since_last_replayed"
 pg_is_in_recovery | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | since_last_replayed
-------------------+-------------------------+------------------------+---------------------
 t                 | 0/2A0AFB8               | 0/2A0AFE0              |
(1 row)
$ sudo -u postgres psql -p 5433 -c "select status, sender_host, sender_port, slot_name from pg_stat_wal_receiver"
  status   | sender_host | sender_port | slot_name
-----------+-------------+-------------+-----------
 streaming | 127.0.0.1   |        5432 | replica1
(1 row)
$ sudo -u postgres psql -p 5433 -d orders -c "update orders set status = 'x' where id = 1"
ERROR:  cannot execute UPDATE in a read-only transaction

Writes are refused: cannot execute UPDATE in a read-only transaction. Note now() - pg_last_xact_replay_timestamp() grows on an idle primary (nothing to replay), so it is a lag signal only when the primary is busy - prefer the primary-side LSN difference.

What makes a replica fall behind:

Replication slots

$ sudo -u postgres psql -c "select slot_name, slot_type, active, restart_lsn, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained from pg_replication_slots"
 slot_name | slot_type | active | restart_lsn | wal_status | retained
-----------+-----------+--------+-------------+------------+----------
 replica1  | physical  | t      | 0/2A0AFB8   | reserved   | 0 bytes
(1 row)

A slot guarantees the primary keeps every WAL segment from its restart_lsn on - so a replica that was down for an hour can catch up without a new base backup. The catch: a slot whose replica never comes back keeps WAL forever. Stop the replica and write some data:

$ sudo pg_ctlcluster 18 replica stop
$ for i in 1 2 3; do sudo -u postgres psql -d orders -qc "update orders set updated_at = now()"; done
$ sudo -u postgres psql -c "select slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained from pg_replication_slots"
 slot_name | active | wal_status | retained
-----------+--------+------------+----------
 replica1  | f      | reserved   | 30 MB
(1 row)
$ sudo du -sh /var/lib/postgresql/18/main/pg_wal
81M	/var/lib/postgresql/18/main/pg_wal

active = f, and the retained WAL grows with every write - checkpoints cannot remove it. With a replica gone for good, this is the "disk full on the primary" incident (one of this chapter's pages). The cure is to drop the slot (select pg_drop_replication_slot('replica1')) - and to put a ceiling on it in advance:

• max_slot_wal_keep_size = '10GB' - beyond that the slot is given up (wal_status = lost; the replica will need a new base backup) instead of the primary filling its disk.
• idle_replication_slot_timeout (new in 18) - invalidates slots that have been inactive longer than this.
• Alert on active = f and on retained WAL per slot.

wal_status reads reserved (within max_wal_size), extended (kept beyond it because of the slot), unreserved (about to be removed) or lost. Bring the replica back so it catches up:

$ sudo pg_ctlcluster 18 replica start
$ sudo -u postgres psql -c "select slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained from pg_replication_slots"
 slot_name | active | retained
-----------+--------+----------
 replica1  | f      | 30 MB
(1 row)

Failover

The primary is gone - a dead host, a broken disk. Promote the replica:

$ sudo pg_ctlcluster 18 main stop -m immediate
$ sudo pg_ctlcluster 18 replica promote
waiting for server to promote.... done
server promoted
$ sudo tail -n 4 /var/log/postgresql/postgresql-18-replica.log
2026-09-22 20:17:20.950 UTC [17513] LOG:  archive recovery complete
2026-09-22 20:17:20.950 UTC [17527] LOG:  checkpoint starting: force
2026-09-22 20:17:20.950 UTC [17512] LOG:  checkpoint complete: wrote 7 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=0.002 s, sync=0.001 s, total=0.017 s; sync files=7, longest=0.001 s, average=0.001 s; distance=1 kB, estimate=1 kB; lsn=0/2A0B1E0, redo lsn=0/2A0B1E0
2026-09-22 20:17:20.950 UTC [17512] LOG:  database system is ready to accept connections
$ sudo -u postgres psql -p 5433 -Atc "select pg_is_in_recovery()"
f
$ sudo -u postgres psql -p 5433 -d orders -c "update orders set status = 'paid' where id = 1"
UPDATE 1

selected new timeline ID: 2, database system is ready to accept connections, and writes work. (select pg_promote() from SQL does the same.) With async replication, whatever the old primary had committed but not yet sent is lost - that is the RPO of asynchronous replication.

Then the part that decides whether this was a failover or a disaster:

1. The old primary must not come back as a primary. If it restarts (a reboot, systemd, a colleague) while apps still have its address, both servers take writes: split brain, and two diverging copies of your data. Fence it: stop it and make sure it stays stopped (echo disabled > /etc/postgresql/18/main/start.conf, systemctl mask, cut it off the network, or the cloud API stops the VM).
2. Repoint the clients: a DNS name or virtual IP that moves, a proxy (HAProxy, PgBouncer), or connection strings with several hosts and target_session_attrs=read-write (libpq tries each until it finds the writable one).
3. Rebuild redundancy: the old primary rejoins as a replica of the new one - with pg_rewind (rewinds its divergent changes; needs wal_log_hints or data checksums, on by default in 18) or a fresh base backup.

$ echo disabled | sudo tee /etc/postgresql/18/main/start.conf
disabled
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main    /var/log/postgresql/postgresql-18-main.log
18  replica 5433 online postgres /var/lib/postgresql/18/replica /var/log/postgresql/postgresql-18-replica.log

Doing all of that by hand at 3am is how mistakes happen. In production a manager does it with a consensus store so only one node can ever be primary: Patroni (etcd/Consul), pg_auto_failover, the Kubernetes operators (CloudNativePG, Crunchy PGO, Zalando), or the managed service's HA. Your job is to understand what it does so you can tell when it did the wrong thing.

In an interview: "The primary's disk is filling with WAL - what do you check?" - an inactive replication slot (pg_replication_slots with active = f and large retained WAL), a failing archive_command (pg_stat_archiver.failed_count), and checkpoints. Drop the stale slot (the replica it served needs a new base backup), and set max_slot_wal_keep_size so it cannot happen again. Never delete files from pg_wal by hand.

What you can do now

• Build a streaming replica with pg_basebackup -R -C -S and the right pg_hba.conf rule.
• Read pg_stat_replication, pg_stat_wal_receiver and replica-side functions, and measure lag in bytes and time.
• Name what makes a replica fall behind and where each shows.
• Manage replication slots: retained WAL, wal_status, max_slot_wal_keep_size, dropping stale ones.
• Promote a replica, fence the old primary, and explain split brain and pg_rewind.