What is it? #
Goal: build working streaming replication, break it in several ways, and perform a failover — so that the first time you do it is not during an outage.
Replication is one of those topics that reads simply and contains a number of surprises in practice: a standby that falls behind and cannot catch up, a synchronous configuration that blocks every commit when one server goes away, an abandoned slot that fills the primary's disk, and a promoted standby whose old primary cannot simply be restarted.
Every one of those is reproducible in a lab in a few minutes, and each is much better met there.
Two containers or two small VMs are enough. The whole project runs on a laptop.
Think of it like this #
A fire drill for the database.
The procedure is short and everyone thinks they know it. Running it once reveals the parts nobody had thought about — who decides, what order things happen in, and what to do with the building you just evacuated.
The value is entirely in having done it before.
Simple example #
Two PostgreSQL instances: a primary on port 5432 and a standby on 5433.
You will replicate between them, measure lag under load, deliberately disconnect the standby, watch WAL retention on the primary, switch to synchronous and observe what happens when the standby disappears, then promote the standby and discover why the old primary cannot rejoin as-is.
Code #
# ---------- PART 1: two instances ----------
# Option A: Docker
docker network create pglab
docker run -d --name pg_primary --network pglab \
-e POSTGRES_PASSWORD=labpass -p 5432:5432 postgres:16
docker run -d --name pg_standby --network pglab \
-e POSTGRES_PASSWORD=labpass -p 5433:5432 postgres:16
docker stop pg_standby # the standby will be CLONED, not initialised
# Option B: two clusters on one Debian/Ubuntu machine
# sudo pg_createcluster 16 primary --port 5432
# sudo pg_createcluster 16 standby --port 5433-- ---------- PART 2: prepare the PRIMARY ----------
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replpass';
-- REPLICATION allows streaming the WAL and grants NO table access.
SELECT pg_create_physical_replication_slot('standby1');
-- The slot makes the primary RETAIN WAL until the standby has consumed it.
-- Seed some data so replication has something to carry:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO events (payload)
SELECT 'seed ' || i FROM generate_series(1, 10000) AS i;# ---------- PART 3: primary configuration ----------
# postgresql.conf
wal_level = replica # the default; required
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = '2GB' # SAFETY BOUND on abandoned slots
listen_addresses = '*' # lab only — restrict this in production
# pg_hba.conf — note the literal word "replication"
# TYPE DATABASE USER ADDRESS METHOD
host replication replicator 0.0.0.0/0 scram-sha-256
docker restart pg_primary # wal_level/max_wal_senders need a restart# ---------- PART 4: clone the primary onto the standby ----------
docker run --rm --network pglab -v pg_standby_data:/data \
-e PGPASSWORD=replpass postgres:16 \
pg_basebackup -h pg_primary -U replicator -D /data \
-S standby1 \ # use the slot
-R \ # WRITES standby.signal + primary_conninfo for you
-Xs -P
# -R is what turns a plain copy into a configured standby.
# Without it you would create standby.signal and set primary_conninfo by hand.
docker start pg_standby-- ---------- PART 5: confirm it is working ----------
-- On the STANDBY (port 5433):
SELECT pg_is_in_recovery(); -- t = it is a standby
-- On the PRIMARY (port 5432):
SELECT client_addr, state, sync_state,
sent_lsn, replay_lsn,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_bytes
FROM pg_stat_replication;
-- state should be "streaming", sync_state "async"
-- Prove data flows:
-- primary:
INSERT INTO events (payload) VALUES ('replication test');
-- standby (should appear within milliseconds):
SELECT * FROM events ORDER BY id DESC LIMIT 1;
-- standby is READ-ONLY:
INSERT INTO events (payload) VALUES ('nope');
-- ERROR: cannot execute INSERT in a read-only transaction# ---------- EXPERIMENT 1: lag under write load ----------
# Generate sustained writes on the primary:
psql -p 5432 -U postgres -c "
INSERT INTO events (payload)
SELECT 'load ' || i FROM generate_series(1, 2000000) AS i;" &
# Watch lag on the standby while that runs:
watch -n1 "psql -p 5433 -U postgres -tAc \
\"SELECT now() - pg_last_xact_replay_timestamp();\""
# EXPECT: lag rises during the load, then returns to near zero.
# Lag that keeps growing and never recovers means the standby cannot
# keep up — usually slower disks or insufficient resources.# ---------- EXPERIMENT 2: disconnect the standby ----------
docker stop pg_standby
# On the primary, watch WAL accumulate for the inactive slot:
psql -p 5432 -U postgres -c "
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
AS wal_retained
FROM pg_replication_slots;"
# Write more data, then check again. wal_retained GROWS.
# THIS IS THE MECHANISM THAT FILLS PRODUCTION DISKS:
# an abandoned slot retains WAL forever. max_slot_wal_keep_size bounds it.
docker start pg_standby
# The standby catches up from the slot. Watch lag fall back to zero.# ---------- EXPERIMENT 3: synchronous replication ----------
# On the primary:
psql -p 5432 -U postgres -c "
ALTER SYSTEM SET synchronous_standby_names = 'standby1';
SELECT pg_reload_conf();"
# Requires application_name to match. Set it in primary_conninfo on the
# standby (postgresql.auto.conf):
# primary_conninfo = '... application_name=standby1'
docker restart pg_standby
psql -p 5432 -U postgres -c "SELECT application_name, sync_state FROM pg_stat_replication;"
# sync_state should now be "sync"
# NOW THE IMPORTANT PART — stop the standby:
docker stop pg_standby
psql -p 5432 -U postgres -c "INSERT INTO events (payload) VALUES ('blocked?');"
# !! THIS HANGS. With one synchronous standby and no standby available,
# !! COMMITS BLOCK. The safety feature has become a write outage.
# Ctrl-C, then recover:
psql -p 5432 -U postgres -c "
ALTER SYSTEM SET synchronous_standby_names = '';
SELECT pg_reload_conf();"
# THE LESSON: never run a single synchronous standby.
# synchronous_standby_names = 'ANY 1 (standby1, standby2)'
docker start pg_standby-- ---------- EXPERIMENT 4: query conflicts on the standby ----------
-- On the STANDBY, start a long-running query:
SELECT pg_sleep(60), count(*) FROM events;
-- On the PRIMARY, meanwhile:
DELETE FROM events WHERE id < 5000;
VACUUM events;
-- The standby query may be cancelled:
-- ERROR: canceling statement due to conflict with recovery
-- DETAIL: User query might have needed to see row versions
-- that must be removed.
-- Two ways to handle it, on the standby:
-- max_standby_streaming_delay = 60s -- let replay wait
-- hot_standby_feedback = on -- tell the primary what is still needed
-- !! hot_standby_feedback causes BLOAT ON THE PRIMARY by holding back VACUUM.
-- !! It trades primary health for standby query stability. Choose deliberately.# ---------- EXPERIMENT 5: failover ----------
# 1. FENCE the primary first. Two writable servers = split brain =
# divergent data that is extremely painful to reconcile.
docker stop pg_primary
# 2. Promote the standby:
docker exec pg_standby su postgres -c \
"pg_ctl promote -D /var/lib/postgresql/data"
# or: psql -p 5433 -U postgres -c "SELECT pg_promote();"
# 3. Confirm:
psql -p 5433 -U postgres -c "SELECT pg_is_in_recovery();" # f = writable
# 4. It accepts writes now:
psql -p 5433 -U postgres -c "INSERT INTO events (payload) VALUES ('new primary');"
# 5. Check the timeline changed:
psql -p 5433 -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint();"
# Was 1, now 2.# ---------- EXPERIMENT 6: why the old primary cannot just come back ----------
docker start pg_primary
# It starts as a PRIMARY on timeline 1, while the new primary is on
# timeline 2. Both believe they are authoritative. If applications could
# reach both, you would have split brain.
# The old primary must be REBUILT as a standby of the new primary.
# Either clone it again with pg_basebackup, or use pg_rewind, which
# replays only the diverged changes and is much faster on large databases:
docker stop pg_primary
docker exec pg_primary su postgres -c \
"pg_rewind --target-pgdata=/var/lib/postgresql/data \
--source-server='host=pg_standby port=5432 user=postgres' -P"
# Then add standby.signal + primary_conninfo pointing at the NEW primary.
# 6. Take a NEW BASE BACKUP of the new primary — the old one belongs to
# the old timeline and is no longer a valid recovery chain.How it works #
Expected result: working replication, measured lag, and direct experience of the four failure modes that matter — a lagging standby, an abandoned slot retaining WAL, synchronous replication blocking commits, and a promoted standby whose old primary cannot rejoin.
Each experiment demonstrates something specific:
Experiment 2 shows how replication slots fill production disks. The slot does exactly what it promises — retains WAL until the consumer reads it — and that guarantee becomes a hazard the moment the consumer stops existing. Seeing wal_retained grow makes the abstract warning concrete, and max_slot_wal_keep_size the obvious mitigation.
Experiment 3 is the most important one to have done. A single synchronous standby looks like the safest possible configuration and is in fact a write outage waiting for that standby to reboot. The primary has nobody to wait for, so every commit hangs. ANY 1 (standby1, standby2) means "any one of these confirms", which restores the safety without the single point of failure. Very few people expect this before they see it.
Experiment 4 shows the trade-off in serving reads from a standby. WAL replay needs to remove rows a long query is still reading, so PostgreSQL either delays replay or cancels the query. hot_standby_feedback resolves the conflict by holding back VACUUM on the primary — which means standby query stability is purchased with primary bloat. There is no free option, only a deliberate choice.
Experiments 5 and 6 cover failover properly. Fencing comes first because two writable servers produce divergent data that may be impossible to reconcile. Promotion starts a new timeline, which is exactly what stops the two histories being confused — and is also why the old primary cannot simply restart. pg_rewind exists for this: it replays only the diverged portion instead of copying the whole database.
The final step — a new base backup after promotion — closes the loop back to the PITR lesson. Until it exists, the new primary has no valid recovery chain at all.
Real-world use #
The gap between reading about replication and having run a failover is large, and it shows up under pressure. Everything in this lab takes minutes to reproduce and hours to debug for the first time in production.
The synchronous experiment is the one to insist on. Configurations with a single synchronous standby exist in real systems, set up by people who reasonably believed they were choosing maximum safety, and they work perfectly until the standby is rebooted for patching.
Monitor replication lag and alert on it. A standby hours behind is not the failover option anyone believes it to be, and the drift is gradual and silent.
For production, use Patroni or repmgr rather than manual promotion. Correctly deciding that a primary is genuinely dead rather than briefly unreachable is a hard distributed-systems problem, and the cost of getting it wrong is split brain. Manual failover is appropriate when a human is available to make that judgement; automation needs consensus machinery to make it safely.
Know pg_rewind before you need it. Rebuilding a failed primary by full clone is straightforward and, on a large database, slow enough to matter. pg_rewind turns hours into minutes by replaying only what diverged.
And keep repeating that a standby is not a backup. This lab makes the point vividly: run DROP TABLE on the primary and watch it vanish from the standby within a second.
Common mistakes #
- Configuring a single synchronous standby, which blocks every commit when that standby is unavailable.
- Leaving an abandoned replication slot, retaining WAL until the primary’s disk fills.
- Promoting a standby without fencing the old primary first, causing split brain.
- Enabling hot_standby_feedback without realising it bloats the primary by holding back VACUUM.
- Expecting the old primary to rejoin by restarting — it has diverged and needs a rebuild or pg_rewind.
Practice #
Run all six experiments and write down what you observed for each. Then extend the lab: add a second standby and configure ANY 1 (standby1, standby2), then stop one and confirm commits continue. Measure how long a full failover takes end to end, including repointing a client. Use pg_rewind to rebuild the old primary and time it against a full pg_basebackup. Finally, run DROP TABLE events on the primary and time how long it takes to disappear from the standby — then state in one sentence why replication is not a backup.