Learning: PostgreSQL streaming replication for beginners - build a primary and a hot standby, watch lag, fail over and rebuild

#About this session

Note

This lab teaches the mechanics by hand. In production you would normally put a manager such as Patroni, repmgr or your cloud service in charge of failover, but you still need to understand what it does underneath.

#Module 1: the ideas you need first

PostgreSQL writes every change to the write-ahead log (WAL) before it changes data files. Each position in the WAL is an LSN (log sequence number), shown like 0/3000148.

Physical streaming replication sends WAL records from the primary to a standby over a normal connection, and the standby replays them. The standby is a byte-for-byte copy of the whole cluster.

   app writes                         app reads (optional)
       │                                     │
 ┌─────▼──────┐   WAL stream (TCP)    ┌──────▼─────┐
 │  primary   │ ────────────────────► │  standby   │
 │ read/write │   replication slot    │ read-only  │
 └────────────┘                       └────────────┘

#Module 2: start the primary

docker network create pglab

docker run -d --name pg-primary --network pglab -p 5432:5432 \
  -e POSTGRES_PASSWORD='Lab#Pass2026' postgres:17

docker exec -it pg-primary psql -U postgres -c "SELECT version();"

Check the settings replication needs. In recent versions the defaults are already right:

docker exec -it pg-primary psql -U postgres -c "SHOW wal_level;" -c "SHOW max_wal_senders;" -c "SHOW hot_standby;"

You want wal_level = replica (or logical), max_wal_senders above 0, and hot_standby = on.

#Module 3: replication user, pg_hba and a slot

docker exec -it pg-primary psql -U postgres

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'Repl#Pass2026';
SELECT pg_create_physical_replication_slot('standby1');
SELECT slot_name, slot_type, active FROM pg_replication_slots;
\q

Replication connections use the special database name replication in pg_hba.conf; all does not cover them. Add a rule and reload:

docker exec pg-primary bash -c \
  "echo 'host replication replicator samenet scram-sha-256' >> /var/lib/postgresql/data/pg_hba.conf"
docker exec -it pg-primary psql -U postgres -c "SELECT pg_reload_conf();"
docker exec -it pg-primary psql -U postgres -c \
  "SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE 'replication' = ANY(database);"

samenet matches any address on the container's own subnet. If replication is refused later with "no pg_hba.conf entry", see this troubleshooting guide.

Add some data:

docker exec -it pg-primary psql -U postgres -c "
CREATE TABLE orders (id serial PRIMARY KEY, customer text, amount numeric(10,2), created timestamptz DEFAULT now());
INSERT INTO orders (customer, amount) VALUES ('Asha',120.50),('Ben',75),('Chen',310.20);"

#Module 4: clone the standby with pg_basebackup

pg_basebackup copies the primary's data directory over a replication connection. With -R it also writes the settings that make the copy start as a standby.

docker volume create pg-standby-data

docker run --rm --network pglab -v pg-standby-data:/var/lib/postgresql/data \
  -e PGPASSWORD='Repl#Pass2026' postgres:17 bash -c "
    pg_basebackup -h pg-primary -U replicator -D /var/lib/postgresql/data \
      -X stream -S standby1 -R -P &&
    chown -R postgres:postgres /var/lib/postgresql/data &&
    chmod 700 /var/lib/postgresql/data"

Look at what -R wrote:

docker run --rm -v pg-standby-data:/d postgres:17 bash -c "ls /d/standby.signal; cat /d/postgresql.auto.conf"

Now start the standby from that volume:

docker run -d --name pg-standby --network pglab -p 5433:5432 \
  -v pg-standby-data:/var/lib/postgresql/data postgres:17

docker logs pg-standby 2>&1 | tail -5

The log should say it is entering standby mode and started streaming WAL from primary.

#Module 5: check that it replicates

On the primary:

-- docker exec -it pg-primary psql -U postgres
SELECT client_addr, state, sync_state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_behind, replay_lag
FROM   pg_stat_replication;

state = streaming and a small or zero bytes_behind means it works.

On the standby:

-- docker exec -it pg-standby psql -U postgres
SELECT pg_is_in_recovery();                                     -- t
SELECT count(*) FROM orders;                                    -- 3
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
SELECT status, sender_host FROM pg_stat_wal_receiver;
INSERT INTO orders (customer, amount) VALUES ('Dev', 1);
-- ERROR:  cannot execute INSERT in a read-only transaction

Insert on the primary and immediately count on the standby: the row appears within milliseconds.

#Module 6: create lag and watch it

Write a lot on the primary:

docker exec -it pg-primary psql -U postgres -c \
  "INSERT INTO orders (customer, amount) SELECT 'bulk', random()*100 FROM generate_series(1,2000000);"

While it runs, re-run the pg_stat_replication query on the primary a few times. bytes_behind and replay_lag rise, then fall to zero.

Now stop the standby and keep writing:

docker stop pg-standby
docker exec -it pg-primary psql -U postgres -c \
  "INSERT INTO orders (customer, amount) SELECT 'while-down', 1 FROM generate_series(1,500000);"
docker exec -it pg-primary psql -U postgres -c \
  "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots;"
docker start pg-standby

The slot is inactive and retains WAL for the standby, so it catches up when it returns. That is also the danger: a standby that never comes back makes the primary keep WAL until the disk fills. See pg_wal filling the disk, and consider max_slot_wal_keep_size as a safety limit.

#Module 7: the primary dies, promote the standby

docker stop pg-primary
docker exec -it pg-standby psql -U postgres -c "SELECT pg_promote();"
docker exec -it pg-standby psql -U postgres -c "SELECT pg_is_in_recovery();"   # f
docker exec -it pg-standby psql -U postgres -c "INSERT INTO orders (customer, amount) VALUES ('after-failover', 9) RETURNING id;"

The standby is now a read/write primary on a new timeline. Applications must be pointed at it (DNS, a virtual IP, a connection string with several hosts and target_session_attrs=read-write, or a proxy).

Warning

Never let the old primary start up and take writes again while the new one is live. Two primaries ("split brain") means two diverging copies of the data. Failover managers use fencing to prevent it.

#Module 8: rebuild the old primary as a standby

The old primary may have WAL the new primary never received, so it can't just follow the new one. The simple, always-correct method is to re-clone it.

docker rm pg-primary

# slot on the new primary for the rebuilt node
docker exec -it pg-standby psql -U postgres -c "SELECT pg_create_physical_replication_slot('standby2');"

docker volume create pg-node1-data
docker run --rm --network pglab -v pg-node1-data:/var/lib/postgresql/data \
  -e PGPASSWORD='Repl#Pass2026' postgres:17 bash -c "
    pg_basebackup -h pg-standby -U replicator -D /var/lib/postgresql/data -X stream -S standby2 -R -P &&
    chown -R postgres:postgres /var/lib/postgresql/data && chmod 700 /var/lib/postgresql/data"

docker run -d --name pg-node1 --network pglab -p 5434:5432 \
  -v pg-node1-data:/var/lib/postgresql/data postgres:17

The pg_hba.conf rule for replicator came across in the original clone, so the new primary already accepts it. Check pg_stat_replication on pg-standby; pg-node1 is now its standby. For large databases, pg_rewind can resynchronise an old primary much faster than a full copy (try it in the exercises).

#Module 9: clean up

docker rm -f pg-standby pg-node1 pg-primary 2>/dev/null
docker volume rm pg-standby-data pg-node1-data
docker network rm pglab

#Exercises

1. Make replication synchronous: on the primary set synchronous_standby_names = 'FIRST 1 (*)' with ALTER SYSTEM and reload. Check sync_state in pg_stat_replication. Stop the standby and try an INSERT. What happens, and why?
2. Rebuild the old primary with pg_rewind instead of pg_basebackup (it needs wal_log_hints = on or data checksums, set before the failover). Compare the time taken.
3. Run a long query on the standby while deleting many rows on the primary. Find the canceling statement due to conflict with recovery error and the settings (max_standby_streaming_delay, hot_standby_feedback) that affect it.
4. Use a connection string with two hosts, host=localhost,localhost port=5432,5433 target_session_attrs=read-write, and show that psql always lands on the current primary.
5. Drop the slot standby1 while the standby is stopped, write lots of WAL, then start the standby. What error does it log, and how do you recover it?

#Knowledge check

1. What does the standby receive from the primary?
2. Why does the replication rule in pg_hba.conf need replication in the database column?
3. What does pg_basebackup -R do?
4. What problem does a replication slot solve, and what new risk does it create?
5. Which view on the primary shows lag, and which columns?
6. Why can't the old primary simply start following the new primary after a failover?

#Summary

• Streaming replication ships WAL from primary to standby; the standby is read-only until promoted.
• Setup = replication role + pg_hba.conf rule + slot + pg_basebackup -X stream -S <slot> -R.
• Watch pg_stat_replication on the primary and pg_last_xact_replay_timestamp() on the standby.
• Slots protect standbys but can fill the disk; limit them with max_slot_wal_keep_size.
• Promotion is one call; preventing split brain and rebuilding the old primary are the hard parts.

#Further reading

• PostgreSQL documentation: High Availability, Load Balancing, and Replication (Log-Shipping Standby Servers), pg_basebackup, pg_rewind.
• On this site: replication lag on a standby, manual standby promotion when the primary is lost, cross-region DR with pgBackRest.