critical riskdata loss risk~60 min

Runbook: Restore a Cluster From a Physical Backup

1 · Prerequisites

Confirm every item is in place before any state change.

The base backup, and the WAL archive covering the period from the backup to the point you intend to reach
A target host with enough storage for the whole cluster, and a PostgreSQL major version matching the backup exactly
Confirmation of what is being restored and why: a lost primary, a verification exercise, or a copy for another purpose — because the target being production changes every subsequent step
A restore_command that can read the archive from the target host, tested by hand before recovery starts
Knowledge of whether the original cluster is still running, and whether it must be fenced first
A place to record timings, because the duration of this procedure is the recovery time objective the estate actually has

2 · Pre-checks

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

· Confirm the major version matches. cat <backup>/PG_VERSION against the target's installed version. A physical backup is not portable across major versions, and starting a PostgreSQL 18 binary against a 17 data directory fails immediately and loudly.
· **Confirm backup_label is present in the backup.** Its absence means this is a copy of a data directory rather than a base backup, and recovery has no defined starting point.
· **Read backup_label** and note the checkpoint LSN and the WAL segment recovery will begin from. Everything from that segment onwards must exist in the archive.
· **Test the restore_command by hand.** Run it against a segment you know exists, as the postgres OS user, on the target host. A restore_command that fails at recovery time produces a recovery that stops silently at the last segment it could read.
· Confirm the archive is continuous. ls /archive | sort | tail and check for gaps around the segments recovery will need. A missing segment stops replay at the gap, and the resulting cluster is consistent but earlier than you intended.
· Confirm the target data directory is empty, and that the target has enough space for the cluster plus the WAL it will replay.
· If the original primary may still be running, fence it before starting. Two clusters believing they are primary is a worse outcome than a slow restore, and it is not recoverable without discarding somebody's committed work.
· Record the start time. The elapsed time of this procedure is the number that matters to the service owner.

3 · Procedure

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

1Place the backup in the target data directory with correct ownership and permissions: owned by the postgres OS user, mode 0700. A data directory with looser permissions causes a startup failure with data directory "..." has invalid permissions.
2**Write the recovery configuration into postgresql.auto.conf** (or postgresql.conf): restore_command = 'cp /archive/%f %p'. Use the archive tool your estate actually uses; a bare cp is adequate for reading and inadequate for writing.
3Decide the recovery target explicitly. To replay everything available, set no target — recovery runs to the end of the archive. To stop at a moment, this is a point-in-time recovery and belongs in that runbook, with recovery_target_action = 'pause'.
4**Create recovery.signal in the data directory.** touch <datadir>/recovery.signal. Its presence is what makes the startup an archive recovery rather than a crash restart, and its absence is the most common reason a restore silently becomes something else.
5Disable archiving on the restored cluster before starting it, unless you intend it to write into the same archive: archive_mode = off. A restored cluster archiving into the source's archive will corrupt the timeline history for everybody.
6Start the cluster and immediately begin reading the log. pg_ctl -D <datadir> -l <logfile> start, then follow the log. Do not treat the command's exit status as the result — pg_ctl stops waiting once the postmaster is up, and the startup process can fail afterwards.
7Watch for the consistency milestone. consistent recovery state reached at <LSN> is the point at which the cluster is coherent. Before that it is not, and a cluster that never reaches it is not a restored cluster.
8Watch the segments being restored. restored log file "0000000100000000000000XX" from archive lines confirm the restore_command is working. Their absence, with recovery apparently complete, means recovery replayed only what was inside the backup.
9Watch for the end of recovery. For a restore to the end of the archive, expect redo done at <LSN>, then a new timeline selection, then archive recovery complete, then database system is ready to accept connections.
10Confirm the cluster left recovery. SELECT pg_is_in_recovery(); must return false. This is authoritative; pg_controldata reports the last checkpoint's timeline and legitimately lags behind a fresh promotion.
11Confirm the data is what you expect before anybody points an application at it: row counts on the significant tables, the latest timestamps, and the presence of the objects you expect. A consistent cluster restored to the wrong point is consistent.
12Record the timings: time to copy the backup, time to replay, time to first connection, and the total. That total is the recovery time objective this estate has.

4 · Verification

Confirm the procedure actually fixed the problem.

✓The log contains consistent recovery state reached at <LSN>.
✓The log contains restored log file ... from archive lines, confirming the archive was actually read rather than only the backup's own WAL being replayed.
✓The log contains redo done at <LSN> and archive recovery complete, with no FATAL between the start and those lines.
✓SELECT pg_is_in_recovery(); returns false.
✓SELECT pg_last_wal_replay_lsn(); and the latest data in the significant tables agree with the point you intended to reach.
✓Row counts and maximum timestamps on the significant tables are recorded and compared against what was expected.
✓The timeline history file for the new timeline exists in pg_wal, and its third field records why the timeline diverged — no recovery target specified for a restore to the end of the archive.
✓The total elapsed time is recorded, compared against the recovery time objective, and reported whether or not it met it.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶A restore to a fresh target host changes nothing else, so rollback is to stop the restored cluster and delete the data directory. Do that deliberately rather than leaving a half-restored cluster running.
↶If the recovery stopped short of where you intended, do not attempt to continue by promoting. Fix the restore_command or the archive gap and start the restore again from the base backup; replay cannot be resumed after the cluster has left recovery.
↶If the restored cluster was started with archive_mode on and has written into the source archive, stop it immediately and treat the archive as suspect. Two clusters archiving into one destination produce a timeline history that will confuse every future recovery.
↶If the restore was onto the original host and the original data directory was moved rather than deleted, it is still there — do not delete it until the restored cluster has been verified and has been serving traffic for long enough to trust.
↶If an application was pointed at the restored cluster and the restore turns out to be wrong, stop the application before stopping the cluster. Writes made to a wrongly restored cluster are on a timeline that cannot be merged with anything.
↶Record what was restored, to what point, and what was discarded. A restore is a decision about which data survives, and that decision needs a written record.

6 · Escalation

When the runbook isn't enough, contact:

· The log shows FATAL: recovery ended before configured recovery target was reached: escalate and check the archive. This means replay ran out of WAL before reaching the target — either the archive has a gap, or the target is beyond its end, or it is before the base backup.
· The restore_command fails and no segments are restored: escalate to whoever owns the archive storage. Recovery will complete using only the WAL inside the backup, producing a cluster that is consistent and much earlier than intended.
· The cluster reaches consistency but the data is older than expected: escalate before promoting anything. This is an archive completeness question, and promoting fixes the appearance while making the gap permanent.
· The restore is on the critical path of an outage and the elapsed time is exceeding the recovery time objective: escalate to the incident owner with the measured remaining work, so that they can decide about partial service rather than waiting.
· The original primary is still running and cannot be fenced: escalate immediately. Restoring a second primary while the first accepts writes creates a split brain whose resolution discards one side's committed work.
· The backup's major version does not match any available binary: escalate to the platform owner. A physical backup cannot be restored across major versions, and the answer is the correct binaries rather than any flag.

Restoring a physical backup is four things: put the files down, tell PostgreSQL where the archive is, create recovery.signal, and read the log.

The last one is where the procedure is actually performed. Everything else is preparation.

`recovery.signal` is what makes this a recovery

Without it, PostgreSQL treats the directory as a crashed cluster

A data directory containing backup_label and no recovery.signal is started as a crash restart. PostgreSQL replays only the WAL it finds inside the directory, never consults restore_command, and comes up consistent at a point far earlier than you intended — with no error.

touch /var/lib/postgresql/18/main/recovery.signal

One empty file. Its absence is the most common reason a restore quietly becomes something else.

Do not trust `pg_ctl`’s exit status

The four log lines that matter

LOG:  restored log file "000000010000000000000039" from archive
LOG:  consistent recovery state reached at 0/39000120
LOG:  redo done at 0/3A19A2B8
LOG:  archive recovery complete

Line	What its absence means
`restored log file ... from archive`	The `restore_command` is not working; only the backup’s own WAL was replayed
`consistent recovery state reached`	The cluster is not coherent; this is not a restored cluster
`redo done at`	Recovery is still running, or it failed
`archive recovery complete`	Recovery did not finish; check for a `FATAL` above

Blast radius

Action	Reversible?	What it costs if wrong
Restoring onto a fresh host	Yes — delete the directory	Time
Starting without `recovery.signal`	Yes — start again	A cluster restored to the wrong point, with no error
Starting with `archive_mode` on	No	Two clusters writing one archive; every future recovery is compromised
Restoring while the original primary is live	No	A split brain whose resolution discards committed work
Pointing an application at a wrongly restored cluster	No	Writes on a timeline that cannot be merged with anything

Consistent is not the same as correct

A cluster that reaches consistent recovery state and completes recovery is internally coherent. It says nothing about whether it reached the point you wanted.

Check the data before anybody connects an application:

SELECT pg_is_in_recovery();          -- must be false
SELECT pg_last_wal_replay_lsn();
SELECT max(created_at) FROM orders;  -- and the counts that matter

If the archive had a gap, recovery stopped at the gap, and the result looks exactly like a successful restore.

Write down the time

Copy time, replay time, time to first connection, total. That total is the recovery time objective this estate has, as distinct from the one in the disaster-recovery document — and the two are frequently not the same number.