Restoring PostgreSQL to a recovery target

Which backup can reach the target?

A PostgreSQL database cluster is the set of databases managed by one server instance. Physical archive recovery restores that whole cluster. It does not restore one table into the currently running database, and a copy of arbitrary live files is not a substitute for PostgreSQL’s backup procedure.

A logical dump instead saves definitions and data for reconstruction. It can help restore a database or selected objects, but cannot be advanced using WAL. If a dump contains the 2.20 mistake, adding an earlier WAL file will not undo it. The logical restore near the end follows that separate path.

The WAL bundled with a base backup makes that backup consistent. Reaching a later target also requires the intervening archive. If one required later segment is missing, recovery cannot jump over it to use the segments beyond it.

Even when those later files remain and the base passes verification, the recovery path cannot cross the gap. Another complete backup and its dependencies might provide a different path. That is why retention starts from the states you must recover, rather than the age of individual files.

Archive WAL before taking the backup

PostgreSQL divides WAL into segment files, normally 16 MiB each. With continuous archiving, the server hands completed segments to an archive command or library before recycling them. Enable archiving and test retrieval before taking the base backup.

wal_level = 'replica'
archive_mode = 'on'
archive_command = '/srv/shop-backup/archive-wal "%p" "%f"'

The helper path is a deployment choice: supply and test your archive tool there. %p gives it the source path and %f the original filename. wal_level and archive_mode require a server restart. Check the effective settings after that restart, including whether an archive library is configured instead.

The helper must return success only after the file is safely retained. It must preserve filenames, keep unrelated cluster histories apart, refuse a conflicting existing file, and safely accept a repeated identical file that is already persisted. Test failure as well as success: an unavailable destination or conflicting contents must produce failure. A command that returns zero without storing anything tells PostgreSQL it may recycle your only copy.

Use a protected archive outside the loss you intend to survive. The local directory in this drill tests the mechanics; it does not survive loss of its container’s storage. Record the source system identifier with the archive and backup inventory:

SELECT system_identifier FROM pg_control_system();
SHOW data_directory;
SHOW archive_mode;
SHOW archive_command;

SELECT archived_count, last_archived_wal,
       last_archived_time, failed_count, last_failed_wal
FROM pg_stat_archiver;

Run privileged inspection through the intended administrative connection. A recent last_archived_wal is progress evidence, not proof that every earlier file exists. Inspect the archive, retrieve files and read the server and archive-tool logs too. Some failures that restart the archiver are absent from its failure counter. Repeated archival failure can also leave WAL accumulating on the source until storage runs out.

A local commit can be ahead of the archive

The normal commit promise concerns local durable WAL; it does not wait for completed-segment archiving. A quiet server may leave a recently committed change in an unfinished segment for a long time. archive_timeout can force periodic segment switches when there has been activity, at the cost of archiving full-sized segment files even when little of each is used.

SELECT pg_switch_wal();

A switch makes the previous segment eligible. Wait for its actual archival success and verify retrieval; the returned LSN alone does not prove it arrived. PostgreSQL WAL, checkpoints and durability follows the local flush and restart promise.

Take and verify a base backup

pg_basebackup takes a supported copy while the source runs. Its connection needs replication permission and an appropriate pg_hba.conf entry. Streaming the backup’s WAL needs a second WAL sender as well as the sender copying the files.

Use explicit connection details and a new, empty destination. These command shapes assume a verified administrative shell: SOURCE_SOCKET, SOURCE_PORT, BACKUP_ROLE and the absolute BASE directory name the intended isolated resources. Clear inherited service and connection overrides before setting them; check the source’s identity before copying.

pg_basebackup --host="$SOURCE_SOCKET" --port="$SOURCE_PORT" \
  --username="$BACKUP_ROLE" --pgdata="$BASE" \
  --format=plain --wal-method=stream \
  --manifest-checksums=SHA256

pg_verifybackup "$BASE"

--wal-method=stream includes WAL needed to make this backup usable. It does not subscribe the saved directory to every future change. Keep the base intact, including backup_label, its manifest and any tablespace map. For a cluster with extra tablespaces, arrange separate safe destinations with --tablespace-mapping; plain backups otherwise use those tablespaces’ original absolute paths.

pg_verifybackup checks the manifest, expected files and their checksums, and parses the WAL required for backup consistency. Use matching-major verification tools, including pg_waldump. Success does not check every later archived segment, prove that a chosen target is reachable or establish the shop’s recovered facts. Those need a restore.

An online backup cannot recover a target from halfway through its own copying. Its earliest consistent recovery point is the backup end. Choose an older completed backup if the desired incident boundary is earlier. We deliberately create our drill’s named point only after the backup has completed.

Incremental backups add dependencies

PostgreSQL’s native incremental backup uses WAL summaries to identify changed blocks. Enable summarize_wal and retain the summaries covering the required interval before attempting an increment. Pass the earlier backup’s manifest to pg_basebackup --incremental.

An increment is not a directly startable full cluster. Reconstruct a full backup from its required chain, oldest first:

pg_combinebackup --output="$COMBINED" \
  "$FULL" "$INCREMENT_1" "$INCREMENT_2"
pg_verifybackup "$COMBINED"

Verify the constituent backups as well. pg_combinebackup checks their relationship, not every input file’s integrity. Retain the dependencies until a tested replacement makes them unnecessary. WAL summaries help take increments; they do not replace the archived WAL needed to recover the reconstructed backup to a later target.

Choose the target and timeline

For this controlled drill, create a named restore point on the source after the completed backup, before either later change:

SELECT pg_create_restore_point('before_price_mistake');
-- Keep the returned LSN with the source identity and timeline.

-- Only afterwards, in this independent recovery branch:
UPDATE products SET price = 2.20 WHERE product_id = 'P7';
-- The later valid payment is a separate committed transaction.

The named point is a WAL record, not a saved copy of the database. Retain its containing segment and the path leading to it. Use a unique name for each real marker. You cannot create a marker retrospectively before an unknown mistake.

Configure only one target form. For time, transaction ID and LSN targets, recovery_target_inclusive controls whether the exact target boundary is included; the default is on. It does not apply to a named restore point. Prove the selected cut with recovered facts, whichever form supplied it.

A timeline identifies a branch of WAL history. Completing archive recovery creates a new one, with a small history file recording its parent and branch point. Keep those history files alongside WAL. An LSN alone cannot distinguish different changes at the same position on different branches.

recovery_target_timeline = 'current' follows the timeline current when the base was taken. An explicit timeline ID chooses a particular intended branch. The default, latest, finds the latest timeline in the archive; that may be useful for a standby, but should not silently choose an incident investigation’s history. Record the source system identifier, backup, timeline and target together.

Restore into an isolated cluster

Keep the source and original recovery material intact. Copy the verified backup into a separate, stopped target with compatible PostgreSQL software, extensions and architecture. Restore permissions and tablespace destinations deliberately. Do not let symlinks or replayed tablespace operations lead back to live storage.

Inspect the copied configuration before starting it. Data directories, sockets, ports, archive helpers and external configuration paths may still name the source. Remove source streaming settings and any standby.signal; a standby signal takes precedence over recovery.signal. Exclude ordinary callers through network isolation and authentication, and keep application workers and external dispatch stopped.

In a target that retrieves from a plain local archive, the recovery settings have this shape. /srv/recovery/shop-wal is the selected source’s archive, made readable to the target’s server account:

restore_command = 'cp /srv/recovery/shop-wal/%f %p'
recovery_target_name = 'before_price_mistake'
recovery_target_timeline = 'current'
recovery_target_action = 'pause'
hot_standby = 'on'
primary_conninfo = ''
listen_addresses = ''

The retrieval command must return failure when a requested file is unavailable, rather than creating an empty substitute. Create an empty recovery.signal in the copied data directory. With the target configuration reviewed, start it as its server account. These variables identify the separate target and a pre-created, server-owned socket directory; the example paths contain no spaces:

touch "$RESTORE_DATA/recovery.signal"
pg_ctl --pgdata="$RESTORE_DATA" --log="$RESTORE_LOG" \
  --options="-p $RESTORE_PORT -k $RESTORE_SOCKET" start

Retain the server log and connect only through that target’s explicit socket and port. The source and target must never share a data directory or tablespace files.

Confirm that recovery actually paused

A server accepting a read-only connection may still be replaying. Inspect the state through the target’s verified connection:

SELECT system_identifier FROM pg_control_system();
SHOW data_directory;
SELECT pg_is_in_recovery(),
       pg_get_wal_replay_pause_state(),
       pg_last_wal_replay_lsn();

Expect recovery to be active and the pause state to be paused. Match the log’s named stopping point to the intended marker, source history and timeline. Then read the shop:

SELECT (SELECT price FROM products WHERE product_id = 'P7') AS p7_price,
       o.status,
       (SELECT sum(quantity * unit_price) FROM order_items
        WHERE order_id = o.order_id) AS line_total,
       (SELECT coalesce(sum(amount), 0)::numeric(8,2) FROM payments
        WHERE order_id = o.order_id) AS paid
FROM orders o WHERE order_id = 103;
-- Before the mistake: 20.00 | pending | 29.00 | 0.00

SELECT product_id, stock FROM products ORDER BY product_id;
-- P3 10; P7 0; P9 4
SELECT * FROM payments WHERE payment_id = 504;
-- No rows

The two sums remain separate so payments cannot multiply order lines. Check required objects, other representative rows, constraints and application reads under the intended access identity, with dispatch still disabled. Catalogue price, agreement, stock and payments are different facts; one correct price is not sufficient acceptance evidence.

If the target is too early, keep recovery unfinished: shut down, choose a later supported target and restart to continue. An earlier target needs a fresh restore from the original material. Calling pg_wal_replay_resume() after reaching a pause target finishes recovery; it is not the instruction to inspect the next transaction.

A missing segment must fail the attempt

Test a gap on a separate disposable restore and a copied archive. Withhold a required post-backup segment and choose an explicit target beyond it. Ensure that neither bundled pg_wal nor streaming provides another copy. Leave the original archive unchanged.

Recovery can request absent history or next-segment files during normal discovery, so one failed file lookup alone does not establish failure. The decisive evidence is whether it reached the explicit target. When archive recovery ends first, PostgreSQL reports a fatal error rather than accepting that different stopping point as the requested recovery.

In the PostgreSQL 18.6 drill, the intact archive reached before_price_mistake and paused with the expected shop facts. The gap attempt logged that it had reached consistency and was ready for read-only connections, then failed before its configured after_payment target:

FATAL: recovery ended before configured recovery target was reached

The original base still passed verification afterwards. The two observed results separate backup integrity from later target coverage: the base remained intact while the missing archive prevented reaching the target.

Complete recovery and reconcile the payment

The isolated candidate can be prepared while the source still runs. Final reconciliation needs a closed source history. Stop new authoritative source writes and dispatch, account for database commits and provider calls already in flight, and preserve their final outcomes. Prevent stale clients, workers and a restarted old source from becoming writers again. Failover and fencing develops that authority boundary.

After accepting the paused target, finish recovery under controlled access:

SELECT pg_wal_replay_resume();
-- Wait for completion, then confirm on the target:
SELECT pg_is_in_recovery();
-- false

Finishing recovery creates a new writable history branching from the recovered target. Keep ordinary writers and dispatch excluded while applying the accepted repairs. Do not replay the original purchase: order 103 and its stock decrement are already present. Do not replay the bad price.

The payment’s original operation identity is order-103-part-payment-1, with immutable meaning: order 103, 10.00 by card, resulting payment 504. Obtain its provider outcome using the provider’s retained lookup contract. The supplied branch assumes a known accepted result matching that meaning. Record that existing charge in the recovered database; sending another charge would create another effect.

Use the reconciliation established in Backup, restore and reconciliation: verify the order and agreement, compare any existing payment and identity mapping, then atomically record the omitted payment with its mapping. A repeat must verify the same result, and a changed amount or order must stop. Under the example payment rule, recorded payments must not exceed the unchanged 29.00 agreement.

SELECT operation_id, x.order_id, x.amount, x.payment_id,
       p.amount AS recorded_amount, p.method
FROM payment_operations x
JOIN payments p USING (payment_id)
WHERE operation_id = 'order-103-part-payment-1';
-- order-103-part-payment-1 | 103 | 10.00 | 504 | 10.00 | card

payment_operations is the drill’s identity-to-payment record, separate from the shop’s original tables. A uniqueness constraint protects the mapping; comparing its meaning protects against a wrong first mapping. If the provider outcome is unknown, keep that case unresolved. Restored absence is not evidence that no charge happened, and an uncertain outcome does not justify a new charge.

The completed branch must now show P7 at 20.00, pending order 103 with agreed total 29.00, exactly one 10.00 payment and 19.00 due. Stock remains 10/0/4. Review restored dispatch records before workers resume: a job that looks unsent may already have caused an effect. Carry forward applicable correction and deletion decisions before exposing recovered older data or rebuilding derived copies.

Return callers to one authority and resume only the work whose outcomes are accounted for. Promoting an existing physical replica is a different route, developed in PostgreSQL physical replicas and promotion. A successfully restored cluster still needs a controlled service handover.

Restoring a logical dump

A custom-format dump follows a simpler reconstruction path. Export one database, inspect its contents, then restore into a fresh isolated database created from template0:

pg_dump --host="$SOURCE_SOCKET" --port="$SOURCE_PORT" \
  --username="$DUMP_ROLE" --dbname="$SOURCE_DB" \
  --format=custom --file="$DUMP_FILE"

pg_restore --list "$DUMP_FILE"
createdb --host="$TARGET_SOCKET" --port="$TARGET_PORT" \
  --username="$RESTORE_ROLE" --template=template0 "$FRESH_DB"
pg_restore --host="$TARGET_SOCKET" --port="$TARGET_PORT" \
  --username="$RESTORE_ROLE" --dbname="$FRESH_DB" \
  --exit-on-error --single-transaction "$DUMP_FILE"

Use explicit verified destinations and stop on errors. The single transaction makes this small restore all-or-nothing; it cannot be combined with parallel restore jobs. A plain SQL dump goes through psql -X --set=ON_ERROR_STOP=1 instead of pg_restore.

Check required roles and grants before restoring ownership and privileges. A single-database dump does not define cluster-wide roles or tablespaces; handle required globals separately, for example with a reviewed pg_dumpall --globals-only export. Install compatible extension software before recreating its database objects. Inspect ownership and extension versions afterwards; omitting ownership or privilege restoration changes the result rather than repairing a missing role.

The drill’s dump is taken before the price mistake and restores P7 at 20.00 with no payment 504. It is a reconstruction of the dump’s saved view, not a selectable WAL target. Check the actual rows and intended application access just as with a physical restore. Upgrading and migrating PostgreSQL covers version compatibility and a migration’s write handover.

Keep the recovery path testable

Retain the complete path:

• full and required incremental backups
• consistency and later WAL
• timeline history and manifests
• compatible software and extensions
• configuration
• recovery access and any decryption material

Test the new writable branch’s backup and archive coverage before expiring an older usable path.

Rehearse the service objective with representative data and retained history. Measure obtaining access, fetching material, reconstruction, replay, validation and necessary reconciliation. A tiny restore proves that these steps work for its fixture; it gives no production recovery rate.

The figures are explanatory models. The full base backup, named-point restore, missing-segment failure, custom-format logical restore and repeated payment repair were checked on an owned Linux PostgreSQL 18.6 instance. Source and targets had separate data directories in one disposable container; this did not test an independent storage failure or a production recovery rate. Incremental reconstruction is described from the manual.

The source database was closed to connections before the recovered target’s payment repair. No production authority handover or external provider was tested: the accepted charge is supplied example evidence. Additional SQL checks for changed identity, atomic rollback and blind replay ran in PGlite 0.5.8, a WebAssembly build of PostgreSQL 18.3.