PostgreSQL Point-in-Time Recovery Readiness Review

How to use this review

Quarterly, and it requires an actual recovery. Reading the configuration establishes that a PITR is possible; performing one establishes how long it takes and whether the archive has a gap in it.

A point-in-time recovery is bounded at one end by a base backup and at the other by the last archived segment. Everything in this review is either about those two boundaries or about the procedure between them.

The setting that costs fifty minutes per wrong guess

The default is pause — and a recovery still promoted past the mistake

recovery_target_action does not default to promote, though the claim is widely repeated. Measured on 18.6, boot_val is pause, and a recovery that reaches its target with the setting absent stops there:

LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

What actually ends a recovery early is a target that is never reached — one written in local time against an archive recorded in UTC, say. Recovery replays to the end of the WAL, finds nothing more to apply, and promotes onto a new timeline. No action setting applies, because there was no arrival. The tell is the absence of a recovery stopping before line in the log.

By the time anybody can look at the data, the cluster has branched and the restored directory cannot be reused for a different target.

recovery_target_action = 'pause'

With pause, recovery stops at the target and holds. The cluster is read-only, pg_is_in_recovery() is true, and every option is still open: adjust the target and restart to go later, or restore the backup again to go earlier.

The setting is free. In one estate its absence cost three base-backup restores of fifty minutes each, inside a four-hour incident.

The timestamp, in UTC, with the offset written

recovery_target_time = '2026-08-28 14:06:00+00'

The most common way a recovery misses is a target copied from an application log that records local time. PostgreSQL records UTC and interprets the value in the session’s time zone; an hour of difference replays straight past the event and reproduces it faithfully.

Take the target from the server log.

What the log tells you at the target

LOG:  starting point-in-time recovery to 2026-08-28 06:04:03.779666+00
LOG:  consistent recovery state reached at 0/39000120
LOG:  recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324+00
LOG:  pausing at the end of recovery

recovery stopping before commit of transaction N names the exact transaction. Record it. It is a far stronger statement about where you landed than the target you requested.

Three ways to make a recovery quietly wrong

Omission	What happens	Visible?
No `recovery.signal`	Crash restart; `restore_command` never consulted; comes up far earlier	No error at all
`restore_command` fails	Only the backup’s own WAL is replayed	No error; consistent and early
Archive gap before the target	Replay stops at the gap	No error; consistent and early

All three produce a cluster that reaches consistent recovery state, completes, and is earlier than intended. Consistent is not the same as correct, and checking the data is the only thing that distinguishes them.

Record the timings

Base backup restore, replay, verification, total. That total is the recovery time objective the estate has.

The alternative is discovering it during an incident, one fifty-minute restore at a time, while somebody asks how much longer.

Where the numbers come from

The recoverable window comes from the oldest base backup that still has an unbroken WAL chain behind it, established by listing both — not from the retention policy, which is a statement of intent. The archive’s health comes from pg_stat_archiver. The recovery point actually achieved comes from the recovered cluster’s own data, compared against what the source held at the target time.

The recovery duration is measured end to end, including the time to find and stage the base backup, which is routinely omitted and is routinely the largest part.

Access this needs

Read access to the WAL archive, to its retention configuration, and to the base backups it belongs with — and a non-production host with capacity to run the recovery.

A role holding pg_monitor on the source cluster for pg_stat_archiver and the WAL positions, plus the operating-system account the restore_command runs as, because that command must be tested by hand, as that user, on the host a recovery would actually use.

Nothing here requires write access to the archive. A reviewer who needs it to complete an item has found a defect in the archive’s permissions.

What the review produces

A dated record naming the reviewer, the target chosen, the target actually reached, and the measured duration. Attach the recovery log’s recovery stopping before ... line, the timeline history file the promotion produced, and the verification performed while the server was still paused.

The gap between the achieved recovery point and the objective is the headline number. It goes to the service owner whether or not it is acceptable, because it is the only honest input to that conversation.

Sign-off

Reviewer: ________________ Date: ___________
Database owner: ___________ Date: ___________
Service owner: ____________ Date: ___________

Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint: record the date, the reviewer, the disposition of every item that did not pass, and the name of whoever accepted the residual risk.