PostgreSQL point-in-time recovery to a target before a mistake

Objective

A base backup restores the world as it was when the backup ran. WAL archiving turns that single point into a continuum: any instant between the backup and the last archived segment becomes reachable. That is the difference between “we lost a day” and “we lost the four seconds containing the mistake”.

You will build the chain, break the database on purpose at a recorded time, and recover to an instant before it - then prove the recovery against a number written down beforehand, not against the fact that the server started.

Architecture

The archive is the only thing that connects the backup to the mistake. The dotted edge is the trap this lab exists to make you meet once, cheaply.

flowchart TD
    A["rbdr-primary, port 5433\narchive_mode = on\narchive_command copies to rbdr-wal-archive"] --> B["pg_basebackup -X stream -c fast\nbase backup holds 45000 rows"]
    B --> C["business continues\n50000 rows, sum(amount)=825025000\ntarget recorded 13:34:40.077562+00"]
    C --> D["unqualified DELETE\nrows after the mistake: 0\ncommitted 13:34:42.096745+00"]
    A -->|"5 WAL segments plus one .backup label"| ARC["rbdr-wal-archive\narchived=6 failed=0"]
    D --> E["copy of the base backup\narchive_mode = off\nrecovery_target_time = the recorded target"]
    ARC --> E
    E --> F["stopped before commit of transaction 836\nselected new timeline ID: 2\n50000 rows, sum 825025000"]
    E -.->|"archive_mode left ON: the copy writes 00000002.* into the production archive"| ARC

Requirements

• Mode B-nested. The capture ran in a container: one cluster, a second cluster started from its base backup, both on loopback, no replication.
• PostgreSQL 18. Everything quoted below came from this build:

$ postgres --version

postgres (PostgreSQL) 18.6 (Debian 18.6-1.pgdg13+2)

• Everything this lab creates on disk is prefixed rbdr- and lives under one directory, so Cleanup can be scoped and asserted. The deliverable files deliberately carry no prefix, which is why that assertion still holds after they are kept.
• The capture ran under /work inside a container, so its paths, LSNs, transaction ids and timestamps are its own. The row counts, the checksum and the timeline number are the point, and those reproduce.

What that capture does and does not cover. The transcript records the base backup, the post-backup workload, the recorded target, the mistake, the archiver statistics, the full recovery log and the validated result - the substance of Tasks 4 through 9. It does not record Task 1, the cluster initialisation in Task 2, the archiver polling loop, the failing case in Task 10, or the Cleanup diff. Those older embedded blocks retain their original provenance. A later complete run from Task 1 through Cleanup is captured in docs/courses/backup-dr/execution-evidence/backup-dr-lab-22-postgresql-pitr-to-a-chosen-target-2026-08-29.txt and supports the last_executed date.

Scenario

An orders database is backed up nightly and archives WAL continuously. At lunchtime somebody opens a psql session against production, writes a DELETE and forgets the WHERE. The table is empty, the transaction is committed, and the application is still writing.

The nightly backup is fourteen hours old, so restoring it alone would discard a morning of trade. What you need is the instant before the statement committed, and the only artefacts that can produce it are a base backup and every WAL segment written since.

Tasks

Task 1 - Record the pre-lab state

Cleanup is diffed against this file. Record it before anything exists.

LAB="$HOME/rbdr-lab-22"
mkdir -p "$LAB"

{
  command -v initdb || echo "no initdb on PATH"
  command -v pg_basebackup || echo "no pg_basebackup on PATH"
  pgrep -a postgres || echo "no postgres process running"
  ls -d "$LAB"/rbdr-* 2>&1
} | tee "$LAB/state.pre-lab"

The last line reports an ls error for the glob. That absence is the baseline; at the end the same four lines have to report it identically.

Task 2 - Archive WAL to a destination outside the data directory

LAB="$HOME/rbdr-lab-22"
PRIMARY="$LAB/rbdr-primary"
ARCHIVE="$LAB/rbdr-wal-archive"
mkdir -p "$ARCHIVE"
initdb -D "$PRIMARY" --auth=trust > "$LAB/rbdr-initdb.log" 2>&1

cat >> "$PRIMARY/postgresql.conf" <<EOF
port = 5433
unix_socket_directories = '$LAB'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f $ARCHIVE/%f && cp %p $ARCHIVE/%f'
EOF

pg_ctl -D "$PRIMARY" -l "$LAB/rbdr-primary.log" start
psql -h "$LAB" -p 5433 -d postgres -Atc "SHOW archive_mode" -c "SHOW archive_command"

The archive lives beside the data directory, not inside it: a destination under PGDATA is lost with the volume that carried the failure, and is copied into every base backup taken afterwards. The test ! -f guard refuses to overwrite a segment that is already archived, turning a silent overwrite into a visible failed_count.

Task 3 - Create the business data and seed it

LAB="$HOME/rbdr-lab-22"
psql -h "$LAB" -p 5433 -d postgres -c \
  "CREATE TABLE rbdr_orders (id bigint PRIMARY KEY, amount bigint NOT NULL)"
psql -h "$LAB" -p 5433 -d postgres -c \
  "INSERT INTO rbdr_orders SELECT g, 16001 + ((g - 1) % 1000) FROM generate_series(1, 5000) AS g"
psql -h "$LAB" -p 5433 -d postgres -c \
  "INSERT INTO rbdr_orders SELECT g, 16001 + ((g - 1) % 1000) FROM generate_series(5001, 45000) AS g"
psql -h "$LAB" -p 5433 -d postgres -Atc "SELECT count(*) FROM rbdr_orders"

Forty-five thousand rows, matching the capture’s state at backup time. The amount expression is chosen so that summed over 50,000 rows it reaches 825,025,000, reproducing the capture’s checksum. The capture’s own generator is not in the transcript; what matters is that the number was recorded before the incident, not which arithmetic produced it.

Task 4 - Take the base backup

LAB="$HOME/rbdr-lab-22"
pg_basebackup -h "$LAB" -p 5433 -D "$LAB/rbdr-base" -X stream -c fast
echo "pg_basebackup exit code: $?"

$ pg_basebackup -D /work/base -X stream -c fast

>>> exit code: 0
rows contained in the base backup: 45000

-X stream streams the WAL generated during the copy into the backup’s own pg_wal, so it reaches a consistent state without touching the archive; -c fast requests an immediate checkpoint rather than waiting for the next scheduled one. Neither flag makes the backup a recovery point on its own: without the archive, rbdr-base restores to 45000 rows and nothing later.

Task 5 - Business continues, and you write the invariant down

LAB="$HOME/rbdr-lab-22"
psql -h "$LAB" -p 5433 -d postgres -c \
  "INSERT INTO rbdr_orders SELECT g, 16001 + ((g - 1) % 1000) FROM generate_series(45001, 50000) AS g"

psql -h "$LAB" -p 5433 -d postgres -Atc \
  "SELECT 'rows=' || count(*) || ' sum=' || sum(amount) FROM rbdr_orders" \
  | tee "$LAB/invariant.txt"

psql -h "$LAB" -p 5433 -d postgres -Atc "SELECT now()" | tee "$LAB/target-time.txt"
sleep 2

$ count the rows, checksum the amounts, and record now() as the recovery target

--- business continues after the backup: 5,000 more orders arrive ---
rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

The target comes from the server, not from your wristwatch. recovery_target_time is compared against transaction commit timestamps in WAL, so a target derived from a different clock lands somewhere you did not choose. The sleep 2 opens a deliberate gap between the target and the mistake, which is what makes the recovery boundary visible in Task 9.

Task 6 - The mistake

LAB="$HOME/rbdr-lab-22"
echo "about to empty: rbdr_orders on port 5433"
psql -h "$LAB" -p 5433 -d postgres -c "DELETE FROM rbdr_orders"
psql -h "$LAB" -p 5433 -d postgres -Atc "SELECT count(*) FROM rbdr_orders"

$ DELETE FROM rbdr_orders, then count the rows

--- and then somebody runs an unqualified DELETE ---
rows after the mistake        : 0

Task 7 - Poll pg_stat_archiver before you try to recover

LAB="$HOME/rbdr-lab-22"
CUR=$(psql -h "$LAB" -p 5433 -d postgres -Atc \
  "SELECT pg_walfile_name(pg_current_wal_lsn())")
psql -h "$LAB" -p 5433 -d postgres -Atc "SELECT pg_switch_wal()" > /dev/null

for i in $(seq 1 30); do
  LAST=$(psql -h "$LAB" -p 5433 -d postgres -Atc \
    "SELECT last_archived_wal FROM pg_stat_archiver")
  FAILED=$(psql -h "$LAB" -p 5433 -d postgres -Atc \
    "SELECT failed_count FROM pg_stat_archiver")
  echo "attempt $i last_archived_wal=$LAST failed_count=$FAILED"
  if [ "$LAST" = "$CUR" ]; then echo "the segment holding the mistake is archived"; break; fi
  sleep 1
done

psql -h "$LAB" -p 5433 -d postgres -Atc \
  "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver"
ls "$LAB/rbdr-wal-archive"

$ SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver, then list the archive

  pg_stat_archiver:
  archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive   : 5
  000000010000000000000001
  000000010000000000000002
  000000010000000000000003
  000000010000000000000003.00000028.backup
  000000010000000000000004
  000000010000000000000005

Read the two counts against each other. archived=6 and six filenames, but the capture’s own label says five segments: the sixth file is the .00000028.backup label the base backup left behind. failed=0 is the number that gates the recovery - non-zero means the archive is behind the database, and the target you chose may sit in a segment that only ever existed on the failed server.

A segment is archived when it is full or switched. Until pg_switch_wal() runs, the WAL holding the last minutes of trade is still in pg_wal on the machine you are recovering from - the machine you may not have.

Task 8 - Recover a copy of the base backup to the recorded target

LAB="$HOME/rbdr-lab-22"
ARCHIVE="$LAB/rbdr-wal-archive"
RESTORE="$LAB/rbdr-restore"
TARGET=$(cat "$LAB/target-time.txt")

cp -a "$LAB/rbdr-base" "$RESTORE"
echo "archive files before recovery: $(ls "$ARCHIVE" | wc -l)" | tee "$LAB/recovery-report.txt"

cat >> "$RESTORE/postgresql.auto.conf" <<EOF
port = 5434
archive_mode = off
restore_command = 'cp $ARCHIVE/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'promote'
EOF
touch "$RESTORE/recovery.signal"

pg_ctl -D "$RESTORE" -l "$LAB/rbdr-restore.log" start
echo "archive files after recovery: $(ls "$ARCHIVE" | wc -l)" | tee -a "$LAB/recovery-report.txt"

archive_mode = off is the line that matters and the one a runbook forgets. The copy carries the primary’s postgresql.conf verbatim, archive_mode = on and archive_command included, so without this override the recovered cluster writes its own history into the archive production depends on. Task 10 does exactly that, on purpose, so you meet it once.

recovery_target_action is set explicitly here to reproduce the captured log. Its documented default is pause - the cluster reaches the target and stops, accepting read-only connections until pg_wal_replay_resume() is called - and the documentation notes that the server falls back to shutdown if hot_standby is off. Pause is the safer incident choice, because it lets you inspect the data before the branch becomes permanent.

Task 9 - Read where recovery stopped, and validate against the invariant

LAB="$HOME/rbdr-lab-22"
grep -E "point-in-time recovery|recovery stopping|last completed|timeline|archive recovery complete" \
  "$LAB/rbdr-restore.log" | tee -a "$LAB/recovery-report.txt"

psql -h "$LAB" -p 5434 -d postgres -Atc \
  "SELECT 'rows=' || count(*) || ' sum=' || sum(amount) FROM rbdr_orders" \
  | tee "$LAB/restored-invariant.txt"

diff "$LAB/invariant.txt" "$LAB/restored-invariant.txt" \
  && echo "RESTORE VALIDATED: recovered data matches the pre-incident invariant"

$ read the restored cluster's log

2026-08-28 13:35:12.698 UTC [631] LOG:  database system was interrupted; last known up at 2026-08-28 13:34:37 UTC
cp: cannot stat '/work/wal-archive/00000002.history': No such file or directory
2026-08-28 13:35:12.701 UTC [631] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
2026-08-28 13:35:12.707 UTC [631] LOG:  restored log file "000000010000000000000003" from archive
2026-08-28 13:35:12.708 UTC [631] LOG:  starting point-in-time recovery to 2026-08-28 13:34:40.077562+00
2026-08-28 13:35:12.713 UTC [631] LOG:  restored log file "000000010000000000000004" from archive
2026-08-28 13:35:12.713 UTC [631] LOG:  consistent recovery state reached at 0/3000120
2026-08-28 13:35:12.713 UTC [625] LOG:  database system is ready to accept read-only connections
2026-08-28 13:35:12.726 UTC [631] LOG:  restored log file "000000010000000000000005" from archive
2026-08-28 13:35:12.735 UTC [631] LOG:  recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00
2026-08-28 13:35:12.735 UTC [631] LOG:  last completed transaction was at log time 2026-08-28 13:34:38.041366+00
2026-08-28 13:35:12.736 UTC [631] LOG:  selected new timeline ID: 2
2026-08-28 13:35:12.740 UTC [631] LOG:  archive recovery complete
2026-08-28 13:35:12.745 UTC [625] LOG:  database system is ready to accept connections

Three lines are the whole recovery. recovery stopping before commit of transaction 836 names the transaction that was refused - the DELETE, whose commit stamp 13:34:42.096745+00 falls after the target. last completed transaction was at log time 2026-08-28 13:34:38.041366+00 names the boundary actually reached. selected new timeline ID: 2 says the cluster has branched: from here its WAL is 00000002..., while timeline 1 remains in the archive, unmodified and still recoverable.

The cp: cannot stat line is not a failure. Recovery asks the archive for 00000002.history to learn whether a later branch already exists; on a first recovery there is none, and the missing-file exit is how restore_command reports that.

$ count rows and sum amounts on the recovered cluster, and compare with invariant.txt

rows recovered  : 50000   (expected 50000)
sum(amount)     : 825025000   (expected 825025000)
RECOVERED - row count and business checksum both match the pre-DELETE state

Task 10 - The failing case: the copy that writes into the production archive

LAB="$HOME/rbdr-lab-22"
ARCHIVE="$LAB/rbdr-wal-archive"
TARGET=$(cat "$LAB/target-time.txt")
BEFORE=$(ls "$ARCHIVE" | wc -l)

cp -a "$LAB/rbdr-base" "$LAB/rbdr-fail"
cat >> "$LAB/rbdr-fail/postgresql.auto.conf" <<EOF
port = 5435
restore_command = 'cp $ARCHIVE/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'promote'
EOF
touch "$LAB/rbdr-fail/recovery.signal"

pg_ctl -D "$LAB/rbdr-fail" -l "$LAB/rbdr-fail.log" start
sleep 5
AFTER=$(ls "$ARCHIVE" | wc -l)
echo "archive files before: $BEFORE  after: $AFTER" | tee -a "$LAB/recovery-report.txt"
ls "$ARCHIVE" | grep -c '^00000002' || echo "no timeline-2 files in the archive"

AFTER is larger than BEFORE, and the archive now holds files production never wrote. The one that always appears is 00000002.history, written and archived the moment the copy promoted; timeline-2 segments join it as soon as one fills or is switched. The history file is the lasting damage: any later recovery from this archive finds it, concludes that branch is already taken, and selects timeline 3 instead - a different answer to the same question, produced by an artefact a restore test left behind.

Validation

Every row names the command, the exact string, and the exit code expected.

The two rows in bold are the failing case: a recovery that succeeded by every other measure while quietly writing into the archive that protects production.

Expected Outcome

An RPO of zero here is a property of this arrangement - continuous archiving, a switched segment, and a target chosen with knowledge of the incident - not of PostgreSQL. Move the target one second later and transaction 836 replays, the table empties again, and every command still exits 0.

Troubleshooting

Cleanup

LAB="$HOME/rbdr-lab-22"
for D in rbdr-fail rbdr-restore rbdr-primary; do
  [ -d "$LAB/$D" ] || continue
  pg_ctl -D "$LAB/$D" -m immediate stop || echo "$D: already stopped"
done
sleep 2
rm -rf "$LAB"/rbdr-primary "$LAB"/rbdr-base "$LAB"/rbdr-restore "$LAB"/rbdr-fail "$LAB"/rbdr-wal-archive
rm -f "$LAB"/rbdr-primary.log "$LAB"/rbdr-restore.log "$LAB"/rbdr-fail.log "$LAB"/rbdr-initdb.log

{
  command -v initdb || echo "no initdb on PATH"
  command -v pg_basebackup || echo "no pg_basebackup on PATH"
  pgrep -a postgres || echo "no postgres process running"
  ls -d "$LAB"/rbdr-* 2>&1
} | tee "$LAB/state.post-lab"

diff "$LAB/state.pre-lab" "$LAB/state.post-lab" \
  && echo "CLEAN: post-lab state matches the baseline recorded in Task 1"

The diff must print nothing and exit 0. Run it in the same shell that ran Task 1 so PATH is comparable. The six deliverable files are kept; none matches the rbdr-* glob, which is why the assertion still holds.

Production notes

• The archive is a separate failure domain or it is not an archive. A destination inside PGDATA, or on the same volume, dies with the event you are recovering from and is copied into every subsequent base backup.
• Alert on pg_stat_archiver, not on the backup job. failed_count rising means the recoverable window has stopped advancing while the nightly job keeps reporting success. That divergence is invisible until the restore.
• A base backup without its WAL restores to the backup, not to the incident. The 45000-row figure in Task 4 is what rbdr-base alone can give you; the 50000-row result needed three archived segments as well.
• Turn archive_mode off on every recovered copy, in a template, not from memory. A restore test that pollutes the production archive is worse than no restore test, because it leaves a history file that redirects the next real recovery.
• Prefer the documented pause action for a real incident. Reaching the target and stopping lets you query the data before promotion writes a new timeline; promoting first means a second recovery if the target was wrong.
• Record the invariant before you need it. Row counts and business checksums taken from the damaged database after the fact prove nothing. The comparison in Task 9 only had meaning because invariant.txt was written in Task 5.

What You Learned

• A point in time is only reachable if the segment holding it is in the archive. pg_switch_wal() and a poll of last_archived_wal are what make the last minutes of trade recoverable from somewhere other than the failed machine.
• Recovery tells you exactly where it stopped. recovery stopping before commit of transaction 836 and last completed transaction was at log time 2026-08-28 13:34:38.041366+00 are the boundary, in the log, for free.
• A recovery creates a branch. selected new timeline ID: 2 means the original history survives in the archive and the recovered cluster is a new line - which is also why a stray 00000002.history misdirects the next one.
• A copied data directory brings its configuration with it. archive_mode and archive_command come along, and a recovered copy will write into the archive it just read from unless you say otherwise.
• The restore is proved by a number written down beforehand. 50000 rows and sum(amount)=825025000 were recorded before the DELETE existed. Without them, a cluster that starts and answers queries looks identical whether it holds the right data or not.