Monitoring Replication Lag
Create lag on demand, watch write_lag and replay_lag diverge, then use a deliberate delay as a real disaster-recovery safeguard
pg_stat_replication reports lag as three separate numbers, not one, because a change genuinely passes through three distinct stages on its way to being usable on a standby: written to the OS, flushed durably to disk, and replayed into the actual data the standby will answer queries against. Under normal, healthy streaming, all three stay within milliseconds of each other. The moment something stops the standby from replaying — a pause, an expensive query holding it back, or a deliberate policy — write_lag and flush_lag stay small while replay_lag climbs on its own, and that divergence is itself the diagnosis.\n\nThis lab creates that divergence on purpose with pg_wal_replay_pause(), watches it recover on resume, and then builds a standing, deliberate version of the same thing with recovery_min_apply_delay — a real disaster-recovery technique, not just a test.
Creating Lag on Demand
-- On the standby:
SELECT pg_wal_replay_pause();
SELECT pg_is_wal_replay_paused();
-- ... write on the primary ...
SELECT pg_wal_replay_resume();
Pausing replay does not stop the standby from receiving and flushing WAL — only from applying it to the actual data. That is exactly why write_lag and flush_lag stay small while paused, and replay_lag alone grows.
Three Numbers, Three Meanings
| Column | What it measures |
|--------|-------------------|
| write_lag | Time from commit on the primary to the standby writing that WAL to its OS |
| flush_lag | Time until the standby has durably flushed it to disk |
| replay_lag | Time until the standby has actually applied it — the number that determines what a query against the standby will see |
A Deliberate, Standing Delay
# In the standby's postgresql.auto.conf:
recovery_min_apply_delay = '5s'
Unlike an ad-hoc pause, this is a permanent policy: this standby will always replay five seconds behind the primary. That five-second window is a live undo buffer — a mistaken DROP TABLE or bad bulk UPDATE on the primary has not been applied here yet, giving a brief, genuine window to intervene before the damage propagates.
Watching From the Standby's Own Side
SELECT status, written_lsn, flushed_lsn, latest_end_lsn FROM pg_stat_wal_receiver;
pg_stat_replication only exists on the primary; pg_stat_wal_receiver is its counterpart on the standby, reporting the exact same kind of information from the receiving end.
pg_wal_replay_pause() / pg_wal_replay_resume()
Standby-side functions that stop and restart WAL replay on demand, without touching the streaming connection itself — WAL keeps arriving and getting flushed to disk the whole time, it simply stops being applied. This makes them a safe, reversible way to create replay lag deliberately for testing, or to freeze a standby's visible data at a known point without disconnecting it.
recovery_min_apply_delay
A standby-side setting that deliberately delays WAL replay by a fixed duration, every time, as a standing policy rather than a one-off test. It trades read freshness on that standby for a genuine recovery window — enough time for a human to notice and react to a destructive mistake on the primary before it reaches this standby's actual data.
⏸️ Pause Replay on the Standby
Pause WAL replay on the already-running standby and confirm it took effect.
psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_wal_replay_pause();"psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_is_wal_replay_paused();"student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_wal_replay_pause();" SET pg_wal_replay_pause ---------------------- (1 row) student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_is_wal_replay_paused();" SET pg_is_wal_replay_paused -------------------------- t (1 row)
✍️ Write on the Primary While Paused
Insert 5,000 rows on the primary, then check how write_lag, flush_lag, and replay_lag each responded.
psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS lag2_demo (id serial PRIMARY KEY, note text);" -c "INSERT INTO lag2_demo (note) SELECT 'row-'||g FROM generate_series(1,5000) g;"psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS lag2_demo (id serial PRIMARY KEY, note text);" -c "INSERT INTO lag2_demo (note) SELECT 'row-'||g FROM generate_series(1,5000) g;" SET CREATE TABLE INSERT 0 5000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;" SET client_addr | state | sent_lsn | replay_lsn | write_lag | flush_lag | replay_lag -------------+-----------+-----------+------------+-----------------+-----------------+----------------- 127.0.0.1 | streaming | 0/92A44A4 | 0/9000058 | 00:00:00.006044 | 00:00:00.006068 | 00:00:02.842653 (1 row)
▶️ Resume and Watch It Recover
Resume replay and confirm the standby catches all the way back up.
psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_wal_replay_resume();"psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_wal_replay_resume();" SET pg_wal_replay_resume ----------------------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;" SET client_addr | state | sent_lsn | replay_lsn | write_lag | flush_lag | replay_lag -------------+-----------+-----------+------------+-----------------+----------------+---------------- 127.0.0.1 | streaming | 0/92F2698 | 0/92F2698 | 00:00:00.003666 | 00:00:00.00606 | 00:00:00.00606 (1 row)
🛡️ Set a Deliberate, Standing Delay
Configure recovery_min_apply_delay so this standby always replays five seconds behind the primary, as a permanent policy rather than a one-off test.
as-postgres sh -c "echo \"recovery_min_apply_delay = '5s'\" >> /tmp/standby_data/postgresql.auto.conf"as-postgres pg_ctl restart -D /tmp/standby_data -wstudent@lab:~$ as-postgres sh -c "echo \"recovery_min_apply_delay = '5s'\" >> /tmp/standby_data/postgresql.auto.conf" student@lab:~$ as-postgres pg_ctl restart -D /tmp/standby_data -w waiting for server to shut down.... done server stopped waiting for server to start.... done server started
🕳️ Confirm the Undo Window Is Real
Write on the primary, and confirm the change genuinely is not visible on the standby yet.
psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS lag3_demo (id serial PRIMARY KEY, note text);" -c "INSERT INTO lag3_demo (note) VALUES ('should be delayed');"psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM lag3_demo;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS lag3_demo (id serial PRIMARY KEY, note text);" -c "INSERT INTO lag3_demo (note) VALUES ('should be delayed');" SET CREATE TABLE INSERT 0 1 student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM lag3_demo;" SET ERROR: relation "lag3_demo" does not exist LINE 1: SELECT count(*) FROM lag3_demo; ^
📡 Monitor From the Standby's Own Side
Query pg_stat_wal_receiver on the standby — the counterpart to pg_stat_replication, from the receiving end.
psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT status, written_lsn, flushed_lsn, latest_end_lsn FROM pg_stat_wal_receiver;"student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT status, written_lsn, flushed_lsn, latest_end_lsn FROM pg_stat_wal_receiver;" SET status | written_lsn | flushed_lsn | latest_end_lsn -----------+-------------+-------------+----------------- streaming | 0/9385A80 | 0/9385A80 | 0/9385A80 (1 row)
Lab 2.5.4 complete. You can now create, measure, and deliberately use replication lag:\n\n\n pg_wal_replay_pause/resume : ✅ lag created and recovered on demand\n write/flush/replay_lag : ✅ told apart on real, diverging numbers\n recovery_min_apply_delay : ✅ configured as a standing DR safeguard\n pg_stat_wal_receiver : ✅ monitored from the standby's own side\n
Enable JavaScript to run the live terminal and track your progress.