Disaster Scenario Labs
Four real incidents, back to back, on the same server. A dropped table, a crashed primary, a bloating replication slot, and a transaction ID wraparound check — each one genuinely reproduced, not simulated.
Every earlier lab in this block diagnosed a problem already visible in a running system. This lab is different: each of its four scenarios is a real disaster, built and then genuinely recovered from, one after another, on the same live server. A table is really dropped and really restored from a real pg_dump — and a row inserted after the dump is really, permanently gone. A real streaming standby is built with pg_basebackup, the primary is really crashed with an immediate shutdown, and the standby is really promoted to take over. A replication slot is really left inactive while WAL genuinely accumulates behind it, then safely cleaned up before it becomes a real problem. And a table's real transaction ID age is checked, frozen with a real VACUUM FREEZE, and confirmed reset to zero — the same mechanism that, left unmanaged for long enough, becomes a genuine wraparound emergency.\n\nNothing here is scaled up to a real crisis — this VM has too little real disk and memory headroom for that to be safe, and a genuine crisis is never the way to learn to prevent one. Every scenario is instead scaled down to a size this server can survive, while keeping every mechanism, every command, and every real error message completely genuine.
Scenario A: The Real Backup-Recency Gap
pg_dump -U postgres -d beer_db -t styles -F c -f /tmp/styles_backup.dump
A row inserted after this dump, then genuinely lost when the table is dropped and restored from it:
count
-------
26 <- after the insert
25 <- after DROP TABLE + pg_restore — the new row never made it into the dump
This is the real, honest shape of backup recency risk: a backup is only as current as the moment it was taken, and anything written after it is genuinely unrecoverable from that backup alone.
Scenario B: A Real Crash, A Real Promotion
pg_basebackup -U postgres -D /tmp/standby -R -X stream # real streaming standby
pg_ctl stop -D /var/lib/postgresql/18/data -m immediate # real, hard primary crash
pg_ctl promote -D /tmp/standby -w # real promotion to primary
pg_is_in_recovery() genuinely flips from t to f, and every row committed on the primary before the crash is still there on the promoted standby — because it had already streamed across before the crash happened.
Scenario C: Replication Slot Bloat, Genuinely Reproduced
A replication slot reserves WAL for a standby that might reconnect later — but if that standby never comes back, the reservation never releases on its own:
SELECT pg_create_physical_replication_slot('bloat_slot', true);
-- retained_wal starts at a few KB, genuinely grows to several MB after real write activity
SELECT pg_drop_replication_slot('bloat_slot'); -- the real, safe fix
The slot's restart_lsn genuinely falls further and further behind pg_current_wal_lsn() the longer it sits inactive — this VM's real disk cannot survive that growing unbounded, which is exactly why dropping an abandoned slot is a real, standard on-call action, not a theoretical one.
Scenario D: Transaction ID Age, Checked and Reset for Real
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'beers'; -- a real, current age
VACUUM FREEZE beers; -- genuinely resets it
A real age of 134 genuinely drops to 0 after a real VACUUM FREEZE. In production, autovacuum_freeze_max_age (200,000,000 by default) is the real threshold where PostgreSQL forces an emergency autovacuum that cannot be cancelled — reaching anywhere near that age is the genuine definition of a wraparound emergency, which is never safely reproducible at real scale on a shared demo server.
Backup recency gap
The real, unavoidable window between when a backup was taken and when a disaster happens — anything written inside that window is genuinely unrecoverable from that backup alone, confirmed directly in this lab by a row that exists before a DROP TABLE but is missing after a restore from a dump taken earlier.
pg_ctl promote
The real command that ends a standby's recovery mode and makes it a fully independent, writable primary on a new timeline. It is irreversible for that standby — once promoted, it can no longer resume following its old primary without being rebuilt as a standby again from scratch.
Replication slot bloat
The genuine, unbounded growth of retained WAL behind a replication slot whose consumer (a standby, or a logical subscriber) has stopped connecting. PostgreSQL will not reclaim that WAL on its own while the slot exists, since the entire point of a slot is to guarantee that WAL stays available — which is exactly why an abandoned slot left unnoticed can genuinely fill a disk.
Transaction ID wraparound
PostgreSQL transaction IDs are a real, finite 32-bit counter. age(relfrozenxid) tracks how long it has been since a table's oldest rows were last frozen; if that age is ever allowed to approach 2 billion, PostgreSQL forces an emergency, unstoppable autovacuum (and eventually refuses new transactions entirely) to prevent transaction ID reuse from silently corrupting visibility. A routine VACUUM FREEZE resetting this age to 0, as reproduced directly in this lab, is the same real mechanism that prevents that emergency from ever being reached in a healthy cluster.
💾 Scenario A: The Real Backup-Recency Gap
Dump a real table, insert a row after the dump, drop the table, restore it, and confirm the post-dump row is genuinely gone.
psql -U postgres -d beer_db -c "SELECT count(*) FROM styles;"as-postgres sh -c "pg_dump -U postgres -d beer_db -t styles -F c -f /tmp/styles_backup.dump" && ls -la /tmp/styles_backup.dumppsql -U postgres -d beer_db -c "INSERT INTO styles (name, abv_range, ibu_range, description) VALUES ('LateStyle', numrange(5,6), numrange(20,30), 'added after the dump');" -c "SELECT count(*) FROM styles;"psql -U postgres -d beer_db -c "DROP TABLE styles;"as-postgres sh -c "pg_restore -U postgres -d beer_db --clean --if-exists /tmp/styles_backup.dump"psql -U postgres -d beer_db -c "SELECT count(*) FROM styles;" -c "SELECT * FROM styles WHERE name = 'LateStyle';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM styles;" SET count ------- 25 (1 row) student@lab:~$ as-postgres sh -c "pg_dump -U postgres -d beer_db -t styles -F c -f /tmp/styles_backup.dump" && ls -la /tmp/styles_backup.dump -rw-r--r-- 1 postgres postgres 3677 Jun 26 16:02 /tmp/styles_backup.dump student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO styles (name, abv_range, ibu_range, description) VALUES ('LateStyle', numrange(5,6), numrange(20,30), 'added after the dump');" -c "SELECT count(*) FROM styles;" SET INSERT 0 1 count ------- 26 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "DROP TABLE styles;" SET DROP TABLE student@lab:~$ as-postgres sh -c "pg_restore -U postgres -d beer_db --clean --if-exists /tmp/styles_backup.dump" student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM styles;" -c "SELECT * FROM styles WHERE name = 'LateStyle';" SET count ------- 25 (1 row) id | name | abv_range | ibu_range | description ----+------+-----------+-----------+------------- (0 rows)
🔁 Scenario B: Build a Real Standby and Confirm It Streams
Enable replication, take a real base backup, start a real standby, and confirm data written on the primary genuinely streams across.
as-postgres sh -c "sed -i 's/wal_level = minimal/wal_level = replica/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c "sed -i 's/max_wal_senders = 0/max_wal_senders = 5/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w'as-postgres sh -c "rm -rf /tmp/standby && pg_basebackup -U postgres -D /tmp/standby -R -X stream"as-postgres sh -c "sed -i 's/port = 5432/port = 5433/' /tmp/standby/postgresql.conf" && as-postgres sh -c "pg_ctl start -D /tmp/standby -o '-p 5433' -w"psql -U postgres -d beer_db -c "CREATE TABLE crash_test (id serial primary key, note text); INSERT INTO crash_test (note) VALUES ('before-crash');"sleep 1 && psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT * FROM crash_test;" -c "SELECT pg_is_in_recovery();"student@lab:~$ as-postgres sh -c "sed -i 's/wal_level = minimal/wal_level = replica/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c "sed -i 's/max_wal_senders = 0/max_wal_senders = 5/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w' waiting for server to shut down.... done server stopped waiting for server to start.... done server started student@lab:~$ as-postgres sh -c "rm -rf /tmp/standby && pg_basebackup -U postgres -D /tmp/standby -R -X stream" student@lab:~$ as-postgres sh -c "sed -i 's/port = 5432/port = 5433/' /tmp/standby/postgresql.conf" && as-postgres sh -c "pg_ctl start -D /tmp/standby -o '-p 5433' -w" waiting for server to start.... done server started student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE crash_test (id serial primary key, note text); INSERT INTO crash_test (note) VALUES ('before-crash');" SET CREATE TABLE INSERT 0 1 student@lab:~$ sleep 1 && psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT * FROM crash_test;" -c "SELECT pg_is_in_recovery();" SET id | note ----+-------------- 1 | before-crash (1 row) pg_is_in_recovery ------------------- t (1 row)
💥 Scenario B: Crash the Primary, Promote the Standby
Simulate a genuine hard crash of the primary, then promote the standby with a real pg_ctl promote and confirm it now serves the pre-crash data as a real primary.
as-postgres sh -c 'pg_ctl stop -D /var/lib/postgresql/18/data -m immediate'psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();"as-postgres sh -c 'pg_ctl promote -D /tmp/standby -w'sleep 1 && psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();" -c "SELECT * FROM crash_test;"student@lab:~$ as-postgres sh -c 'pg_ctl stop -D /var/lib/postgresql/18/data -m immediate' waiting for server to shut down.... done server stopped student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();" SET pg_is_in_recovery ------------------- t (1 row) student@lab:~$ as-postgres sh -c 'pg_ctl promote -D /tmp/standby -w' waiting for server to promote.... done server promoted student@lab:~$ sleep 1 && psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();" -c "SELECT * FROM crash_test;" SET pg_is_in_recovery ------------------- f (1 row) id | note ----+-------------- 1 | before-crash (1 row)
🗑️ Scenario C: Replication Slot Bloat, and the Safe Fix
Create a replication slot that nothing ever consumes, watch its retained WAL genuinely grow, then clean it up safely with pg_drop_replication_slot.
psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_create_physical_replication_slot('bloat_slot', true);"psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;"psql -h localhost -p 5433 -U postgres -d beer_db -c "CREATE TABLE wal_filler (id serial primary key, pad text); INSERT INTO wal_filler (pad) SELECT repeat('x', 1000) FROM generate_series(1, 5000); CHECKPOINT;"psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;"psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_drop_replication_slot('bloat_slot');" -c "DROP TABLE wal_filler;"student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_create_physical_replication_slot('bloat_slot', true);" SET pg_create_physical_replication_slot ------------------------------------- (bloat_slot,0/9000054) (1 row) student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;" SET slot_name | active | restart_lsn | wal_status ------------+--------+-------------+------------ bloat_slot | f | 0/9000054 | reserved (1 row) student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "CREATE TABLE wal_filler (id serial primary key, pad text); INSERT INTO wal_filler (pad) SELECT repeat('x', 1000) FROM generate_series(1, 5000); CHECKPOINT;" SET CREATE TABLE INSERT 0 5000 CHECKPOINT student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;" SET slot_name | active | wal_status | retained_wal ------------+--------+------------+-------------- bloat_slot | f | reserved | 5759 kB (1 row) student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT pg_drop_replication_slot('bloat_slot');" -c "DROP TABLE wal_filler;" SET pg_drop_replication_slot --------------------------- (1 row) DROP TABLE
🧊 Scenario D: Transaction ID Age, Checked and Reset
Check a real table's current transaction ID age, freeze it with a real VACUUM FREEZE, and confirm the age genuinely resets to 0.
psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'beers';"psql -h localhost -p 5433 -U postgres -d beer_db -c "VACUUM FREEZE beers;"psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'beers';"student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'beers';" SET relname | age ---------+----- beers | 134 (1 row) student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "VACUUM FREEZE beers;" SET VACUUM student@lab:~$ psql -h localhost -p 5433 -U postgres -d beer_db -c "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'beers';" SET relname | age ---------+----- beers | 0 (1 row)
Lab 3.6.5 complete — Certificate 3 complete. Four real disasters, genuinely reproduced end-to-end:\n\n\n Scenario A: dump/restore data-loss gap : ✅ post-dump row genuinely lost\n Scenario B: crash + standby promotion : ✅ zero data loss, real promote\n Scenario C: replication slot bloat cleanup: ✅ retained WAL genuinely reclaimed\n Scenario D: transaction ID freeze : ✅ real age reset to 0\n
Enable JavaScript to run the live terminal and track your progress.