Crash Recovery Timeline, Recovery Consistency, and Corruption Boundaries

Learning outcomes

A clean shutdown is not the failure mode WAL was invented to solve. In this lesson you will create a completely disposable second PostgreSQL cluster, commit one ServiceHub event, leave a second transaction uncommitted, terminate the server with immediate shutdown, and restart it. The goal is to see the recovery boundary directly without risking the Chapter 01 lab.

1. Create a disposable crash-recovery cluster

The commands differ only in shell syntax. Use PostgreSQL 18 server utilities from the same installation. The explicit alternate port prevents accidental connection to the Chapter 01 instance.

export PG12_DATA="$HOME/pg12_crash_lab"rm -rf "$PG12_DATA"initdb -D "$PG12_DATA" -A trustpg_ctl -D "$PG12_DATA" -l "$PG12_DATA/server.log" -o "-p 55433 -c listen_addresses=localhost" startcreatedb -p 55433 servicehub_wal_crash

$env:PG12_DATA = Join-Path $HOME "pg12_crash_lab"if (Test-Path $env:PG12_DATA) { Remove-Item -Recurse -Force $env:PG12_DATA }initdb -D $env:PG12_DATA -A trustpg_ctl -D $env:PG12_DATA -l "$env:PG12_DATA\server.log" -o "-p 55433 -c listen_addresses=localhost" startcreatedb -p 55433 servicehub_wal_crash

PostgreSQL 18 initdb enables data checksums by default unless explicitly disabled. Verify rather than assume the cluster's exact setting.

psql -p 55433 -d servicehub_wal_crash -c "SELECT version(), current_database(), pg_is_in_recovery();"

2. Create a table and one durable committed row

CREATE TABLE public.recovery_probe (    probe_id integer PRIMARY KEY,    note text NOT NULL,    created_at timestamptz NOT NULL DEFAULT clock_timestamp());BEGIN;INSERT INTO public.recovery_probe (probe_id, note)VALUES (1, 'committed-before-immediate-stop');COMMIT;SELECT probe_id, note FROM public.recovery_probe ORDER BY probe_id;

Row 1 is the positive control: it was committed under the cluster's ordinary durable settings before the crash simulation.

3. Leave a second transaction uncommitted

Open a second psql session and deliberately leave the transaction open. Do not close this session normally.

BEGIN;INSERT INTO public.recovery_probe (probe_id, note)VALUES (2, 'uncommitted-at-immediate-stop');SELECT probe_id, note FROM public.recovery_probe ORDER BY probe_id;-- Keep this transaction open. Do not COMMIT or ROLLBACK.

Session B can see its own uncommitted row. Another session should not see it under the default Read Committed isolation level. That is MVCC visibility, not yet crash recovery.

SELECT probe_id, noteFROM public.recovery_probeORDER BY probe_id;

4. Stop immediately and restart

From a third shell, use immediate shutdown. PostgreSQL terminates server processes without a clean checkpoint. The next startup must perform crash recovery.

pg_ctl -D "$PG12_DATA" -m immediate stoppg_ctl -D "$PG12_DATA" -l "$PG12_DATA/server.log" -o "-p 55433 -c listen_addresses=localhost" start

pg_ctl -D $env:PG12_DATA -m immediate stoppg_ctl -D $env:PG12_DATA -l "$env:PG12_DATA\server.log" -o "-p 55433 -c listen_addresses=localhost" start

Expected log evidence includes an indication that the previous shutdown was interrupted/not clean, followed by REDO/recovery activity and eventually a message that the database system is ready to accept connections. Exact wording, LSNs, and timestamps are not stable output contracts.

During startup recovery PostgreSQL begins from the redo position associated with the last usable checkpoint and replays later WAL records needed to bring data files forward. It also reconstructs transaction commit/abort visibility. Recovery is therefore not “re-run the SQL statements”; it is application of WAL records to physical database state plus transaction-status rules.

A checkpoint is not a promise that every transaction after it was lost or that every page after it was dirty. It is a recovery starting boundary. The final recovered state is determined by WAL records and transaction outcomes up to the available end of WAL.

5. Verify business state after recovery

SELECT pg_is_in_recovery() AS in_recovery;SELECT probe_id, noteFROM public.recovery_probeORDER BY probe_id;

After startup has completed, pg_is_in_recovery() should be false. Row 1 should exist. Row 2 should not be committed. Recovery may physically replay WAL records related to aborted/incomplete work while still reconstructing transaction visibility so the uncommitted row is not part of the committed database state.

6. Read recovery logs as a timeline

tail -n 80 "$PG12_DATA/server.log"

Get-Content "$env:PG12_DATA\server.log" -Tail 80

Look for the sequence: abnormal prior shutdown → recovery/REDO start → REDO completion/end-of-WAL handling → readiness. Do not build monitoring that parses one exact English sentence if a structured or supported metric is available.

7. Crash recovery is not arbitrary corruption repair

WAL replays valid recorded changes after a crash. Full-page writes reduce the risk that an interrupted operating-system page write leaves a torn page that row-level WAL cannot reconstruct. Data checksums can detect many forms of on-disk page corruption, but detection does not manufacture a clean replacement page. A failed disk, overwritten relation file, missing WAL history, or corrupted backup crosses into restore/recovery engineering.

8. Cleanup the disposable cluster

pg_ctl -D "$PG12_DATA" stoprm -rf "$PG12_DATA"

pg_ctl -D $env:PG12_DATA stopRemove-Item -Recurse -Force $env:PG12_DATA

Production crash testing should use infrastructure designed for failure injection, with backups, observability, fencing, and clear rollback procedures. Never use process-kill experiments as a substitute for verified storage-failure testing.

Authoritative references

Durability behavior depends on PostgreSQL version, storage semantics, configuration source, replication topology, and the exact failure mode. The references below are the primary source for the mechanisms used in this lesson.

• PostgreSQL 18 — WAL Configuration and Crash Recovery
• PostgreSQL 18 — Reliability
• PostgreSQL 18 — Data Checksums
• PostgreSQL 18 — pg_ctl
• PostgreSQL 18 — pg_resetwal
• PostgreSQL 18 — System Administration / Recovery Functions