Point-in-time recovery, performed

Everything in this part has been building to one operation. This lesson is a recovery that was actually performed, transcribed.

The disaster

$ # the sequence that was run

  INSERT 1000 rows 'baseline row N'
[base backup taken here]
INSERT 500 rows 'good row N'
T1 = 2026-08-27 21:22:48.247655+00      <- 1500 rows present
DROP TABLE ledger;                       <- THE DISASTER
T2 = 2026-08-27 21:22:51.397478+00
CREATE TABLE aftermath; INSERT ...       <- work done after the disaster
SELECT pg_switch_wal();                  <- so the last segment is archived

Three seconds separate the good state from the disaster. The goal is to recover to T1: every row that existed then, nothing created after.

The restore

$ tar xzf /backup/base/base.tar.gz -C /restore
mkdir -p /restore/pg_wal && tar xzf /backup/base/pg_wal.tar.gz -C /restore/pg_wal
chmod 700 /restore

# no output; the mode 700 matters, PostgreSQL refuses to start otherwise

$ cat >> /restore/postgresql.auto.conf <<EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-27 21:22:48.247655+00'
recovery_target_action = 'promote'
archive_mode = 'off'
port = 5433
EOF
touch /restore/recovery.signal

# no output

Every line has a reason:

The recovery log, verbatim

$ pg_ctl -D /restore start && tail -f /restore/log/postgresql.log

LOG:  starting PostgreSQL 18.6 ...
LOG:  listening on IPv4 address "0.0.0.0", port 5433
LOG:  database system was interrupted; last known up at 2026-08-27 21:22:32 UTC
cp: cannot stat '/archive/00000002.history': No such file or directory
LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG:  restored log file "000000010000000000000003" from archive
LOG:  starting point-in-time recovery to 2026-08-27 21:22:48.247655+00
LOG:  redo starts at 0/3000028
LOG:  restored log file "000000010000000000000004" from archive
LOG:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG:  consistent recovery state reached at 0/3000120
LOG:  database system is ready to accept read-only connections
cp: cannot stat '/archive/000000010000000000000005': No such file or directory
LOG:  recovery stopping before commit of transaction 756, time 2026-08-27 21:22:51.345091+00
LOG:  redo done at 0/4034B48 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
LOG:  last completed transaction was at log time 2026-08-27 21:22:46.193856+00
LOG:  restored log file "000000010000000000000004" from archive
cp: cannot stat '/archive/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat '/archive/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  checkpoint complete: ... distance=16594 kB
LOG:  database system is ready to accept connections

The lines that look like failures and are not

cp: cannot stat '/archive/00000002.history': No such file or directory
cp: cannot stat '/archive/00000001.history': No such file or directory

Recovery probes for timeline history files. These are the restore_command reporting a miss, and a miss is exactly how recovery discovers there is no later timeline to follow. On a successful first recovery they are guaranteed to appear.

cp: cannot stat '/archive/000000010000000000000005': No such file or directory

Recovery asks for the next segment, is told there is none, and concludes the archive ends there. On a PITR whose target is comfortably before the end of the archive this line does not appear; here it did because the target was near the end.

Three alarming-looking lines in a recovery that worked perfectly. An operator who aborts on seeing them abandons a correct recovery.

The lines that report the actual outcome

LOG:  recovery stopping before commit of transaction 756, time 2026-08-27 21:22:51.345091+00
LOG:  last completed transaction was at log time 2026-08-27 21:22:46.193856+00
LOG:  selected new timeline ID: 2

The first names the transaction recovery stopped before — the DROP. The second reports what actually made it in. The third records the promotion.

Note the gap: the target was 21:22:48.247655 and the last completed transaction was at 21:22:46.193856, two seconds earlier. Recovery stops before the first commit at or after the target, so the true stopping point is the last commit that precedes it. You do not get the target instant; you get the last consistent state before it.

Verifying

$ SELECT min(id), max(id),
     count(*) FILTER (WHERE note LIKE 'good%')     AS good_rows,
     count(*) FILTER (WHERE note LIKE 'baseline%') AS baseline_rows
FROM ledger;
SELECT count(*) FROM pg_class WHERE relname = 'aftermath';
SELECT pg_is_in_recovery();

 min | max  | good_rows | baseline_rows
-----+------+-----------+---------------
 1 | 1500 |       500 |          1000

count
-------
   0          <- work done AFTER the target is absent

pg_is_in_recovery
-------------------
f                 <- promoted, accepting writes

All 1,500 rows. The dropped table is back, the work done after the disaster is absent, and the cluster is promoted.

$ pg_controldata /restore | grep TimeLineID
cat /restore/pg_wal/00000002.history

Latest checkpoint's TimeLineID:       2
Latest checkpoint's PrevTimeLineID:   1

1	0/4034B48	before 2026-08-27 21:22:51.345091+00

One line, three fields: the timeline diverged from timeline 1, at LSN 0/4034B48, because it was told to stop before 21:22:51.345091+00. Every future recovery reads this file to know that timeline 2 exists and where it branched.

Meanwhile, on the original:

$ SELECT count(*) FROM pg_class WHERE relname IN ('ledger','aftermath');

 count
-------
   1          <- only aftermath; ledger is still dropped

Both states exist. Nothing was destroyed to produce the recovery, and the operator can now compare the two and decide what to do — copy the table back, switch traffic to the restored cluster, or try a different target.

What to take from this

• Recover onto a copy, on a different port, with archive_mode = off.
• recovery.signal is what makes recovery happen. Without it every recovery setting is silently ignored.
• cp: cannot stat '...history' is normal probing, not a failure.
• Recovery stops before the first commit at or after the target, so the true stopping point is the last commit preceding it. Measured: a two-second gap.
• Verify by content, not by “it started” — row counts, absent objects, pg_is_in_recovery().
• Promotion creates a new timeline, and the 40-byte .history file is what makes both histories navigable.

Cross-course references

• Linux for Production Sysadmins — Part XLIX (Restore) and Part L (Disaster recovery) cover rehearsing a recovery rather than documenting one, and Part LXXXI (Incident command) covers who chooses the recovery target under pressure.
• Observability for Production Sysadmins — Part CIX (Incident investigation workflows) covers establishing when the damage happened, which is the input this whole procedure depends on.

Quiz