08 - PostgreSQL Restore and Recovery

13 min read

Restore and Recovery

Restore is where a PostgreSQL backup design stops being theory. The SQL Server source note restores one database name through a chain of NORECOVERY and RECOVERY steps. PostgreSQL solves the same operational problem differently: physical restore is cluster-wide, recovery state is controlled by recovery.signal plus recovery_target_* settings, and side-by-side validation inside the same running cluster is usually a logical restore with pg_restore.

Restore decision path

PostgreSQL restore decisions split first by scope. If the operator needs one database name preserved inside the current cluster, the answer is usually logical restore. If the operator needs physical crash recovery or PITR, the answer is a separate cluster restored from a base backup plus WAL.


flowchart LR
  START["Restore needed"] --> SCOPE{"Need one database<br/>inside current cluster?"}
  SCOPE -->|Yes| LOGICAL["Create new database<br/>pg_restore into it"]
  SCOPE -->|No| PHYS["Start separate cluster<br/>from base backup"]
  PHYS --> PITR{"Need a specific<br/>time target?"}
  PITR -->|No| FULL["Start to end-of-backup<br/>and promote"]
  PITR -->|Yes| TARGET["Add recovery.signal<br/>restore_command<br/>recovery_target_time"]
  TARGET --> ACTION{"Inspect before<br/>promotion?"}
  ACTION -->|Yes| PAUSE["recovery_target_action = pause"]
  ACTION -->|No| PROMOTE["recovery_target_action = promote"]
  LOGICAL --> VALIDATE["Validate counts,<br/>schemas, privileges"]
  FULL --> VALIDATE
  PAUSE --> VALIDATE
  PROMOTE --> VALIDATE

Restore Fundamentals

PostgreSQL | recovery.signal / recovery_target_* | recovery state semantics

Understand how PostgreSQL decides whether more WAL should be applied

Use this material at the start of every physical restore. The operational goal is to choose whether the cluster should stop at the end of the backup, continue replaying WAL to the end of the archive, or stop at a precise recovery target for inspection.

PostgreSQL state choiceWhat it doesOperational use
Start from a base backup with no recovery.signalCompletes backup recovery to the end-of-backup WAL and opens normallyFull-cluster restore to the backup timestamp
Start with recovery.signal and valid restore_commandReplays archived WAL beyond the base backupPITR or continuous recovery
recovery_target_action = 'pause'Stops at the chosen target and keeps the cluster read-only in recoveryInspect recovered state before promotion
recovery_target_action = 'promote'Stops at the target and promotes automaticallyFast cutover when inspection is not needed

PostgreSQL has no per-step "final RECOVERY" command

In SQL Server, the dangerous mistake is applying WITH RECOVERY too early. In PostgreSQL, the equivalent mistake is starting a restored cluster without the correct recovery.signal, restore_command, or recovery_target_* settings. The error moves from the command text to the startup configuration.

Pause is the closest analogue to an inspectable final step

recovery_target_action = 'pause' gives the operator a read-only checkpoint at the chosen target, which is operationally similar to bringing a restore online for validation before committing to the final cutover.

The PITR restore in this note was configured with these exact recovery settings:

restore_command = 'cp /tmp/note08/archive/%f %p'
recovery_target_time = '2026-04-18 23:21:22.500000+00'
recovery_target_action = 'pause'
recovery_target_timeline = 'current'

The target time was chosen between these two transactions captured on the primary:

idlabelinserted_at
2after_backup_keep2026-04-18 23:21:21.804624+00
3after_backup_discard2026-04-18 23:21:23.008614+00

PostgreSQL | restore logs | the restore audit trail

Use server logs as the authoritative record of what recovery did

PostgreSQL does not maintain an msdb.dbo.restorehistory catalogue. The authoritative restore audit trail is the restore log itself plus the operator runbook that describes which base backup and WAL source were used.

2026-04-18 23:24:17.027 UTC [416] LOG:  starting point-in-time recovery to 2026-04-18 23:21:22.5+00
2026-04-18 23:24:17.054 UTC [416] LOG:  restored log file "000000010000000000000007" from archive
2026-04-18 23:24:17.073 UTC [416] LOG:  restored log file "000000010000000000000008" from archive
2026-04-18 23:24:17.081 UTC [416] LOG:  recovery stopping before commit of transaction 919, time 2026-04-18 23:21:23.008651+00
2026-04-18 23:24:17.081 UTC [416] LOG:  pausing at the end of recovery
2026-04-18 23:24:17.081 UTC [416] HINT:  Execute pg_wal_replay_resume() to promote.

These lines answer the same questions restorehistory answers in SQL Server:

QuestionEvidence in PostgreSQL
Which base backup was used?backup_label, startup LSNs, and the operator’s artifact path
Which WAL files were applied?restore log lines such as restored log file ... from archive
Where did replay stop?recovery stopping before commit ... time ...
Is the cluster still recoverable forward?pg_is_in_recovery() and whether replay is paused or promoted

Side-By-Side Restore

PostgreSQL | pg_restore | restore a database under a new validation name

Create and populate stoxx_restore_check

This is the PostgreSQL side-by-side validation pattern: create a disposable database name, restore the custom archive into it, and compare counts or schemas before deciding whether the artifact is trustworthy.

DROP DATABASE IF EXISTS stoxx_restore_check WITH (FORCE);
CREATE DATABASE stoxx_restore_check TEMPLATE template0;
DROP DATABASE
CREATE DATABASE
pg_restore -U postgres -d stoxx_restore_check /tmp/note07/stoxx_note07.dump

pg_restore completed without errors, so validation moved immediately to state inspection:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname IN ('stoxx', 'stoxx_restore_check')
ORDER BY datname;
datnamesize
stoxx45 MB
stoxx_restore_check44 MB
SELECT COUNT(*) AS eurostoxx50_rows
FROM silver.eurostoxx50_ohlcv;
databaseeurostoxx50_rows
stoxx67155
stoxx_restore_check67155

The restored database is close in size to the source and contains the same row count in a representative fact table. That is the minimum validation bar before calling the logical restore usable.

Point-In-Time Recovery

PostgreSQL | pg_receivewal | capture WAL after the base backup

Hold the post-backup change stream outside the primary

pg_receivewal is not the only way to provide WAL for PITR, but it is a useful lab analogue to an external archive because it works without reconfiguring the primary cluster.

pg_receivewal: starting log streaming at 0/6000000 (timeline 1)
SELECT pid, application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, sync_state
FROM pg_stat_replication
ORDER BY pid;
pidapplication_nameclient_addrstatesent_lsnwrite_lsnflush_lsnsync_state
2170pg_receivewalstreaming0/6030C200/6000000async

The replication view confirms that WAL is leaving the primary while the PITR chain is being built.

PostgreSQL | pg_basebackup and timed writes | define the recovery target boundary

Capture the baseline and the transactions that will be included or excluded

The base backup for the PITR demo started here:

START WAL LOCATION: 0/7000028 (file 000000010000000000000007)
CHECKPOINT LOCATION: 0/7000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-04-18 23:21:12 UTC
LABEL: note08_pitr_base
START TIMELINE: 1

The two post-backup transactions were then written with deliberately separated timestamps:

INSERT INTO demo_stc.note08_recovery_demo (id, label)
VALUES (2, 'after_backup_keep')
RETURNING id, label, inserted_at;
 
SELECT pg_sleep(1.2);
 
INSERT INTO demo_stc.note08_recovery_demo (id, label)
VALUES (3, 'after_backup_discard')
RETURNING id, label, inserted_at;
idlabelinserted_at
2after_backup_keep2026-04-18 23:21:21.804624+00
3after_backup_discard2026-04-18 23:21:23.008614+00

The source database after both writes contained all three rows:

idlabelinserted_at
1before_backup2026-04-18 23:20:53.667264+00
2after_backup_keep2026-04-18 23:21:21.804624+00
3after_backup_discard2026-04-18 23:21:23.008614+00

PostgreSQL | recovery_target_time | replay to the exact stop point

Restore the base backup and stop before the final transaction commits

To make the WAL segments recoverable, the primary forced segment completion:

SELECT pg_current_wal_lsn() AS before_switch;
SELECT pg_switch_wal() AS switched_1;
SELECT pg_switch_wal() AS switched_2;
SELECT pg_current_wal_lsn() AS after_switch;
stepvalue
before_switch0/80002D8
switched_10/80002F0
switched_20/9000000
after_switch0/9000000

The receiver then finished the relevant archive segments:

pg_receivewal: finished segment at 0/7000000 (timeline 1)
pg_receivewal: finished segment at 0/8000000 (timeline 1)
pg_receivewal: finished segment at 0/9000000 (timeline 1)
000000010000000000000006
000000010000000000000007
000000010000000000000008
000000010000000000000009.partial

The partial 000000010000000000000009.partial file is expected because the current segment was still open when capture stopped. The PITR target does not need it, because replay stops before the commit recorded in segment 000000010000000000000009.

The restored PITR cluster then started with recovery.signal and the recovery settings captured earlier. Its startup log proves the replay boundary:

2026-04-18 23:24:17.027 UTC [416] LOG:  starting point-in-time recovery to 2026-04-18 23:21:22.5+00
2026-04-18 23:24:17.054 UTC [416] LOG:  restored log file "000000010000000000000007" from archive
2026-04-18 23:24:17.073 UTC [416] LOG:  restored log file "000000010000000000000008" from archive
2026-04-18 23:24:17.081 UTC [416] LOG:  recovery stopping before commit of transaction 919, time 2026-04-18 23:21:23.008651+00
2026-04-18 23:24:17.081 UTC [416] LOG:  pausing at the end of recovery

Validation on the restored cluster shows that PostgreSQL is still in recovery and that only rows 1 and 2 survived the replay target:

SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_wal_replay_lsn() AS replay_lsn,
       pg_last_xact_replay_timestamp() AS replay_ts;
 
SELECT id, label, inserted_at
FROM demo_stc.note08_recovery_demo
ORDER BY id;
in_recoveryreplay_lsnreplay_ts
t0/80002782026-04-18 23:21:21.804704+00
idlabelinserted_at
1before_backup2026-04-18 23:20:53.667264+00
2after_backup_keep2026-04-18 23:21:21.804624+00

That is the PostgreSQL PITR proof: the target time includes the wanted transaction and excludes the later one.

PITR failure modes to watch

  • missing required WAL file: recovery halts because restore_command cannot return the next segment
  • target later than retained WAL: the cluster can recover only to the end of the retained archive, not to the requested timestamp
  • restore started without recovery.signal: the cluster opens at end-of-backup and never attempts PITR

Restore From Object Storage

PostgreSQL | packaged base backup | restore after download

Extract the archived artifact and boot it on a different port

The packaged artifact created in note 07 was unpacked under /tmp/note08/from_object. After extraction, the top-level directory required a PostgreSQL-compatible permission fix from 0755 to 0700 before startup would succeed.

tar -xzf /tmp/note07/basebackup-20260418.tar.gz -C /tmp/note08/from_object
ls -lh /tmp/note08/from_object/basebackup/backup_label
-rw------- 1 postgres postgres 217 Apr 18 23:12 /tmp/note08/from_object/basebackup/backup_label

The first startup attempt failed for the exact reason PostgreSQL documents for physical data directories:

FATAL:  data directory "/tmp/note08/from_object/basebackup" has invalid permissions
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

After chmod 700 /tmp/note08/from_object/basebackup, the extracted cluster started cleanly on port 5546:

waiting for server to start.... done
server started
/tmp/note08/run/full:5546 - accepting connections
2026-04-18 23:23:39.976 UTC [321] LOG:  starting backup recovery with redo LSN 0/5000028, checkpoint LSN 0/5000060, on timeline ID 1
2026-04-18 23:23:39.980 UTC [321] LOG:  completed backup recovery with redo LSN 0/5000028 and end LSN 0/5000100
2026-04-18 23:23:40.005 UTC [318] LOG:  database system is ready to accept connections
SELECT pg_is_in_recovery() AS in_recovery,
       pg_postmaster_start_time() AS started_at;
in_recoverystarted_at
f2026-04-18 23:23:39.756278+00
SELECT datname
FROM pg_database
ORDER BY datname;
datname
postgres
stoxx
template0
template1

The important boundary is operational, not syntactic: object storage changes how the artifact is transported, not how PostgreSQL restores it after download.

Tail-WAL Capture and Disaster Recovery

PostgreSQL | no direct tail-log backup | capture the last completed WAL safely

Use WAL switch plus external capture when the primary is still reachable

The lab used pg_switch_wal() followed by pg_receivewal segment completion as the disaster-recovery equivalent of “capture the tail before restore”:

SignalEvidence
WAL switch forcedbefore_switch = 0/80002D8, switched_1 = 0/80002F0, switched_2 = 0/9000000
Receiver finished archiveable segments000000010000000000000006, 000000010000000000000007, 000000010000000000000008
Current open segment remained partial000000010000000000000009.partial

This maps to disaster recovery in PostgreSQL as follows:

SQL Server conceptPostgreSQL analogue
Tail-log backup before replaceForce WAL switch and confirm final completed segment is archived or streamed out
Log-backup file chainArchived WAL segment chain
Damaged source unavailable before final captureUnarchived WAL in the current segment may be lost

If the server is still alive, a controlled shutdown and verified WAL capture materially improve recovery completeness. If it is already gone, PostgreSQL can recover only as far as the last durable WAL segment already outside the server.

Recovery Monitoring

PostgreSQL | pg_stat_activity / replay functions | observe recovery state

Confirm whether the restored cluster is still replaying or already promoted

The paused PITR cluster exposes its state through both catalog views and replay functions:

SELECT pid, backend_type, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type IN ('startup', 'client backend')
ORDER BY backend_type, pid;
pidbackend_typestatewait_event_typewait_event
502client backendactiveIODataFileRead
503client backendactive
416startupIPCRecoveryPause
SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_wal_replay_lsn() AS replay_lsn,
       pg_last_xact_replay_timestamp() AS replay_ts;
in_recoveryreplay_lsnreplay_ts
t0/80002782026-04-18 23:21:21.804704+00

The full restore from the packaged base backup shows the opposite state:

restore targetin_recoverymeaning
PITR cluster on 5547tpaused at the requested recovery target
full cluster on 5546fend-of-backup recovery completed and cluster promoted

PostgreSQL | restore drill checklist | prove the restore path monthly

Run the PostgreSQL equivalent of a restore drill

StepProof captured in this note
Restore a logical archive into a disposable namestoxx_restore_check restored and validated
Start a physical restore target from a base backuppackaged base backup started on port 5546
Replay WAL to a chosen timePITR cluster stopped before transaction 919 at 23:21:23.008651+00
Verify target data stateonly rows 1 and 2 survived on the PITR cluster
Confirm monitoring surfacespg_stat_activity, restore logs, and replay functions all matched the expected state

Next: 09-postgresql-high-availability-overview expands from backup-and-restore into continuous availability patterns, replica roles, and failover tradeoffs.

Graph View

Backlinks