Runbook: Perform a 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 recover to, and a continuous WAL archive from that backup forward
• The recovery target, taken from the database server log in UTC, with an explicit offset — not from an application log in local time
• A target host separate from production, with a matching PostgreSQL major version and room for the whole cluster
• A decision, recorded, about what happens to the data written after the recovery target: it will not exist in the recovered cluster
• A restore_command tested by hand on the target host before recovery begins
• Agreement on who authorises the promotion, because promotion is the irreversible step

2 · Pre-checks

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

• · Establish the target from the database's own record. The server log, pg_stat_activity if you caught the event live, or the transaction's commit time. An application log records local time; PostgreSQL records UTC, and the difference is the most common way a recovery misses its target.
• · Confirm the base backup predates the target. cat <backup>/backup_label gives the backup start time and the WAL segment recovery begins from. A target earlier than the backup cannot be reached, and the attempt fails with recovery ended before configured recovery target was reached.
• · Confirm the archive covers the target. ls /archive | sort | tail and SELECT last_archived_wal, last_archived_time FROM pg_stat_archiver; on the source. A gap between the backup and the target stops replay at the gap.
• · **Test the restore_command by hand** as the postgres OS user on the target host, against a segment you know exists.
• · Decide, and write down, what is being discarded. Everything committed after the recovery target will not exist in the recovered cluster. That is the point of the exercise and it needs to be stated explicitly, with a name against it.
• · Confirm you are not recovering over production. If the intent is eventually to replace production, do the recovery on a separate host first and cut over deliberately.
• · Note the time. Restoring the base backup alone can take a long time — measured at fifty minutes in one estate — and each wrong target costs that time again.

3 · Procedure

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

1. 1Restore the base backup into the target data directory, with correct ownership and mode 0700.
2. 2Write the recovery configuration, and set the target action explicitly: restore_command = 'cp /archive/%f %p', recovery_target_time = '2026-08-28 14:06:00+00', recovery_target_action = 'pause'.
3. 3**Set recovery_target_action = 'pause' without exception, and do not rely on the default.** Measured on 18.6 the default is pause, so this is not defence against the default — it is defence against a template that sets promote, against hot_standby being off (where pause degrades to shutdown), and against the reader who believes the widely-repeated claim that the default is promote. Setting it explicitly costs one line and removes the question.
4. 4Choose a target slightly before the event, not exactly at it. Continuing a paused recovery to a later target is a restart; going earlier means restoring from scratch. Undershooting is cheap and overshooting is not.
5. 5**Set archive_mode = off** so the recovered cluster does not write into the source archive.
6. 6**Create recovery.signal.** touch <datadir>/recovery.signal. Without it this is a crash restart, not a recovery.
7. 7Start the cluster and read the log, not the exit status of the start command.
8. 8Confirm recovery began with the target you set. LOG: starting point-in-time recovery to 2026-08-28 06:04:03.779666+00. If that timestamp is not what you intended, stop now — before replay has gone anywhere.
9. 9Watch for the stopping point. LOG: recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324+00 names the exact transaction replay stopped at, followed by LOG: pausing at the end of recovery.
10. 10Inspect while paused, with every option still open. SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(), pg_last_wal_replay_lsn(); should report t, paused, and the LSN. The cluster is read-only and you can query anything.
11. 11Check the data against what you expected. The rows that should exist, the rows that should not, the objects a DROP removed. In a measured recovery, the dropped table was present again and 5,000 deleted rows were back, giving 15,001 rows against the 10,000 that existed at backup time.
12. 12**If the target was wrong and you need to go later, change recovery_target_time and restart the server. Replay continues forward from where it is. To go earlier**, restore the base backup again; replay cannot run backwards.
13. 13Promote only when the data has been checked and somebody has authorised it. SELECT pg_promote();. This ends recovery, selects a new timeline and makes the cluster writable, and it cannot be undone.
14. 14Record everything: the target, the transaction replay stopped at, what was verified, who authorised the promotion, and every timing.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓The log shows starting point-in-time recovery to <target> with the timestamp you intended, in UTC.
• ✓The log shows restored log file ... from archive lines, proving the archive was read rather than only the backup's own WAL.
• ✓The log shows consistent recovery state reached at <LSN> and then recovery stopping before commit of transaction N and pausing at the end of recovery.
• ✓While paused, pg_is_in_recovery() is true and pg_get_wal_replay_pause_state() is paused.
• ✓The data at the pause point contains what should exist and does not contain what should not, checked by query rather than by assumption.
• ✓After promotion, pg_is_in_recovery() returns false, the log shows selected new timeline ID and archive recovery complete, and a write succeeds.
• ✓The new timeline history file names the recovery target — for example before 2026-08-27 21:22:51.345091+00 — which distinguishes this cluster's history from a plain promotion, whose history file reads no recovery target specified.
• ✓The total elapsed time is recorded, broken into base backup restore, replay, and verification.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶While paused, everything is reversible. That is the entire reason for recovery_target_action = 'pause', and it is why the promotion is the last step and requires authorisation.
• ↶To move the target later, edit recovery_target_time and restart the server. Recovery continues 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. There is no way to unwind replay.
• ↶After promotion there is no rollback within this cluster. The remedy is another recovery from the base backup, and it costs the whole procedure again.
• ↶If the recovered cluster was started with archive_mode on and has written into the source archive, stop it immediately and treat the archive as suspect. Conflicting timeline histories damage every future recovery, not just this one.
• ↶If an application was pointed at the recovered cluster before verification and has written to it, those writes are on a new timeline. Decide deliberately whether they survive; they cannot be merged back into anything.
• ↶Keep the original cluster and the base backup until the recovered cluster has been verified and has been serving for long enough to trust.

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 beyond the end of the archive, or it is before the base backup. No flag makes a recovery reach a moment earlier than the backup it starts from.
• · The archive has a gap before the target: escalate to whoever owns the archive. The recovery can only reach the gap, and that is a capability limit rather than a procedural mistake.
• · The data at the pause point is not what anybody expected and the target cannot be improved: escalate to the data owner before promoting. Promotion converts an uncertain recovery into a permanent one.
• · The recovery is on the critical path of an outage and is exceeding the recovery time objective: escalate to the incident owner with the measured remaining work. Partial service on stale data is somebody else's decision to make.
• · The event being recovered from is a deliberate destructive act rather than an accident: escalate to security before recovering. The recovery destroys evidence about what else happened, and a copy should 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 it.

References

1. PostgreSQL 18 documentation, Continuous Archiving and Point-in-Time Recovery
2. PostgreSQL 18 documentation, Recovery Target Settings
3. PostgreSQL 18 documentation, Recovery Control Functions
4. PostgreSQL 18 documentation, Timelines