Performing a point-in-time recovery · Backup & DR

Archive continuity was the previous lesson’s subject: a base backup plus an unbroken run of WAL segments after it is what turns a set of nightly instants into a range of recoverable moments. This lesson spends that archive. What follows is one capture on PostgreSQL 18.6 — a base backup, five thousand rows of ordinary business afterwards, a timestamp written down, an unqualified DELETE that emptied the table, and a recovery that put the rows back.

The procedure itself is four decisions and a start command. The part worth learning slowly is how the server narrates what it is doing, because the log states where replay stopped, and the data can only agree with it afterwards.

timeline
    title Point-in-time recovery chooses a branch, not merely a file
    Base backup : 45,000 rows : replay begins on timeline 1
    Business WAL : 5,000 later rows : expected count 50,000 recorded
    Recovery target : timestamp recorded : stop before transaction 836 commits
    Destructive WAL : unqualified DELETE : excluded from replay
    Promotion : validation returns 50,000 rows : timeline 2 begins

The target sits after the valid business writes and before the destructive transaction. Promotion creates a new history from that point; it does not erase timeline 1, and later recovery tooling must know which branch it is following.

What the archive was asked to undo

The setup exists to make the recovery checkable. A base backup was taken with pg_basebackup -D /work/base -X stream -c fast, and it reported that it contained 45000 rows. Business then continued: five thousand more orders arrived, taking the table to 50000 rows and the business checksum to sum(amount)=825025000, and a target time was recorded. Then the mistake, in the form it usually takes — a DELETE with no WHERE clause.

Data-loss riskthe state the archive has to be able to undo— EXECUTION VALIDATED on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). Part C of the capture; the labelled lines are the capture script's own summary of what it did and what it then measured.

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

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

--- 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

--- and then somebody runs an unqualified DELETE ---
rows after the mistake        : 0
pg_stat_archiver:
  archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive   : 5
  000000010000000000000001
  000000010000000000000002
  000000010000000000000003
  000000010000000000000003.00000028.backup
  000000010000000000000004
  000000010000000000000005

Three things were written down before the incident and they are what make this recovery something other than a guess: a row count, a checksum over the money column, and a timestamp. None of them is a database feature. All three are decisions somebody made in advance about what evidence a recovery would have to produce, which is the same discipline this course applied to a restic repository in Part VIII and to an etcd snapshot in Part XIII.

The archive side is equally worth reading before starting anything. pg_stat_archiver reports archived=6 failed=0 last=000000010000000000000005 and the listing shows six objects: five segments named 000000010000000000000001 through 000000010000000000000005, plus 000000010000000000000003.00000028.backup, the label file recording where the base backup began. Six archived objects, six files present, nothing failed. The recovery needs segments 3, 4 and 5 — everything from the base backup’s starting segment forward. Segments 1 and 2 belong to history before the backup and will not be touched.

Four decisions, then a start command

A point-in-time recovery is not a command. It is a directory plus three settings plus a file whose existence is itself a signal, and then an ordinary server start.

The first decision is where the base backup goes. Into a new directory, always: /work/base in the capture, listening on port 5434, which left the emptied cluster’s own data directory and port alone. Recovering beside the damaged system rather than over it keeps the evidence, keeps the fallback, and lets you compare the two.

The second is how the server obtains archived segments. restore_command is a shell command with %f for the file the server wants and %p for where it wants it put. The capture used cp, which is honest for a local archive directory and would be an object-storage client or a pgbackrest invocation elsewhere.

The third is where to stop: recovery_target_time, set to the timestamp recorded before the DELETE. The fourth is what to do on arrival: recovery_target_action, which the capture set explicitly to promote — as the promotion at the end of its log shows, since the boot value would have paused there instead.

Then the signal file. recovery.signal in the data directory is what tells the server this is an archive recovery rather than a normal start; without it the three settings above are simply not consulted.

BASE=/work/base
ARCHIVE=/work/wal-archive
TARGET='2026-08-28 13:34:40.077562+00'

cat >> "$BASE/postgresql.conf" <<EOF
restore_command = 'cp $ARCHIVE/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'promote'
EOF

touch "$BASE/recovery.signal"
pg_ctl -D "$BASE" start

recovery_target_action defaults to pause, and a recovery that reaches its target holds there

This one is worth carrying in your head, because it was wrong in six places in an earlier course in this series, including a graded question. When a recovery target is reached, recovery_target_action defaults to pause, not promote. The server stops replay, stays in recovery, and accepts read-only connections until somebody calls pg_wal_replay_resume() or promotes it.

That default is the safe one: it gives you a chance to look at the data before the timeline branches, and it is exactly what you want during an investigation. It is also the reason a recovery automated by someone who assumed otherwise appears to hang. The log will say the target was reached; the deployment script waiting for a writable database will time out anyway. Set the value explicitly in either direction, and put the reason next to it in the runbook.

`restored log file`, then `recovery stopping before commit`

With the settings in place the recovery is a server start, and from that moment the log is the instrument. Read it first, and read the data second.

Service impact possiblethe recovery log, from startup to the point where the copy becomes coherent— EXECUTION VALIDATED on PostgreSQL 18.6. Part C of the capture, recovering /work/base on port 5434; the block is a contiguous excerpt of the server's own log, including the archive command's stderr.

$ pg_ctl -D /work/base start

  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.708 UTC [631] LOG:  redo starts at 0/3000028
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:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
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

Five of those lines carry the whole story of the replay, and one of them is not an error even though it looks like one.

database system was interrupted; last known up at 2026-08-28 13:34:37 UTC is the copy admitting it is not a clean shutdown, which is expected: a base backup of a live cluster never is. The cp: cannot stat line on stderr is the server asking the archive whether a timeline history file exists, and being told no; that question and its answer matter later.

starting point-in-time recovery to 2026-08-28 13:34:40.077562+00 is the line to look for first, every time. It is the server reading back the target you gave it. If that line is absent, no target is in effect, and there are two ways to arrive there. Either recovery.signal was never created — the documented way to start the server in targeted recovery mode at all, so without it restore_command is not consulted and nothing is fetched from the archive — or the signal file is present with no recovery_target_* setting alongside it, in which case the documented behaviour is that recovery “ends when the archived WAL is fully replayed”, the DELETE included, and the server reports success while doing it. The two failures look nothing alike in the data and identical in the exit status.

restored log file "000000010000000000000003" from archive, then the same line for 000000010000000000000004 and 000000010000000000000005: each segment arrives through restore_command, and each arrival is logged by its full name. Three requested, three restored. These lines are the archive’s continuity being demonstrated rather than assumed. Counting them against the segments you expect is the cheapest check available during a recovery.

consistent recovery state reached at 0/3000120 is the one most people misread. It is not the destination; it is the point at which the file copy stops being torn. starting backup recovery with redo LSN 0/3000028 and completed backup recovery with ... end LSN 0/3000120 bracket the WAL written while pg_basebackup was running, and only once that range has been replayed do the pages on disk describe a single coherent instant. That is why read-only connections open at exactly that LSN and not before.

Service impact possiblethe same log, from the last segment through the stop to the promotion— EXECUTION VALIDATED on PostgreSQL 18.6. Part C of the capture, continuing directly from the block above with no lines omitted.

$ pg_ctl -D /work/base start

  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:  redo done at 0/52EBC90 system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
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
cp: cannot stat '/work/wal-archive/00000002.history': No such file or directory
2026-08-28 13:35:12.736 UTC [631] LOG:  selected new timeline ID: 2
cp: cannot stat '/work/wal-archive/00000001.history': No such file or directory
2026-08-28 13:35:12.740 UTC [631] LOG:  archive recovery complete
2026-08-28 13:35:12.740 UTC [629] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-08-28 13:35:12.744 UTC [629] LOG:  checkpoint complete: wrote 1290 buffers (7.9%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=0.004 s, sync=0.001 s, total=0.004 s; sync files=6, longest=0.001 s, average=0.000 s; distance=35759 kB, estimate=35759 kB; lsn=0/52EBC90, redo lsn=0/52EBC90
2026-08-28 13:35:12.745 UTC [625] LOG:  database system is ready to accept connections

recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00 is the sentence the whole recovery exists to produce. The server read a commit record whose timestamp lies past the target and refused to apply it. Note what the line does not say: it does not say what transaction 836 did. Given the sequence it is almost certainly the DELETE, but the server has no opinion on that and neither should the log-reading. What is established is narrower and stronger — nothing that committed at or after 13:34:42.096745 is present in this database.

redo done at 0/52EBC90 fixes the stopping point in the WAL stream, and last completed transaction was at log time 2026-08-28 13:34:38.041366+00 gives the other half of the picture. The target was 13:34:40.077562. The last transaction actually applied committed at 13:34:38.041366, two seconds earlier. Recovery did not stop at the target instant; it stopped at the last transaction boundary before it, because those are the only points that exist.

Why replay lands on a commit boundary and not on a clock reading

WAL is a sequence of records, not a timeline of moments. The documented definition of recovery_target_time is a comparison against transaction commit times, and the capture’s two stop lines say the same thing from the other side: recovery stopping before commit of transaction 836, time ... and last completed transaction was at log time ... each name a commit and nothing else. A commit is the only event in the stream whose time a client ever observed, so it is the only kind of record the target check has to work with.

So the check has exactly one place it can happen. Replay reads records in order and applies them; when it reaches a commit record it compares that record’s timestamp against recovery_target_time. If the timestamp is past the target, replay stops before applying that record, so the transaction never commits and nothing it wrote is ever visible — which is why the log says “stopping before commit” rather than “stopping at”. The reachable recovery points are therefore the commits, and the effective recovery point is the last commit that precedes the target. In this capture the gap between the two was 2.036196 seconds of wall clock and zero transactions — but on a busy system it can be milliseconds, and on an idle one it can be hours.

Two consequences follow directly. First, a target expressed in wall-clock time is only as good as the clock that stamped those commits; a database whose clock drifted is a database whose targets mean something other than what you think. Second, when the transaction you need to exclude has to be excluded exactly, a timestamp is the blunt instrument. recovery_target_xid names a transaction, recovery_target_lsn names a position in the stream, and recovery_target_name names a label somebody created deliberately in advance. The log line naming transaction 836 is, conveniently, the input to a second and sharper attempt.

The other mechanism worth understanding is how replay learns that the archive has run out. restore_command is asked for each segment by name, and the documentation is explicit that the command “will be asked for file names that are not present in the archive; it must return nonzero when so asked”. A non-zero exit is an answer, not a failure — the same answer the three cp: cannot stat lines in this capture carry.

What the server does with that answer depends on whether a target is set. Targeted recovery mode “ends when the archived WAL is fully replayed, or when recovery_target is reached”, so a recovery given no target ends wherever the archive ends and reports success — short of where the operator assumed, with nothing in the log saying so. That is the case the previous lesson took apart, and the reason it treated archive continuity as a property to monitor rather than to assume.

`selected new timeline ID: 2`

The closing lines of the recovery are about the future rather than the past. After reporting the last completed transaction the server asks the archive for 00000002.history, is told there is no such file, selects timeline 2, asks for 00000001.history as well, and declares archive recovery complete.

A timeline is a branch of WAL history. The database that has just been recovered did not continue timeline 1 — it forked from it, at LSN 0/52EBC90, and everything it writes from now on belongs to timeline 2. That fork is visible in the segment names themselves: the leading field of 000000010000000000000005 is the timeline, which is why every segment this archive holds begins 00000001 and the promoted server’s will begin 00000002. Two histories of the same database can therefore sit in one archive without a single name collision.

That naming scheme is also why the log carries three .history probes rather than none, because a shared archive has to be asked which branches it already holds before a new one is taken. The first, before replay begins, asks whether timeline 2 has already been claimed in this archive; the answer was no, so 2 was free to take. The two after redo done belong to writing the fork down. A later recovery reads those same files to discover which timelines are already spoken for, which is why the promoted server has to archive its history file and the archive has to keep it.

The archive is now two histories and only one of them is being written

After a promotion the recovered instance is the production database and its WAL flows into the archive under 00000002.... The old history does not become irrelevant on that day. Every recovery point between the base backup and 13:34:42.096745 lives on timeline 1, in segments 3, 4 and 5, and a retention rule that keeps “the last N segments” or “everything since the newest base backup” can quietly remove the branch that a second, better-targeted recovery would need. The capture recovered once; real incidents frequently recover twice, because the first target was chosen with incomplete information.

Retention for a WAL archive is a decision about which recovery windows remain open, on which timelines, and it is made per timeline or not at all. Take a fresh base backup on the new timeline as soon as the recovered instance is accepted as production, and let that backup — not the promotion — be the event that permits any pruning of what came before it.

50000 rows and `sum(amount)=825025000`

Only now does the data get looked at, and only against numbers recorded before the incident. The check is a count(*) and a sum(amount) over the business table on the promoted instance, compared against the row count and the checksum written down earlier — two comparisons, both against values the recovered database could not have supplied.

Read-only / Safethe recovered database, checked against values written down before the DELETE— EXECUTION VALIDATED on PostgreSQL 18.6, against the promoted instance on port 5434. Part C of the capture; the three lines are the capture script's own summary and the labels and expectations are its own.

$ psql -p 5434

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

The capture’s own note on this is the sentence to take away: what was validated is “not that the server started, but that the data it now holds matches an independently recorded property of the business data from before the incident”. The server started in Part A of that same capture too, on a copy taken with cp from a live data directory, and returned rows there as well. Starting is cheap. Agreeing with a number nobody could have derived from the recovered database is not.

Notice also that the two independent lines of evidence agree. The log said replay stopped before a commit at 13:34:42.096745 and after one at 13:34:38.041366; the table holds exactly the 50000 rows that existed in that window and the checksum that went with them. When those two disagree — a log that stopped where you asked and data that does not match — you have learned something important about your recorded expectations, and you have learned it before telling anyone the recovery is finished.

What none of this proves is that the target was the right target. A point-in-time recovery does exactly what it is told, to sub-second precision, and the precision is not the hard part. Establishing when the damage began usually is, and it comes from the application log, the audit trail or the pipeline that ran the statement, not from the database that faithfully recorded it.

What to take from this

The base backup contained 45000 rows and the recovered database returned 50000 with sum(amount)=825025000, matching the values recorded before the DELETE. The extra 5000 rows came out of the archive, not the backup.
starting point-in-time recovery to 2026-08-28 13:34:40.077562+00 is the line that proves the target was read at all. Without it no target is in effect: either recovery.signal is missing and the archive is never consulted, or it is present with no recovery_target_* beside it and replay runs to the end of the archive. Both report success.
recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00 is the primary evidence of where recovery stopped, and last completed transaction was at log time 2026-08-28 13:34:38.041366+00 shows the effective point was 2.036196 seconds earlier than the target, because replay stops on commit boundaries.
consistent recovery state reached at 0/3000120 marks the end of the base backup’s WAL range, not the target; it is where the copy stops being torn and where read-only connections opened.
recovery_target_action defaults to pause, so a recovery that reaches its target holds there, read-only, until it is resumed or promoted. This recovery promoted at the target instead of holding, which is what setting the value to promote produces and the reason the log ends with database system is ready to accept connections.
selected new timeline ID: 2 at LSN 0/52EBC90 means the recovered instance forked rather than continued. Its segments are named 00000002..., both histories now live in the archive, and retention has to be decided for each.

Cross-course references

PostgreSQL for Production Sysadmins — Part XIII (Backup, Archiving and Point-in-Time Recovery) is the database administrator’s treatment of this same operation, including the pgBackRest and pg_basebackup options this lesson passes over; the recovery_target_action boot value carried forward here is the fact that course’s audit found stated wrongly in six places, so read the two together and trust the log over any summary of it.
Linux for Production Sysadmins — Part XXIV (Time Synchronisation) is a direct dependency of everything above, because recovery_target_time is matched against timestamps written into commit records by the server’s clock: a host whose time drifted during the incident produces a recovery that stops somewhere other than where the operator believes, with no error anywhere.
Observability for Production Sysadmins — Part XXXI (Logging Foundations) covers the shipping and retention that decide whether the recovery log quoted in this lesson still exists when someone asks how the target was chosen; a recovery whose evidence lives only in a directory that was later discarded cannot be reviewed, only re-asserted.

What the archive was asked to undo

Four decisions, then a start command

restored log file, then recovery stopping before commit

selected new timeline ID: 2

50000 rows and sum(amount)=825025000

What to take from this

Cross-course references

Quiz

`restored log file`, then `recovery stopping before commit`

`selected new timeline ID: 2`

50000 rows and `sum(amount)=825025000`