Execute a PostgreSQL point-in-time recovery

1 · Prerequisites

Confirm every item is in place before any state change.

• A base backup taken before the moment you need to return to. In the measured capture it was produced by pg_basebackup -D /work/base -X stream -c fast and contained 45000 rows.
• A WAL archive that runs continuously from that base backup forward. The recovery reads the archive, not the damaged cluster, and that is what makes it independent of the machine that failed.
• A target time in UTC with an explicit offset, derived from the incident timeline. The database recorded the damage faithfully; it cannot tell you when the damage began.
• An invariant recorded before the incident that the recovered data must reproduce — a row count and a checksum over a business column. In the capture these were 50000 rows and sum(amount)=825025000.
• Somewhere to recover that is not the damaged cluster: a separate directory or host, the same PostgreSQL major version, and room for the whole data directory. In the capture the recovered instance listened on port 5434, which its own log records, and nothing was written over the source data directory.
• A restore_command that has already been run by hand, as the postgres OS user, against a segment you know is in the archive.
• A named person who authorises promotion, and written agreement that everything committed after the target will not exist in the recovered cluster.

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Fix the recovery point from the incident timeline, not from the latest available data. "Restore the latest" is the instruction that reapplies the damage. The capture recorded recovery target time : 2026-08-28 13:34:40.077562+00 before the DELETE ran, and that timestamp came from outside the database. Write it in UTC with an explicit offset; a target without one depends on a timezone setting nobody checked.
• · Confirm the base backup predates the target. Read backup_label in the restored directory for the backup start time and the segment replay begins from. No setting makes a recovery reach a moment earlier than the backup it starts from.
• · Confirm the archive is continuous from the base backup to the target BEFORE starting. SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver; on the source, then list the archive and check the segment names form an unbroken run. A gap between the backup and the target means the target is unreachable, and you will discover that only after the restore has cost you its full duration.
• · Understand what a gap does, because it does not look like a failure. Replay asks restore_command for each segment by name; a non-zero exit is the documented way to say the file is not there, and that answer ends recovery rather than failing it. A gap therefore produces a shorter recovery that reports success.
• · **Confirm archive_mode will be off on the recovered copy.** The copy carries the source cluster's configuration verbatim, archive_mode and archive_command included. Left on, the recovered instance writes its own timeline into the archive that production depends on.
• · Confirm you are recovering beside the damaged cluster and never over it. Recovering over production destroys the evidence and the fallback in one step, and it removes the option of comparing the two.
• · Record the invariant you will check afterwards, and who supplied it. A row count and a checksum over a business column, agreed with the data owner before the recovery starts. An invariant chosen after seeing the recovered data proves nothing.
• · Budget the time. Restoring the base backup is the expensive stage and each wrong target costs it again. Note the start time so the elapsed cost of a second attempt is a measured number rather than an argument.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1**Restore the base backup into a new directory owned by the postgres user with mode 0700.** Never unpack it over the damaged data directory: while the original cluster survives, every wrong decision below is reversible.
2. 2Set the port to something production is not using. The capture's recovered instance listened on 5434, which its log records. A distinct port is what lets the copy and the original exist at the same time, and comparing the two is the only cheap way to tell what the recovery actually changed.
3. 3**Set archive_mode = off in the recovered copy's configuration, as a separate deliberate line.** This is the step a runbook forgets, and its failure mode is delayed: the copy writes 00000002 segments and a history file into the shared archive, and the next recovery from that archive discovers a branch production never took.
4. 4**Set restore_command to read the archive, and nothing else.** restore_command = 'cp /srv/rbdr-wal-archive/%f %p' for a local directory; an object-storage client or a pgBackRest invocation elsewhere. It must not be able to write.
5. 5**Set recovery_target_time to the target you fixed in the pre-checks**, quoted, in UTC, with the offset written out.
6. 6**Set recovery_target_action explicitly, and default to pause.** Its documented boot value is pause, so this is not defence against the boot value — it is defence against a template that carries promote, and it makes the intent readable to whoever reviews the recovery later.
7. 7**Create recovery.signal in the restored directory.** Without it the three recovery settings are not consulted at all, and what starts is an ordinary crash restart that replays everything it can find, damage included, and reports success.
8. 8Start the server and read the log, not the exit status of the start command. The start command succeeded in the capture's first part too, on a copy that proved nothing.
9. 9Confirm the log echoes your target back. starting point-in-time recovery to 2026-08-28 13:34:40.077562+00 is the line that proves the settings were read. If it is absent, stop and fix the configuration before replay goes anywhere.
10. 10**Count the restored log file ... from archive lines against the segments you expected.** These are the archive's continuity being demonstrated rather than assumed, and it is the cheapest check available while the recovery is running.
11. 11Find the stopping line and record it verbatim. recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00 is the strongest available statement of where replay landed, and last completed transaction was at log time ... gives the other bracket.
12. 12**Note selected new timeline ID: 2 and what it commits you to.** The recovered instance forked rather than continued; its segments are named 00000002..., the archive now holds two histories, and retention has to be decided for each one separately.
13. 13Check the business invariant while the decision is still open, then promote only when the data has been checked and the named authoriser has said so.
14. 14Take a fresh base backup on the new timeline as soon as the recovered instance is accepted, and let that backup — not the promotion — be the event that permits any pruning of what came before it.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓The log contains starting point-in-time recovery to <the timestamp you configured>, in UTC. Absence of this line means the target was never read.
• ✓The log contains one restored log file "..." from archive line per segment between the base backup and the target, and their names form an unbroken sequence.
• ✓The log contains consistent recovery state reached at <LSN>, which marks the end of the base backup's own WAL range — the point at which the file copy stops being torn, not the target.
• ✓The log contains recovery stopping before commit of transaction N, time <timestamp>, and that timestamp is at or after the target. A recovery with no stopping line ran past the target to the end of the archive.
• ✓The log contains selected new timeline ID: N and archive recovery complete, and the number is one the archive did not already hold.
• ✓The archive file count is identical before and after the recovery, proving archive_mode = off held and the copy wrote nothing into it.
• ✓The recovered database reproduces the invariant recorded before the incident — row count and business checksum both — checked by query rather than by inspection.
• ✓The two independent lines of evidence agree: the log says replay stopped between two named commit timestamps, and the data holds exactly what existed in that window. Disagreement means the recorded expectation was wrong, and you have learned that before telling anyone the recovery is finished.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Before promotion, everything is reversible, and that is the whole reason recovery_target_action is set to pause. Nothing below costs more than time.
• ↶To move the target later: edit recovery_target_time and restart the server. Replay continues forward from where it paused, which costs seconds rather than another base backup restore.
• ↶To move the target earlier: restore the base backup again and start over. Replay cannot run backwards, and no setting makes it.
• ↶After promotion there is no rollback inside this cluster. The remedy is a second recovery from the base backup, at the full cost of the procedure.
• ↶If the recovered copy was started with archive_mode left on, stop it immediately, list what it added to the archive, and quarantine those files with the archive owner before any further recovery reads them. A stray 00000002.history tells every later recovery that a branch exists which production never took.
• ↶Undo the reflex mutations as well as the deliberate ones: alert silences opened during the incident, monitoring checks pointed at the recovery port, connection strings edited to test the copy, and any restore_command left in a shared configuration file.
• ↶Keep the damaged cluster and the base backup until the recovered instance has been verified and has served long enough to trust. Deleting either one converts a recoverable mistake into a permanent one.

6 · Escalation

When the runbook isn't enough, contact:

• · FATAL: recovery ended before configured recovery target was reached: escalate and check both ends. Either the target is past the end of the archive, or it is before the base backup. Neither is fixed by retrying.
• · The archive has a gap between the base backup and the target: escalate to the archive owner. The recovery can only reach the gap, and that is a capability limit of the estate rather than a mistake in this procedure.
• · The data at the pause point is not what anyone expected and no better target can be derived: escalate to the data owner before promoting. Promotion converts an uncertain recovery into a permanent one.
• · The recovered copy has written into the shared archive: escalate to the archive owner and to whoever holds the next recovery, because the damage is to future recoveries rather than to this one.
• · The recovery is on the critical path of an outage and is exceeding the agreed recovery time objective: escalate to the incident owner with the measured remaining work. Serving partial or stale data is somebody else's decision.
• · The event being recovered from looks deliberate rather than accidental: escalate to security before recovering. The recovery discards the evidence of what else the actor did, and a copy has to be preserved first.
• · Nobody can authorise the promotion and the paused cluster is holding resources: escalate rather than promoting to tidy up. A paused recovery is a safe state and can be left in one.

References

1. PostgreSQL 18 documentation, Continuous Archiving and Point-in-Time Recovery (PITR)
2. PostgreSQL 18 documentation, Write Ahead Log and Recovery Target configuration
3. PostgreSQL 18 documentation, Recovery Control Functions
4. PostgreSQL 18 documentation, pg_basebackup