Lab 23: Abandon a replication slot and watch it hold WAL until the server stops it

Objective

Lab 19 showed WAL accumulating because archiving failed. This lab shows the other cause, which is more common and less obvious: a replication slot whose consumer has gone away.

A slot is a promise. It tells the primary “keep every WAL segment from this position onwards, because I will be back for it.” The primary keeps that promise absolutely — through restarts, for weeks, until the filesystem fills — because it has no way to know whether the standby is coming back.

By the end of this lab you will have abandoned a slot, measured what it retained, bounded it with max_slot_wal_keep_size, watched the server invalidate it, and read the message the standby gets when it tries to return. That message is the point: bounding the retention protects the primary and destroys the standby, and knowing which you would rather lose is the decision the setting forces you to make in advance.

Architecture

flowchart TD
    P["primary rbpg-sb"] --> S1["slot: oldprimary\nactive, consumer streaming"]
    P --> S2["slot: lab21_slot\nconsumer STOPPED"]
    S2 --> R["restart_lsn frozen\nWAL from here retained"]
    R --> G["pg_wal grows past max_wal_size"]
    K["max_slot_wal_keep_size = 256MB"] --> I["wal_status: reserved\n-> extended -> unreserved -> lost"]
    I --> D["slot invalidated\ninvalidation_reason = wal_removed"]
    D --> F["standby cannot reconnect\nmust be rebuilt"]

Requirements

• A primary with a slot whose consumer you can stop — the pair from Lab 21.
• pgbench and roughly 1 GB of free disk on the primary.

Scenario

A standby was decommissioned three weeks ago. The VM was deleted. Nobody dropped its slot, because nobody knew there was one.

The primary’s disk is now at 94%, the database is completely healthy, and the table sizes have not changed.

Tasks

Task 1 — Stop the consumer, keep the slot

LAB="$HOME/rbpg-lab-23"
mkdir -p "$LAB"

docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb stop -m fast
sleep 3

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT slot_name, active, active_pid, restart_lsn, wal_status,
         pg_size_pretty(safe_wal_size) AS safe_wal_size
  FROM pg_replication_slots ORDER BY slot_name;" | tee "$LAB/abandoned.txt"
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT count(*) AS connected_standbys FROM pg_stat_replication;"

$ stop the standby, then list the slots and count connected standbys

 slot_name  | active | active_pid | restart_lsn | wal_status | safe_wal_size 
------------+--------+------------+-------------+------------+---------------
lab21_slot | f      |            | 0/60A5C330  | reserved   | 
oldprimary | t      |        689 | 0/60A5C330  | reserved   | 
(2 rows)

connected_standbys 
--------------------
                1
(1 row)

active = f and active_pid is empty — nothing is consuming this slot. But restart_lsn is still 0/60A5C330, and wal_status = reserved means the primary is honouring it.

Slots are persistent objects. They survive the consumer disconnecting, they survive a primary restart, and they are only removed by an explicit pg_drop_replication_slot().

Task 2 — Write, and measure what is held

for i in 1 2 3 4 5 6; do
  docker exec -u postgres rbpg-sb pgbench -c 4 -j 2 -T 6 -M prepared lab21
done
docker exec -u postgres rbpg-sb psql -X -c "CHECKPOINT;"
sleep 2

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT slot_name, active, wal_status,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained,
         pg_size_pretty(safe_wal_size) AS safe_wal_size
  FROM pg_replication_slots ORDER BY slot_name;" | tee "$LAB/growth.txt"
docker exec rbpg-sb bash -c 'du -sh $PGDATA/pg_wal'

$ run pgbench, checkpoint, then compare the two slots

 slot_name  | active | wal_status | wal_retained | safe_wal_size 
------------+--------+------------+--------------+---------------
lab21_slot | f      | reserved   | 189 MB       | 
oldprimary | t      | reserved   | 0 bytes      | 
(2 rows)

pg_wal: 481M    max_wal_size: 1GB

The contrast is the diagnosis. Both slots exist; one retains 189 MB and one retains nothing, and the difference is entirely whether a consumer is keeping up.

restart_lsn on the abandoned slot has not moved since the standby stopped. It never will. Every segment from that position forward stays on disk, and a checkpoint cannot remove any of them.

Task 3 — Bound it

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT name, setting, unit, boot_val, context FROM pg_settings
  WHERE name IN ('max_slot_wal_keep_size','wal_keep_size');"

docker exec -u postgres rbpg-sb psql -X -c "ALTER SYSTEM SET max_slot_wal_keep_size = '256MB';"
docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_reload_conf();"
sleep 1

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT slot_name, wal_status,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
         pg_size_pretty(safe_wal_size) AS safe_wal_size
  FROM pg_replication_slots ORDER BY slot_name;"

$ read the defaults, set max_slot_wal_keep_size to 256MB, reload, re-check

          name          | setting | unit | boot_val | context 
------------------------+---------+------+----------+---------
max_slot_wal_keep_size | -1      | MB   | -1       | sighup
wal_keep_size          | 0       | MB   | 0        | sighup
(2 rows)

slot_name  | wal_status | retained | safe_wal_size 
------------+------------+----------+---------------
lab21_slot | reserved   | 189 MB   | 72 MB
oldprimary | reserved   | 0 bytes  | 264 MB
(2 rows)

max_slot_wal_keep_size defaults to -1: unlimited. That default is why this failure mode exists at all, and changing it is the single most useful configuration decision in this lab.

safe_wal_size now has a value. It is how much more WAL can be written before this slot exceeds the limit — 72 MB of headroom for the abandoned slot, 264 MB for the healthy one. It is the number to alert on, and it is only populated when a limit is set.

Task 4 — Watch it be invalidated

for r in 1 2 3; do
  docker exec -u postgres rbpg-sb pgbench -c 4 -j 2 -T 8 -M prepared lab21
  docker exec -u postgres rbpg-sb psql -X -c "CHECKPOINT;"
  docker exec -u postgres rbpg-sb psql -X -c "
    SELECT slot_name, wal_status,
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
           pg_size_pretty(safe_wal_size) AS safe_wal_size
    FROM pg_replication_slots WHERE slot_name='lab21_slot';"
done | tee -a "$LAB/growth.txt"

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT slot_name, active, restart_lsn, wal_status, invalidation_reason
  FROM pg_replication_slots ORDER BY slot_name;" | tee "$LAB/invalidated.txt"

$ write past the limit, then read the slot state

 lab21_slot | lost       |          | 
lab21_slot | lost       |          | 
lab21_slot | lost       |          | 

slot_name  | active | restart_lsn | wal_status | invalidation_reason 
------------+--------+-------------+------------+---------------------
lab21_slot | f      |             | lost       | wal_removed
oldprimary | t      |     0/7BCA38C8 | reserved |                     
(2 rows)

The slot went straight from reserved to lost. The intermediate states exist but the write burst crossed the whole 256 MB budget within one round, so they were not sampled.

docker exec rbpg-sb grep -A2 "invalidating obsolete replication slot" /tmp/sb.log \
  | tee -a "$LAB/invalidated.txt"

$ grep the primary's log for the invalidation

2026-08-28 06:21:30.890 UTC [679] LOG:  invalidating obsolete replication slot "lab21_slot"
2026-08-28 06:21:30.890 UTC [679] DETAIL:  The slot's restart_lsn 0/60A5C330 exceeds the limit by 5913808 bytes.
2026-08-28 06:21:30.890 UTC [679] HINT:  You might need to increase "max_slot_wal_keep_size".

Task 5 — What the standby finds when it returns

docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb -l /tmp/sb.log start
sleep 6
docker exec rbpg-lab21 grep -iE "FATAL|slot" /tmp/sb.log | tail -4 | tee -a "$LAB/invalidated.txt"

$ start the standby against the invalidated slot and read its log

waiting for server to start.... done
server started

2026-08-28 06:22:04.022 UTC [250] FATAL:  could not start WAL streaming: ERROR:  can no longer access replication slot "lab21_slot"
DETAIL:  This replication slot has been invalidated due to "wal_removed".
2026-08-28 06:22:09.027 UTC [251] FATAL:  could not start WAL streaming: ERROR:  can no longer access replication slot "lab21_slot"
DETAIL:  This replication slot has been invalidated due to "wal_removed".

The standby started. pg_ctl said server started. It accepts read-only connections and serves stale data quite happily.

And every five seconds it fails to stream, forever. There is no recovering this standby: the WAL it needs to catch up has been deleted. It must be rebuilt from a new base backup.

Task 6 — The monitoring query

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT slot_name, slot_type, active, wal_status, invalidation_reason,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
         pg_size_pretty(safe_wal_size) AS headroom,
         CASE
           WHEN wal_status = 'lost' THEN 'ALERT: slot invalidated, standby must be rebuilt'
           WHEN NOT active THEN 'ALERT: slot has no consumer'
           WHEN wal_status IN ('unreserved','extended') THEN 'WARN: slot near its limit'
           ELSE 'ok'
         END AS verdict
  FROM pg_replication_slots ORDER BY slot_name;" | tee "$LAB/monitoring.txt"

$ the slot health query

 slot_name  | slot_type | active | wal_status | invalidation_reason | retained | headroom |                     verdict                      
------------+-----------+--------+------------+---------------------+----------+----------+--------------------------------------------------
lab21_slot | physical  | f      | lost       | wal_removed         |          |          | ALERT: slot invalidated, standby must be rebuilt
oldprimary | physical  | t      | reserved   |                     | 0 bytes  | 259 MB   | ok
(2 rows)

NOT active is the alert that catches this early. An inactive slot is a slot with no consumer, and it deserves a page long before it has retained anything, because the answer is always one of two things: somebody will bring the consumer back today, or the slot should be dropped.

Task 7 — Recovery

docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb stop -m immediate
docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_drop_replication_slot('lab21_slot');"
docker exec -u postgres rbpg-sb psql -X -c "ALTER SYSTEM RESET max_slot_wal_keep_size;"
docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-sb psql -X -c "CHECKPOINT;"
sleep 2
docker exec rbpg-sb bash -c 'du -sh $PGDATA/pg_wal'
docker exec -u postgres rbpg-sb psql -X -c "SELECT slot_name, active, wal_status FROM pg_replication_slots;"

$ stop the standby, drop the slot, reset the limit, checkpoint

 pg_drop_replication_slot 
--------------------------

(1 row)

pg_wal after dropping the slot and checkpointing: 481M

slot_name  | active | wal_status 
------------+--------+------------
oldprimary | t      | reserved
(1 row)

pg_wal is still 481 MB immediately afterwards, for the reason Lab 19 gave: segments are recycled toward the target over several checkpoints rather than deleted at once. The slot is gone, which is what unblocks it.

Rebuilding the standby is Lab 21’s procedure from the start: create a new slot, pg_basebackup -R -S, start. There is no shortcut, and there is no partial repair.

Validation

test -s "$LAB/abandoned.txt"   && echo "OK abandoned"
test -s "$LAB/growth.txt"      && echo "OK growth"
test -s "$LAB/invalidated.txt" && echo "OK invalidated"
test -s "$LAB/monitoring.txt"  && echo "OK monitoring"

grep -q "wal_removed"  "$LAB/invalidated.txt" && echo "OK invalidation reason captured"
grep -q "can no longer access" "$LAB/invalidated.txt" && echo "OK standby failure captured"

# The check to keep: any slot without a consumer.
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT slot_name, active, wal_status FROM pg_replication_slots WHERE NOT active OR wal_status <> 'reserved';"

Questions to answer without looking anything up:

1. The disk is filling, table sizes are unchanged, and archiving reports no failures. What are you looking for?
2. max_slot_wal_keep_size is -1. What has the server been told to prioritise?
3. A slot shows wal_status = 'unreserved'. Can the standby still catch up?
4. A slot is lost. What is the repair?
5. Which single column would you alert on to catch this three weeks earlier than a disk alert?

Expected Outcome

You have abandoned a slot, measured the WAL it held, bounded it, watched the invalidation from both sides, and seen that the standby starts cheerfully and can never stream again.

The two things to put in place:

-- Alert on this. NOT active is the early signal.
SELECT slot_name, active, wal_status, invalidation_reason,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes,
       safe_wal_size
FROM pg_replication_slots
WHERE NOT active OR wal_status <> 'reserved';

# Decide this deliberately rather than inheriting -1.
max_slot_wal_keep_size = '<peak WAL rate x tolerable standby outage>'

And the operational habit: decommissioning a standby includes dropping its slot. Put it in the runbook next to deleting the VM, because the VM’s absence is what makes the slot invisible.

Troubleshooting

The slot does not retain WAL. A physical slot only holds from restart_lsn, which advances as the consumer confirms. If the consumer never connected, the slot has no restart_lsn yet and holds nothing — connect the standby once, then stop it.

pg_wal is not growing. Not enough WAL is being generated to cross a segment boundary. Generate more, or drop max_wal_size for the demonstration.

max_slot_wal_keep_size appears to do nothing. It is sighup context, so it needs a reload, and it bounds WAL retained for slots only. Segments held for a failing archive are retained regardless — a different mechanism with the same symptom.

wal_status never leaves reserved. The bound has not been exceeded. wal_status moves reserved → extended → unreserved → lost as the retained volume approaches and passes max_slot_wal_keep_size.

The standby starts fine after the slot was invalidated. It does, and then it cannot stream — the segments it needs are gone. This is Task 5’s point: a cheerful start is not evidence of a working standby. Check pg_stat_wal_receiver and the standby log for requested WAL segment ... has already been removed.

Dropping the slot did not free the space immediately. The segments are removed at the next checkpoint. Run CHECKPOINT; and measure again.

Cleanup

Already done in Task 7. Confirm nothing is left:

docker exec -u postgres rbpg-sb psql -X -c "SELECT slot_name, active FROM pg_replication_slots;"
docker exec -u postgres rbpg-sb psql -X -c "SHOW max_slot_wal_keep_size;"
docker rm -f rbpg-lab21

Production notes

• Alert on active = false on any slot. That is the early signal, hours or days before the volume fills, and it is a boolean rather than a threshold anyone has to tune.
• Set max_slot_wal_keep_size deliberately. Its default of -1 means unbounded, which trades the availability of the whole primary for the convenience of one standby that may never return.
• Compute the value from the peak WAL rate and the longest standby outage you are willing to survive without a rebuild. Then check that the pg_wal volume can hold it.
• Invalidation is a choice the server makes on your behalf: it protects the primary and sacrifices the standby, which then needs a fresh base backup. That is usually the right trade, and it should not be a surprise.
• Every logical slot belongs to a consumer somebody owns. A slot with no owner is a disk-full incident waiting for enough write volume.

What You Learned

• An inactive slot retains WAL indefinitely by default, and will fill the volume.
• max_slot_wal_keep_size bounds it, and its default of -1 is unbounded.
• wal_status moves through reserved, extended, unreserved and lost, which is a usable early warning.
• Invalidation protects the primary and sacrifices the standby. The standby then needs a new base backup.
• A standby whose slot was invalidated starts normally and simply cannot stream — so “the standby is up” proves nothing.
• active = false is the alert, because it fires long before any size threshold would.