Measure a Lagging Standby and Release Held WAL

Goal

Measure the difference between a standby being "attached" and "keeping up". Measure the difference between receiving and applying in bytes, actually trigger conflicts between standby queries and apply, the cost of feedback, and the WAL an abandoned slot holds, and then fix three standbys whose connections are fine but whose data is stale.

Why it matters

Replication monitoring usually looks only at the connection state. So even when a standby is hours behind it shows green, and the person pulling reports notices first. Conversely, a single slot of a deleted standby can hold WAL for weeks and fill the primary's disk. Both are visible immediately if you measure "where it stopped" by position (LSN). And if you experience first-hand that every knob for reducing lag pushes the cost somewhere else — canceling queries, the standby falling behind, or the primary's cleanup being delayed — you will flounder less when choosing settings.

Environment

This Pod runs as the postgres account (just type commands, without su or sudo). The primary is running on 5432.

psql                       주 서버(5432, labdb)에 바로 붙습니다
psql -h 127.0.0.1 -p 5433  이 실습에서 세울 대기 서버

(The first line connects straight to the primary on 5432, database labdb; the second is the standby you build in this lab.)

Be sure to add -h 127.0.0.1 for the standby (the Unix socket directory has been moved). Put this lab's files in /root/db/lag/.

Steps

1. Standby lag1 on 5433 with slot lag1
2. Stop only apply and measure the backlog in bytes → /root/db/lag/02-paused.txt
3. Resume and confirm it caught up → /root/db/lag/03-caughtup.txt
4. A conflict between a standby query and apply → /root/db/lag/04-conflict.txt
5. Turning on feedback delays the primary's cleanup → /root/db/lag/05-feedback.txt
6. A slot nobody uses holds WAL → /root/db/lag/06-ghost.txt
7. Set a limit to cut off the abandoned slot → /root/db/lag/07-slots.txt
8. Incident: the connections are fine, but the data on three reporting standbys is stale

Notes

• The standby log is /var/log/labhub/lag1.log, and the primary log is /var/log/postgresql.log.
• It is easy to confuse functions that work only on the standby (pg_wal_replay_pause and so on) with views visible only on the primary (pg_stat_replication).
• Incident tool: incident list, incident show <name>. Starting the same incident again returns it to the initial broken state. In the report /root/incident/<name>/report.txt, write for each target one <target>: <code> line and evidence-<target>: <observed line>.
• The lab Pod has no volume, so when the session ends, all files and servers disappear.

Attach standby lag1 with a slot

Create a physical slot lag1 on the primary (5432), take a base backup into /var/lib/postgresql/lag1, and start it as a standby on 5433 (log /var/log/labhub/lag1.log). The standby must follow the primary using slot lag1.

Direction: exactly what you did in the first lab — create the slot first, take the copy with that slot, and start the copy as a standby. Tools: pg_create_physical_replication_slot, pg_basebackup's -X stream, -S, and -R, port in postgresql.auto.conf, pg_ctl start, and the standby's pg_stat_wal_receiver. Specifics: if you pass true as the second argument when creating the slot, it holds WAL from the moment it is created (so the needed WAL is not deleted before the standby first connects). The role is lab (this lab server allows loopback replication). Add -h 127.0.0.1 -p 5433 when connecting to the standby.

Stop only apply, and measure the backlog in bytes

Stop only apply (replay) on the standby (5433). Then create a table ticks on the primary and insert 1000 or more rows. Confirm that ticks is not yet visible on the standby, and write the position the standby has received, the position it has applied, and their difference to /root/db/lag/02-paused.txt as three lines: receive_lsn: <lsn>, replay_lsn: <lsn>, gap_bytes: <bytes>. Leave the standby paused (you resume it in the next step).

Direction: inside the standby, receiving and applying run separately. If you stop only apply, receiving continues, so a gap opens between them. Tools: pg_wal_replay_pause(), pg_get_wal_replay_pause_state(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_wal_lsn_diff(). Specifics: capture the two positions together in a single select — if you measure them separately, the receive position moves in between and the difference does not add up. Pausing is a function you call on the standby; calling it on the primary gives an error.

Resume apply and confirm it caught up

Resume apply on the standby, and wait until the received position and the applied position become equal (until it catches up). Then write two lines to /root/db/lag/03-caughtup.txt: replay_lsn: <lsn> (the apply position after catching up) and ticks_rows: <rows> (the number of ticks rows counted on the standby).

Direction: the WAL received while paused is on disk, so when you resume it does not receive it again; it just applies it. Tools: pg_wal_replay_resume(), comparing pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn(), count(*). Specifics: right after resuming it may still be applying — write the values only after you see the two positions become equal. The grader counts ticks on both sides again.

A standby query collides with apply and is canceled

Change the standby's max_standby_streaming_delay to 5s (without a restart). Create a 100,000-row table big on the primary; on the standby, start a query in a repeatable read transaction that counts big and then stays for about 30 seconds; and while it runs, delete half of big on the primary and VACUUM. Save the output the standby query received (including errors) to /root/db/lag/04-conflict.txt.

Direction: the primary's VACUUM removes rows that "nobody looks at anymore", but the standby's query is looking at those rows somewhere the primary does not know about. To apply that cleanup record, the standby must either wait for the query to finish or cancel it. Tools: alter system set and pg_reload_conf() on the standby, psql's -c several times (to continue a transaction), background execution (&), the standby's pg_stat_database_conflicts. Specifics: start the standby query in the background and capture its output to a file with 2>&1. If you delete and vacuum on the primary a few seconds later, the query is canceled once the 5-second limit passes, and the conflict statistic goes up by one. The grader looks at that statistic.

Turn on feedback and the cost goes to the primary

Change the standby's hot_standby_feedback to on (without a restart). As in 04, while a long query reading big is running on the standby, delete more rows of big on the primary, run VACUUM (VERBOSE) big, and save its output to /root/db/lag/05-feedback.txt.

Direction: feedback is the mechanism by which the standby tells the primary "my query is still looking at rows as of this point". The query survives, but the side that cannot remove those rows is the primary. Tools: alter system set and pg_reload_conf() on the standby, the tuples line of VACUUM (VERBOSE), and xmin in the primary's pg_replication_slots. Specifics: VERBOSE output goes to standard error as INFO — capture it with 2>&1. If N in "N are dead but not yet removable" is greater than 0, feedback blocked the cleanup.

A slot nobody uses holds WAL

Create a physical slot ghost on the primary that nobody will use (holding WAL from the moment it is created). Do a large write into table bulk so that the WAL held by ghost piles up to more than 100MB (104857600 bytes), and CHECKPOINT. Write three lines to /root/db/lag/06-ghost.txt: ghost_restart_lsn: <lsn>, current_lsn: <lsn> (the primary's current position), and retained_bytes: <bytes> (the difference between the two positions).

Direction: a slot's restart_lsn is a promise: "do not delete from here on". If no standby uses it, that position never moves. Tools: pg_create_physical_replication_slot(name, true), insert … select generate_series, CHECKPOINT, restart_lsn and wal_status of pg_replication_slots, pg_current_wal_lsn(), pg_wal_lsn_diff(), pg_ls_waldir(). Specifics: a few hundred thousand 200-byte rows push WAL past 100MB. Confirm with the sum from pg_ls_waldir() that the pg_wal directory does not shrink even after a checkpoint. Capture the two positions at once.

Set a limit to cut off the abandoned slot

Set the primary's max_slot_wal_keep_size to 64MB (without a restart). Confirm with a CHECKPOINT that ghost gets cut off (wal_status becomes lost), save the pg_replication_slots output at that point to /root/db/lag/07-slots.txt, and then drop ghost. The live standby's slot lag1 must keep following as before.

Direction: the limit is "the maximum WAL a single slot may hold". It protects the primary's disk by cutting off slots that exceed it, and a cut-off standby has to be rebuilt. Tools: alter system set and pg_reload_conf(), CHECKPOINT, wal_status and safe_wal_size of pg_replication_slots, the primary log /var/log/postgresql.log, pg_drop_replication_slot. Specifics: the limit check runs at a checkpoint, not at the moment you change the setting. When a slot is cut off, the primary log keeps a line containing that slot's name. Save the output confirming lost before you drop it.

Incident — the connections are fine, but the data on three reporting standbys is stale

Running incident rp-stale-standby starts the incident (read the ticket first). Symptom: the three reporting standbys rpt1, rpt2, and rpt3 (5451, 5452, 5453) following the primary rp-src (127.0.0.1:5450) are all attached as streaming, yet queries do not show what was just written on the primary. Make all three follow the primary in real time, and correct the settings that caused it so the same thing does not happen again. The standbys must remain standbys, and restart only when fixing a setting that changes only on restart (each standby has dashboard sessions attached). In /root/incident/rp-stale-standby/report.txt, write for each standby one line rpt1: <code> and one line evidence-rpt1: <observed line> (the same form for rpt2 and rpt3). The code for each standby is one of the following: apply-delay (a setting that deliberately delays apply) · conflict-wait (it waits forever on conflicts with standby queries) · recovery-target (it stopped on reaching a recovery target) · replay-paused (someone stopped apply by hand).

Direction: if the connection is fine, receiving is working — the problem is apply. First split them into pairs by "is it in a paused state or not", and within each pair look at what differs. Tools: flush_lsn and replay_lsn in the primary's pg_stat_replication with pg_wal_lsn_diff, the standby's pg_get_wal_replay_pause_state(), apply- and recovery-related settings via show (or pg_settings), pg_stat_activity (the startup process's wait_event, backend_xmin of long-running queries), and the standby log (/var/log/labhub/rp-stale/.log). Specifics: of the two in a paused state, one ends recovery and promotes if you resume it — read the HINT in the log to the end to see what it tells you to do. Time numbers such as replay_lag show stale values when apply has stopped. Most apply settings change with a reload, but recovery_target_* changes only on restart.