Recover PostgreSQL to a point in time with WAL archiving
Build a fail-closed PostgreSQL 18.4 WAL archive and verified base-backup chain, then restore to an exact target in isolation, validate while paused, promote, and re-establish protection.
Prove that a specific unwanted transaction can be excluded while required earlier data is retained, with measured RPO/RTO and no unsafe automatic promotion.
- PostgreSQL 18.4
- Ubuntu Server 24.04 LTS
- Compatible isolated recovery host PostgreSQL 18.4, required extensions/libraries, enough storage/I/O, synchronized UTC, new empty data and tablespace paths, and enforced side-effect isolation.
- Independent encrypted repository Read/write archive path separated by identity and failure domain; immutable/retained objects, capacity monitoring and recovery read credential.
- Business recovery contract RPO/RTO, target authority, UTC evidence, inclusivity/timeline decision, invariants, lost-work reconciliation and promotion approval.
- Preservable source The incident cluster can remain stopped/read-only and isolated while candidates are restored and validated.
OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.
Full guide
What you will build
- A PostgreSQL 18.4 continuous-archiving pipeline in which completed WAL segments are copied atomically and idempotently to encrypted storage in a separate failure domain, conflicting duplicates fail closed, archive failures alert, and retention is tied to verified base backups.
- A full physical base-backup workflow with manifest and checksum verification, protected metadata, restore-point recording, and recurring isolated restore tests rather than declaring success from file presence.
- A point-in-time recovery procedure that selects an exact time, named restore point, transaction ID, or LSN from incident evidence; restores into a new isolated cluster; follows the correct timeline; pauses at the target for human validation; and promotes only after database and application invariants pass.
- A forced WAL switch appears in the archive under the expected immutable key, checksum and permissions; `pg_stat_archiver` advances; missing/conflicting segments fail and alert instead of being silently overwritten.
- The selected base backup predates the target, its manifest passes `pg_verifybackup`, every required WAL segment and timeline-history file is available, and the recovery plan records expected RPO/RTO and ownership.
- The restored cluster reaches and pauses at the target without passing it, remains isolated/read-only during validation, contains expected pre-incident data and excludes the unwanted change, and produces a retained evidence bundle.
- Promotion creates a new timeline only after approval. Client cutover, backup-chain reset, archive behavior, rollback boundary, and retention of the original production evidence are explicit.
Architecture
How the parts fit together
The production PostgreSQL 18.4 primary writes WAL into `pg_wal`. The archiver invokes a root-owned or postgres-owned wrapper that validates the WAL filename, copies `%p` to a staging object/file in a separate encrypted repository, fsyncs or waits for durable provider acknowledgement, compares an existing destination rather than overwriting it, atomically publishes the final `%f`, and returns zero only when identical durable content exists. Base backups are captured with a manifest, verified, indexed with start/end LSN and timestamps, and retained with all WAL required through their recovery window. A new isolated restore host receives one chosen backup, configuration and secrets through controlled channels, and an archive restore wrapper. `recovery.signal` plus one explicit target and one reviewed timeline starts recovery. `recovery_target_action=pause` keeps the target read-only for validation. Only after approval is it promoted and routed; the damaged original remains preserved and isolated.
- Define RPO, RTO, data classification, retention, archive durability, restore isolation, target-selection authority, and acceptance invariants.
- Enable archiving with an atomic fail-closed wrapper, force a segment switch, retrieve and compare the archived file, and alert on count/time/capacity anomalies.
- Take a physical base backup with WAL stream and manifest, verify it, copy it to a separate failure domain, and register its LSN/time/timeline coverage.
- Create deterministic fixtures and a named restore point, then record an unwanted post-target change in an isolated rehearsal dataset.
- Choose the newest verified backup whose end precedes the target, prove the complete WAL/timeline chain, and restore to a new empty host.
- Set restore command, one recovery target, chosen timeline, pause action and signal file; start with all client ingress blocked.
- Watch recovery logs/LSN, pause at the target, validate schema/data/application invariants and absence of the unwanted change, then obtain approval.
- Promote to a new timeline, perform controlled client cutover, establish a new base backup/archive chain, and preserve the original for investigation or rollback before promotion.
Assumptions
- The source and restore binaries use PostgreSQL 18.4 and compatible architecture, extensions, locale, checksums, encryption keys, tablespace mappings and operating-system libraries.
- The WAL repository is independent of the primary host, encrypted, authenticated, capacity-monitored, access-controlled, and capable of returning every required segment and timeline-history file during source outage.
- UTC time is synchronized across database, application, audit and backup systems. A timestamp from a user's browser is not accepted without timezone and server-side correlation.
- The restore target is a new isolated host or storage set with no production endpoint, no automatic job execution, and no credentials that could call external production services.
- Business owners define concrete invariants and the unwanted event. PostgreSQL consistency alone cannot decide that the restored business state is correct.
- The original damaged cluster and archive evidence can be preserved until the recovery is accepted and legal/security retention requirements are met.
Key concepts
- Continuous archiving
- PostgreSQL invokes `archive_command` or `archive_library` for completed WAL segments. Success means the segment is safely stored; false success creates an undetectable recovery gap.
- Base backup
- A physical cluster copy that becomes consistent by replaying WAL. PITR begins from a backup that predates the recovery target.
- Recovery target
- Exactly one stopping criterion: time, name, transaction ID, LSN, or immediate consistency. `recovery_target_inclusive` controls whether the target transaction is included where applicable.
- Timeline history
- Metadata describing branches created by recovery promotion. Correct recovery may require WAL and `.history` files from multiple timelines.
- recovery.signal
- A file in the data directory that instructs PostgreSQL to perform targeted archive recovery using configured restore and target settings.
- recovery_target_action
- Behavior when target is reached. `pause` preserves a review point; `promote` immediately makes the cluster writable and is inappropriate before validation.
- RPO and RTO
- RPO bounds acceptable lost changes; RTO bounds restoration duration. Both must be measured by recurrent recovery drills.
Before you copy
Values used in this guide
{{dataDirectory}}Resolved PostgreSQL restore data directory.
Example: /var/lib/postgresql/18/recovery{{archiveDirectory}}Mounted or accessed path representing the independent WAL repository.
Example: /srv/postgresql-wal/cluster-7589916241639021840{{archiveClusterId}}Immutable PostgreSQL system identifier used as the exact archive namespace.
Example: 7589916241639021840{{baseBackup}}Exact verified base backup selected before the target.
Example: /srv/postgresql-base/2026-07-27T000000Z{{recoveryTargetTime}}UTC incident target with offset and evidence reference.
Example: 2026-07-28 11:42:18.442+00{{recoveryTargetName}}Unique named restore point when available.
Example: before_release_20260728_1140z{{recoveryTimeline}}Reviewed timeline selector determined from history.
Example: latestSecurity and production boundaries
- WAL and base backups contain database contents, credentials, deleted values, row history and possibly cryptographic material. Protect them at least as strongly as the live cluster.
- Archive/restore wrappers must reject path traversal and unexpected filenames, quote `%p/%f` safely, avoid shells where possible, never print secrets, and run with access only to the assigned cluster prefix.
- Do not let a recovery cluster send email, payments, webhooks, queue messages, replication, or scheduled jobs to production. Network isolation is part of data integrity.
- Keep the original source read-only and isolated after an incident. Restoring in place destroys evidence and the easiest rollback before promotion.
- A named restore point is visible in WAL and must not contain secrets. Use unique operational identifiers, not customer data.
- Promotion and routing are separate privileged decisions. Require two-person review of target evidence and invariants before making the recovered copy writable.
Stop before continuing if
- Stop if archive success semantics, encryption, immutable identity, capacity, ownership, or retrieval during primary outage cannot be proved.
- Stop if the selected backup does not predate the target, manifest fails, system identifier/version/tablespaces differ, or any required WAL/history file is missing.
- Stop if more than one recovery target is configured, timezone/timeline/inclusive semantics are ambiguous, or incident evidence cannot distinguish good from unwanted transactions.
- Stop if the restore host can reach production clients or external side-effect services, or if automated jobs may start.
- Do not promote when recovery has overshot, not reached, or failed to pause at the target, or when database/application invariants are incomplete.
- After promotion, do not resume from the original timeline or delete the old cluster/archive chain until the new backup chain and acceptance evidence are complete.
decision
Define RPO, RTO, evidence, isolation, and acceptance
Document which cluster and databases are covered, maximum acceptable data loss and outage, target-selection authority, archive/backup retention, encryption and legal holds, restore network isolation, required application invariants, side effects to disable, and promotion approval.
Why this step matters
PITR can produce many transactionally consistent states, but only the business can identify the correct one. Before configuring archiving, define which incidents it solves, how much committed work may be lost, how quickly recovery must finish, who selects the target, and which invariants distinguish acceptable from merely bootable. The recovery host must not emit side effects, because replaying or starting schedulers against production services can duplicate payments, messages or jobs. Retention must cover the oldest base backup plus all WAL through the recovery window and investigation hold. This contract turns infrastructure behavior into an auditable recovery service.
What to understand
Record cluster system identifier, PostgreSQL/extension versions, databases, tablespaces, encryption keys, and configuration dependencies.
Define timestamp/restore-point/LSN evidence sources, UTC handling, inclusive behavior, and two-person target approval.
List database constraints, row counts, financial totals, latest known-good markers, application smoke tests, and unwanted-event exclusion.
List every scheduler, queue, email, webhook, payment, replication and maintenance side effect to block.
Define backup/archive retention, deletion approvals, legal hold, recovery drill frequency, and evidence owner.
System changes
- None; creates the reviewed recovery contract.
Syntax explained
RPO- Maximum acceptable committed change loss measured at the recovery target.
RTO- Maximum time from declared recovery start to validated service restoration.
sed -n '1,240p' recovery/postgresql-pitr-contract.md && date -u +%FT%TZCluster system identifier: 7589916241639021840 PostgreSQL: 18.4 RPO: <= 5 minutes RTO: <= 120 minutes Recovery target authority: incident commander + data owner Promotion authority: database lead + application owner Restore network: isolated-recovery Last successful full drill: 2026-07-15T09:34:11Z
Checkpoint: Recovery success is measurable before an incident
test -s recovery/postgresql-pitr-contract.md && grep -E 'RPO|RTO|Promotion authority|Restore network|Last successful' recovery/postgresql-pitr-contract.mdContinue whenScope, owners, RPO/RTO, evidence, target semantics, isolation, invariants, side effects, retention and promotion decision are complete.
Stop whenNo business owner can select/accept a point, restore can reach production side effects, or retention and encryption cannot meet the contract.
If this step fails
Teams provide only 'restore before the incident'.
Likely causeThere is no correlated timestamp, transaction, named point, or audit evidence.
Build an UTC event timeline from database, application, audit and user evidence.
ResolutionDefine an evidence interval and rehearse multiple isolated candidates before choosing.
RTO is shorter than the last measured restore.
Likely causeCapacity, backup size, archive retrieval, validation, or approvals exceed the objective.
Break the last drill into transfer, replay, validation and routing durations.
ResolutionImprove parallelism/capacity/process or renegotiate RTO; do not publish an unmeasured target.
Security notes
- Recovery contracts may reveal sensitive topology and incident controls; restrict access and revisions.
Alternatives
- Maintain workload-specific recovery contracts when one cluster hosts applications with different acceptance rules.
Stop conditions
- No business owner can select/accept a point, restore can reach production side effects, or retention and encryption cannot meet the contract.
verification
Inventory cluster, WAL, backup, tablespace, and archive state
Capture PostgreSQL 18.4 version, system identifier, data directory, checksums, WAL segment size, timeline, archive settings and statistics, tablespaces, disk, UTC time, configuration files, extensions, and the latest verified recovery drill.
Why this step matters
The backup and WAL archive are meaningful only for the cluster system identifier, timeline and physical layout that produced them. A second PostgreSQL cluster can generate the same-looking WAL filename, so repositories must be partitioned by authenticated cluster identity rather than hostname alone. Tablespaces and external configuration are easy to omit from a restore plan. Cumulative archive statistics can be reset and do not prove repository durability. This inventory provides the exact baseline and exposes false assumptions before enabling or changing the archiver.
What to understand
Record `pg_controldata` system identifier, checksum version, current timeline and WAL segment size.
Inventory tablespace locations, symlinks, external configuration, SSL keys, extensions and required shared libraries.
Inspect `pg_stat_archiver` timestamps/counters and repository object freshness independently.
Record filesystem capacity and peak WAL rate, including maintenance and bulk operations.
Compare with the latest base-backup catalog and recovery drill result.
System changes
- None; writes a protected baseline record.
Syntax explained
system identifier- Unique physical cluster identity used to prevent archive/backup namespace collision.
pg_stat_archiver- Server-side cumulative archiver outcomes; not proof of off-host object integrity.
sudo -u postgres psql -X -c "SELECT version(),current_setting('data_directory'),pg_current_wal_lsn(); SHOW archive_mode; SHOW archive_command; SELECT * FROM pg_stat_archiver; SELECT spcname,pg_tablespace_location(oid) FROM pg_tablespace;"PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) data_directory = /var/lib/postgresql/18/main pg_current_wal_lsn = 4B/22001428 archive_mode = on archive_command = /usr/local/sbin/archive-wal "%p" "%f" archived_count = 94112 failed_count = 0 last_archived_wal = 000000010000004B00000021
Checkpoint: Every physical dependency belongs to one known cluster
sudo -u postgres pg_controldata /var/lib/postgresql/18/main | grep -E 'Database system identifier|Bytes per WAL segment|TimeLineID'Continue whenVersion, system ID, timeline, WAL size/rate, checksums, tablespaces, config, extensions, archive counters, storage and drill evidence agree.
Stop whenCluster identity, tablespaces, archive prefix, encryption keys, dependencies, statistics reset, or last recovery evidence is unknown.
If this step fails
Archive prefix contains files from multiple system identifiers.
Likely causeRepository naming used hostname/environment rather than immutable cluster identity.
Quarantine prefix and correlate backup manifests, control data and checksums.
ResolutionCreate isolated identity-based prefixes and re-establish a complete verified chain; never merge ambiguous WAL.
A tablespace location is not covered by backup storage.
Likely causePhysical layout inventory or snapshot/backup mapping is incomplete.
Compare `pg_tablespace`, backup manifest and restored symlinks.
ResolutionExtend the physical backup design and complete a new recovery test before claiming PITR coverage.
Security notes
- Do not publish system identifiers, paths or archive prefixes; they aid topology discovery.
Alternatives
- Use backup tooling catalog metadata, verifying it against live PostgreSQL control data.
Stop conditions
- Cluster identity, tablespaces, archive prefix, encryption keys, dependencies, statistics reset, or last recovery evidence is unknown.
config
Install a fail-closed atomic archive wrapper
Deploy a reviewed wrapper that accepts PostgreSQL `%p` and `%f`, allows only WAL/history filename patterns, copies to a unique temporary object, verifies size/checksum, durably publishes or atomically renames, treats an identical existing object as success, rejects different content, and returns nonzero on every uncertainty.
Why this step matters
PostgreSQL repeats `archive_command` until it returns success. This enables safe retry only if the wrapper is idempotent and refuses conflicting content. A naïve `cp` can expose partial files; unconditional overwrite can hide namespace collision or corruption; a command that returns true without storage permanently breaks future recovery. `%p` and `%f` are substituted into a shell command, so validation, quoting and fixed argument execution matter. Repository acknowledgement must mean durable content exists in the correct cluster prefix. The illustrative wrapper delegates the storage-specific atomic operation to a separately reviewed executable; it is not a claim that a comment uploads data.
What to understand
Whitelist exact 24-hex WAL names, 8-hex timeline-history names, and 24-hex plus offset backup-history names; reject separators, traversal, whitespace, options and every unexpected suffix.
Publish to a temporary unique key/file, fsync or await durable remote confirmation, then atomically expose the final immutable name.
If final exists, compare size and cryptographic digest: identical is success, mismatch is a critical failure.
Use a cluster-system-ID prefix and least-privilege credential that can create/read/compare only that prefix, not delete retention.
Limit output, redact endpoints/tokens, emit metrics/audit, and test network/full-disk/interruption/duplicate races.
System changes
- Installs an executable that will receive complete WAL paths from PostgreSQL.
Syntax explained
%p- Path of the completed WAL file relative to PostgreSQL working context; quote and pass as one argument.
%f- Archive filename used as the immutable repository key after strict validation.
/usr/local/sbin/archive-wal#!/bin/sh
set -eu
if [ "${1:-}" = "--self-test" ]; then
exec /opt/backup/bin/archive-put --self-test
fi
[ "$#" -eq 2 ] || { echo "usage: archive-wal SOURCE NAME" >&2; exit 64; }
src=$1
name=$2
[ -f "$src" ] || { echo "archive source is not a regular file" >&2; exit 66; }
printf '%s
' "$name" |
grep -Eq '^([0-9A-F]{24}|[0-9A-F]{8}.history|[0-9A-F]{24}.[0-9A-F]{8}.backup)$' ||
{ echo "invalid archive name" >&2; exit 64; }
# Production implementation publishes to the cluster-system-id prefix.
# It must compare an existing object, upload atomically, and return nonzero
# unless identical content is durably present. Never replace with /bin/true.
exec /opt/backup/bin/archive-put --source "$src" --name "$name"sudo install -o postgres -g postgres -m 0750 operations/archive-wal /usr/local/sbin/archive-wal && sudo -u postgres /usr/local/sbin/archive-wal --self-testPASS accepted WAL filename 000000010000004B00000022 PASS rejected ../escape PASS identical destination is idempotent success PASS conflicting destination is failure PASS temporary object cleaned after injected failure PASS no secret material in output
Checkpoint: Archive success means identical durable bytes exist
sudo -u postgres /usr/local/sbin/archive-wal --self-testContinue whenTraversal/conflicts/partial uploads fail, identical retries succeed, atomic durability and cluster namespace are verified, and no delete/secret capability exists.
Stop whenWrapper can overwrite conflicts, expose partial final files, accept arbitrary paths, return success on uncertainty, delete archives, or leak credentials.
If this step fails
Two archiver invocations race on the same destination.
Likely causeRetry/restart overlap is expected and final publication lacks conditional create/atomic rename.
Run the concurrency self-test and inspect temporary/final object semantics.
ResolutionImplement conditional immutable publish and identical-content comparison before activation.
The storage API accepted upload but later retrieval fails.
Likely causeAcknowledgement, replication/durability class, encryption key, or repository consistency expectation is wrong.
Retrieve immediately and from the disaster-recovery identity/region; compare digest.
ResolutionUse a storage class and acknowledgement meeting RPO, then repeat end-to-end tests.
Security notes
- Do not use `eval`, interpolate credentials, or grant archive deletion to the database host.
Alternatives
- Use a reviewed `archive_library` module or established PostgreSQL archive tool with equivalent fail-closed semantics.
Stop conditions
- Wrapper can overwrite conflicts, expose partial final files, accept arbitrary paths, return success on uncertainty, delete archives, or leak credentials.
config
Enable archiving and prove a forced segment end to end
Set `archive_mode=on` and the exact wrapper command, validate configuration, restart if archive mode changed, force a WAL switch, wait for `last_archived_wal` to advance, retrieve the final object through the recovery identity, and compare it byte-for-byte with the source segment where retained.
Why this step matters
`archive_mode` is a startup parameter; the command can be reloaded, but an untested edit can stall WAL recycling and consume disk. `archive_timeout` can force segment switches during low activity to bound time before a transaction's segment becomes archivable, at the cost of more partially filled files; it does not guarantee remote RPO by itself. A forced switch gives a specific filename and timestamp to trace through PostgreSQL, wrapper, repository, retrieval identity, checksum and retention. The test must be repeated after credential, repository, encryption or wrapper changes.
What to understand
Inspect `pg_file_settings` and pending restart before applying; retain the prior working command.
Restart only in an approved window if `archive_mode` changed and verify the live setting after start.
Capture counters before/after `pg_switch_wal`; wait with timeout rather than assuming immediate completion.
Retrieve using the identity and network available during primary loss, not the uploader credential.
Compare digest/size, encryption metadata, immutable key, retention, and repository audit event.
System changes
- Enables continuous WAL archiving and may increase segment switches/storage use.
Syntax explained
archive_mode=on- Starts archiver for completed WAL during normal primary operation.
archive_timeout- Forces a switch after age to reduce archive time window; values that are too low waste storage.
/etc/postgresql/18/main/conf.d/40-archive.confarchive_mode = on
archive_command = '/usr/local/sbin/archive-wal "%p" "%f"'
archive_timeout = '5min'sudo -u postgres psql -XAtqc "SELECT pg_switch_wal();" && sudo -u postgres psql -X -c "SELECT archived_count,failed_count,last_archived_wal,last_archived_time,last_failed_wal,last_failed_time FROM pg_stat_archiver;"4B/23000078
archived_count | failed_count | last_archived_wal | last_archived_time | last_failed_wal | last_failed_time
----------------+--------------+--------------------------+-------------------------------+-----------------+-----------------
94113 | 0 | 000000010000004B00000022 | 2026-07-28 13:04:19.117+00 | |Checkpoint: One known segment survives independent retrieval
operations/archive-verify --cluster-id 7589916241639021840 --wal 000000010000004B00000022 --retrieval-identity recoveryContinue whenCounter/filename advances, no unresolved failure, final object is durable/encrypted/immutable, recovery identity retrieves identical bytes, and disk remains safe.
Stop whenCounters stall, archive reports false success, retrieved digest differs, recovery identity cannot read, key/retention is wrong, or pg_wal grows toward threshold.
If this step fails
Forced segment never becomes last_archived_wal.
Likely causeArchiver is disabled, command fails, older WAL blocks the queue, or destination is unavailable.
Inspect `pg_stat_archiver`, PostgreSQL logs, process state, wrapper exit and repository capacity.
ResolutionRepair the first failing segment and allow ordered retry; do not remove pg_wal or skip the gap.
Repository object is smaller than WAL segment size.
Likely causeA partial upload was published or compression/format expectations are undocumented.
Inspect wrapper atomicity, metadata, digest and retrieval/decompression behavior.
ResolutionQuarantine the object, fail the archive pipeline and republish from the intact source segment.
Security notes
- Force-switch and verification output should use synthetic identifiers in external reports.
Alternatives
- Use an archive tool's native check command if it verifies the same end-to-end recovery identity and bytes.
Stop conditions
- Counters stall, archive reports false success, retrieved digest differs, recovery identity cannot read, key/retention is wrong, or pg_wal grows toward threshold.
instruction
Monitor archive continuity, capacity, encryption, and retention
Alert on last successful archive age with WAL-activity context, failure count/time, backlog files, `pg_wal` capacity, repository capacity/errors, missing sequential segments, encryption/key expiry, retrieval failures, and the age of the last successful restore drill. Retain WAL according to base-backup recovery windows, not a fixed file count.
Why this step matters
Archive health has both local and remote states. The archiver can be current while repository replication, encryption key, retention, or recovery credential is broken. Conversely, `last_archived_time` can appear old on an idle cluster; alerting must consider whether WAL was generated. `.ready` backlog plus generation rate estimates time to disk exhaustion. Deleting WAL based on age alone can invalidate a retained base backup. A catalog should compute each backup's required starting WAL and preserve continuous history through its promised recovery endpoint and timeline branches.
What to understand
Alert on failed count/timestamp deltas and oldest `.ready` age, not just cumulative total.
Measure `pg_wal` free bytes and backlog rate with predictive warning before emergency capacity.
Continuously check archive naming sequence/timeline history and periodically retrieve random segments through recovery credentials.
Monitor repository replication/durability, encryption key availability/expiry, object retention and audit anomalies.
Delete only when a verified newer base backup and policy prove no retained restore window needs the segments.
System changes
- Adds monitoring, continuity catalog and policy-governed retention jobs.
Syntax explained
.ready- Archive-status marker for a completed segment awaiting successful archive.
stats_reset- Context needed when interpreting cumulative archiver counts.
sudo -u postgres psql -X -c "SELECT archived_count,failed_count,last_archived_wal,last_archived_time,last_failed_wal,last_failed_time,stats_reset FROM pg_stat_archiver;" && find /var/lib/postgresql/18/main/pg_wal/archive_status -name '*.ready' -printf '%T@ %f\n' | sort -n | head archived_count | failed_count | last_archived_wal | last_archived_time | stats_reset
----------------+--------------+--------------------------+-------------------------------+-------------------------------
94113 | 0 | 000000010000004B00000022 | 2026-07-28 13:04:19.117+00 | 2026-06-01 00:00:00+00
0 ready files; repository continuity check PASS through 000000010000004B00000022Checkpoint: Archive failures are detected before RPO or disk is lost
operations/archive-audit --cluster-id 7589916241639021840 --verify-continuity --test-recovery-read --check-retentionContinue whenNo unexplained gap, backlog within response headroom, recovery retrieval succeeds, keys/storage healthy, and every retained backup has continuous required WAL.
Stop whenA gap/conflict exists, recovery credentials fail, deletion cannot prove backup independence, or disk exhaustion can occur before response.
If this step fails
Continuity scanner reports a filename gap.
Likely causeArchive failure, retention bug, wrong timeline assumption, or catalog race exists.
Correlate `pg_stat_archiver`, timeline history, backup catalog and immutable repository audit.
ResolutionFreeze deletions, locate authenticated missing content, and mark affected backups unrecoverable until a new tested chain exists.
Archive age alert fires with no workload.
Likely causeAlert ignores current WAL generation and last switch.
Compare `pg_current_wal_lsn`, `pg_stat_wal` and last archived filename.
ResolutionUse activity-aware freshness while retaining an absolute periodic forced-switch verification.
Security notes
- Retention deletion requires a separate privileged identity and approval; uploader should not delete.
Alternatives
- Use repository object lock/immutability and lifecycle rules generated from a verified backup catalog.
Stop conditions
- A gap/conflict exists, recovery credentials fail, deletion cannot prove backup independence, or disk exhaustion can occur before response.
command
Take, verify, catalog, and separate a full base backup
Run `pg_basebackup` to a new protected directory with streamed WAL, progress, spread checkpoint and default manifest/checksum verification; then run `pg_verifybackup`, calculate an outer artifact digest, copy to independent storage, reverify there, and catalog system ID, times, LSN, timeline, tablespaces, version and archive coverage.
Why this step matters
PITR cannot replay backward, so a base backup must become consistent before or at the target. `pg_basebackup` creates a physical copy and manifest; streamed WAL protects consistency without waiting for archive completion. The default manifest enables `pg_verifybackup` to check files, checksums and required WAL ranges, but verification does not prove application correctness or remote durability. Catalog metadata must survive separately and connect the backup to system identifier, timeline and required archive range. A copy on the same host/storage account is not an independent failure domain.
What to understand
Use a dedicated replication/backup identity and protected local socket or verify-full TLS; never pass passwords in arguments.
Provision exact tablespace destinations and sufficient temporary/final capacity before beginning.
Keep the manifest and do not use `--no-sync` in production; a command return before durable filesystem writes weakens the artifact.
Run `pg_verifybackup` before copying, copy with encryption/integrity, then run it or equivalent digest verification at destination.
Catalog backup start/end times and LSNs, system ID, timeline, version, tablespaces, checksum, repository key, retention and drill status.
System changes
- Creates a full sensitive physical backup and consumes I/O/network/WAL sender capacity.
Syntax explained
--wal-method=stream- Streams required WAL while files copy, using a second replication connection.
--checkpoint=spread- Reduces immediate checkpoint I/O at the cost of a slower start.
pg_verifybackup- Checks the backup against its manifest; does not replace a recovery rehearsal.
Values stay on this page and are never sent or saved.
sudo -u postgres pg_basebackup --dbname='host=/var/run/postgresql user=backup_physical' --pgdata={{baseBackup}} --format=plain --wal-method=stream --checkpoint=spread --progress --label=pitr-full-20260728 && sudo -u postgres pg_verifybackup {{baseBackup}}31254784/31254784 kB (100%), 2/2 tablespaces backup successfully completed backup manifest verified Files verified: 21842 WAL ranges verified: 1 Backup successfully verified
Checkpoint: The catalog points to a verified independent backup
sudo -u postgres pg_verifybackup {{baseBackup}} && operations/backup-catalog verify --backup {{baseBackup}} --require-independent-copyContinue whenManifest and outer digest pass locally/remotely, catalog has complete identity/LSN/timeline/tablespace/archive metadata, and retention protects the full chain.
Stop whenBackup/manifest/digest fails, target is same failure domain only, tablespace is missing, catalog metadata is incomplete, or required WAL coverage is absent.
If this step fails
Base backup finishes but manifest verification fails on required WAL.
Likely causeWAL copy is incomplete/corrupt or backup destination changed after creation.
Preserve manifest report, inspect backup/WAL sender logs and filesystem errors.
ResolutionReject the backup and create a new timestamped artifact after fixing the root cause.
Copy to independent storage changes digest.
Likely causeTransfer truncation, corruption, encryption packaging, or wrong artifact was selected.
Compare size, manifest, transport logs and content digest at both ends.
ResolutionQuarantine the remote copy and repeat a verified transfer; do not catalog it as recoverable.
Security notes
- Backup roles, directories, manifests and remote credentials require least privilege; manifests expose file topology.
Alternatives
- Use tar/compressed or incremental PostgreSQL 18 backups with `pg_combinebackup`, but validate the complete combined restore artifact.
Stop conditions
- Backup/manifest/digest fails, target is same failure domain only, tablespace is missing, catalog metadata is incomplete, or required WAL coverage is absent.
command
Create a named target and deterministic recovery fixture
In an isolated rehearsal database or approved production maintenance event, write a known-good marker, create a uniquely named restore point after its commit, record returned LSN and UTC time, then write a clearly synthetic unwanted marker. Archive through the segment containing both and preserve audit evidence.
Why this step matters
A deterministic fixture makes the restore outcome observable: the good marker must exist and the unwanted marker must not when recovering to the named point. Named restore points are WAL records and require replication-capable privilege; they should be rare, unique and non-sensitive. Their name identifies a target more reliably than a human wall-clock estimate, though application evidence still determines whether the point is semantically correct. In production incidents there may be no pre-created point, so the same method is primarily a drill and release-control pattern; time/LSN evidence remains necessary.
What to understand
Use a dedicated non-business table/database and unique IDs; never mutate customer or financial rows for a test.
Commit the good marker before calling `pg_create_restore_point`; record returned LSN, server UTC timestamp and timeline.
Commit the unwanted marker only after the restore point, then force and verify archive completion.
Store evidence with backup catalog and drill record, not in shell history containing sensitive queries.
Define target inclusivity: recovery by name stops after reaching the named restore point record.
System changes
- Creates two synthetic rows, one restore-point WAL record, and a forced WAL switch.
Syntax explained
pg_create_restore_point- Writes a named WAL marker usable as `recovery_target_name`.
pg_switch_wal- Ends the current segment so the archiver can publish it promptly.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d recovery_fixture -c "INSERT INTO pitr_probe(id,state,created_at) VALUES ('20260728-a','good',clock_timestamp());" -c "SELECT pg_create_restore_point('{{recoveryTargetName}}'), clock_timestamp();" -c "INSERT INTO pitr_probe(id,state,created_at) VALUES ('20260728-b','unwanted',clock_timestamp());" -c "SELECT pg_switch_wal();"INSERT 0 1 pg_create_restore_point | clock_timestamp -------------------------+------------------------------- 4B/26004C10 | 2026-07-28 13:30:00.018+00 INSERT 0 1 pg_switch_wal --------------- 4B/27000078
Checkpoint: The target separates known-good and unwanted state
sudo -u postgres psql -X -d recovery_fixture -c "TABLE pitr_probe;" && operations/archive-wait --lsn 4B/27000078Continue whenGood and unwanted markers are committed in order, named point LSN/time/timeline recorded, and containing WAL is durably archived.
Stop whenFixture touches business data, restore-point identity is ambiguous, timestamps lack UTC, transaction ordering is uncertain, or WAL is not archived.
If this step fails
Restore point creation is denied.
Likely causeThe operator lacks required privilege or connected to a standby.
Check recovery state and role attributes without granting broad permanent access.
ResolutionUse an approved short-lived administrative path or a timestamp/LSN target; do not grant application roles replication privilege.
Both markers appear before the target in audit evidence.
Likely causeThey shared one transaction or commit ordering was misunderstood.
Inspect transaction boundaries, timestamps and WAL/audit records.
ResolutionCreate a new fixture with explicit separate commits and a new unique restore point.
Security notes
- Restore point names are retained in WAL; exclude secrets, usernames and customer identifiers.
Alternatives
- Use a release transaction/audit LSN or carefully correlated UTC target when a named point was not created.
Stop conditions
- Fixture touches business data, restore-point identity is ambiguous, timestamps lack UTC, transaction ordering is uncertain, or WAL is not archived.
decision
Select backup, target, timeline, and inclusive behavior from evidence
Build an incident timeline in UTC, identify the last known-good event and first unwanted commit, choose exactly one recovery target, determine inclusivity, inspect timeline history, and select the newest verified base backup whose end precedes the target and whose WAL chain is complete.
Why this step matters
Recovery target mistakes can be perfectly executed and still lose the wrong transactions. A base backup after the target cannot travel backward. At most one target parameter is allowed. Time targets need explicit timezone and transaction commit interpretation; `recovery_target_inclusive=false` excludes the target transaction where supported, while defaults include it. Timeline selection matters after previous promotions: `latest` follows the latest history available, which is often correct for HA but can be wrong if the incident belongs to an earlier branch. Inspect `.history` files and explain the choice rather than copying `latest` mechanically.
What to understand
Correlate PostgreSQL, application, audit, queue and user evidence in UTC and record uncertainty bounds.
Prefer a unique named restore point or exact LSN tied to a known event; use time when that is the strongest evidence.
Select one target only and document inclusive/exclusive semantics and expected good/unwanted markers.
Inspect timeline history and confirm the chosen path contains the target; preserve required history files.
Use the newest verified backup ending before target and compute every WAL segment required through target plus safety.
System changes
- None; produces a signed recovery plan.
Syntax explained
recovery_target_time/name/lsn/xid- Mutually exclusive target selectors; configure exactly one.
recovery_target_inclusive- Controls whether the target transaction is included for time/XID/LSN semantics.
recovery_target_timeline- Selects the history branch to follow; justify from timeline files.
Values stay on this page and are never sent or saved.
operations/plan-pitr --catalog backup-catalog.json --cluster-id 7589916241639021840 --target-name {{recoveryTargetName}} --timeline {{recoveryTimeline}} --require-complete-wal --output recovery-plan.jsonSelected backup: 2026-07-27T000000Z Backup end LSN: 4A/F81230D0 Target: name=before_release_20260728_1140z Target LSN: 4B/26004C10 Timeline: 1 (history verified; latest resolves to 1) Required WAL: 291 segments Continuity: PASS Estimated replay: 00:37:20
Checkpoint: One evidenced target has one complete recovery chain
jq -e '.complete_wal==true and .backup_end_before_target==true and ([.targets[]]|length)==1 and .approvals|length>=2' recovery-plan.jsonContinue whenOne target, timezone/inclusivity/timeline rationale, pre-target verified backup, complete WAL/history list, invariants and two approvals exist.
Stop whenTarget interval is ambiguous without acceptance plan, multiple targets are configured, backup ends after target, timeline is guessed, or WAL continuity fails.
If this step fails
No single timestamp separates good and unwanted work.
Likely causeConcurrent transactions overlap the incident window or evidence precision is insufficient.
Identify exact transaction IDs/LSNs/audit events and model expected losses for candidate points.
ResolutionRestore multiple isolated candidates and let owners compare invariants; do not pretend false precision.
Latest timeline does not contain the expected target.
Likely causeA previous promotion branched history and the incident evidence references another branch.
Inspect all timeline `.history` files and backup timeline/start/end LSN.
ResolutionChoose the evidenced timeline explicitly and verify required history/WAL before retry.
Security notes
- Incident evidence may contain personal data and sensitive SQL; minimize and restrict the recovery plan.
Alternatives
- Use logical extraction from multiple candidate restores when business reconciliation cannot accept cluster-wide loss.
Stop conditions
- Target interval is ambiguous without acceptance plan, multiple targets are configured, backup ends after target, timeline is guessed, or WAL continuity fails.
warning
Prepare a new isolated restore host and empty storage
Provision a compatible PostgreSQL 18.4 host with network egress denied except archive retrieval and approved validation, disable schedulers and outbound side effects, confirm the target data and tablespace directories are new and empty, install required extensions/libraries, and obtain read-only recovery credentials.
Why this step matters
Restoring in place destroys original evidence and makes rollback harder. A new host supports repeated candidate restores and keeps the damaged source untouched. Isolation must account for database jobs, application workers, extensions, foreign data wrappers, logical replication, webhooks and monitoring automation—not only inbound client traffic. The restore identity should read exact backup/WAL prefixes but never upload or delete. Compatible binaries and extension shared objects are required before PostgreSQL can replay and validate the cluster. Empty-path checks defend against deleting or overlaying another cluster.
What to understand
Resolve and display every data/tablespace path from the recovery plan; create new mounts with postgres ownership and restrictive modes.
Install exact PostgreSQL 18.4 and compatible extension libraries/configuration from approved artifacts.
Block production endpoints and outbound side-effect destinations at network and service layers.
Disable cron/systemd timers, job runners, logical subscribers and application autostart until explicit validation.
Provide only read access to selected backup/WAL and required decryption keys through short-lived recovery credentials.
System changes
- Creates isolated infrastructure and empty PostgreSQL storage; no production data is changed.
Syntax explained
empty target- Prevents overlaying an existing cluster and makes repeated recovery deterministic.
read-only archive identity- Allows recovery without permitting archive modification or deletion.
Values stay on this page and are never sent or saved.
sudo systemctl stop postgresql@18-recovery && sudo test -z "$(find {{dataDirectory}} -mindepth 1 -maxdepth 1 -print -quit)" && ./operations/verify-recovery-isolation --host recovery-a --deny-production-side-effectsPASS PostgreSQL 18.4 binaries and extension libraries PASS empty data and mapped tablespace directories PASS no production writer endpoint registration PASS outbound email/payment/webhook/queue denied PASS archive read-only identity PASS time synchronized and capacity sufficient
Checkpoint: The target cannot damage production or existing data
./operations/verify-recovery-isolation --host recovery-a --deny-production-side-effects && sudo test -z "$(find {{dataDirectory}} -mindepth 1 -maxdepth 1 -print -quit)"Continue whenCompatible software, empty exact paths, sufficient capacity/time, read-only archive access, and enforced side-effect isolation all pass.
Stop whenAny path contains data, host can register as production/write externally, required extension/key is absent, or archive identity can modify retention.
If this step fails
A target tablespace directory is not empty.
Likely causeOld rehearsal or unrelated data uses the mapped path.
Resolve mount/device ownership and compare recovery plan without deleting anything.
ResolutionAllocate a new verified empty path; do not clean an ambiguous directory.
Recovery host needs unrestricted Internet for packages.
Likely causeApproved artifacts or internal repositories are missing.
List exact packages/extensions and compare approved mirrors.
ResolutionStage verified artifacts or approve narrow repository access; never mix arbitrary downloads into incident recovery.
Security notes
- Recovery credentials and decrypted data must expire/be destroyed after evidence retention and acceptance.
Alternatives
- Use an isolated account/project/VPC or offline recovery environment with staged artifacts.
Stop conditions
- Any path contains data, host can register as production/write externally, required extension/key is absent, or archive identity can modify retention.
command
Restore and verify the selected base backup without starting PostgreSQL
Copy or extract the selected backup into exact empty data and tablespace locations, preserve ownership/modes, verify manifest and catalog digest again, compare system identifier/version, restore controlled configuration separately, and keep all services stopped.
Why this step matters
The backup was verified at creation, but storage, transfer and extraction can alter it. Verify again at the exact restore location before adding recovery configuration. Tablespace links and external configuration require explicit mapping. Do not blindly copy source `postgresql.conf`, HBA, TLS keys or service credentials: the recovery host needs isolation and may use different paths. `pg_verifybackup` checks manifest integrity; control data confirms system identity and state. Starting early can consume WAL, trigger jobs or even become writable if the signal file is absent.
What to understand
Confirm selected repository key/digest and target paths against the signed recovery plan before transfer.
Use an integrity-preserving copy/extract mode and inspect errors; do not merge with existing files.
Restore each tablespace to its planned target and validate symlink ownership/path.
Run manifest and outer digest verification, then compare system identifier and PostgreSQL 18.4 compatibility.
Install recovery-specific configuration/HBA/TLS separately and keep service disabled until signal/target review.
System changes
- Populates the isolated target with a full sensitive cluster copy.
Syntax explained
rsync --checksum- Compares content during controlled transfer; manifest remains the authoritative backup check.
pg_verifybackup- Validates files and WAL ranges described by the backup manifest.
Values stay on this page and are never sent or saved.
sudo -u postgres rsync -aHAX --numeric-ids --checksum {{baseBackup}}/ {{dataDirectory}}/ && sudo -u postgres pg_verifybackup {{dataDirectory}} && sudo -u postgres pg_controldata {{dataDirectory}} | grep -E 'Database system identifier|Database cluster state|TimeLineID'Backup successfully verified Database system identifier: 7589916241639021840 Database cluster state: shut down in recovery Latest checkpoint's TimeLineID: 1
Checkpoint: The untouched restored base matches catalog and manifest
sudo -u postgres pg_verifybackup {{dataDirectory}} && operations/backup-catalog compare --backup {{baseBackup}} --restored {{dataDirectory}}Continue whenManifest/digest/system ID/version/tablespaces match, configuration remains recovery-specific, and PostgreSQL has not started.
Stop whenAny verification differs, extraction reports errors, path ownership/mapping is wrong, source secrets/routing are copied, or service starts.
If this step fails
Manifest passes at source but fails after restore.
Likely causeTransfer corruption, changed files, wrong tablespace extraction, or metadata transformation occurred.
Compare failed manifest paths, transfer logs, storage errors and destination filesystem semantics.
ResolutionDiscard the target copy, repair transfer/storage and restore again from the immutable source.
System identifier differs from recovery plan.
Likely causeWrong backup or catalog entry was selected.
Stop, preserve labels/digests, and compare repository identity without starting.
ResolutionErase only the confirmed disposable target and restore the exact cataloged backup.
Security notes
- Do not use a general-purpose copy identity with access to unrelated backup prefixes.
Alternatives
- Use backup-tool restore with equivalent exact-target, manifest, tablespace and identity checks.
Stop conditions
- Any verification differs, extraction reports errors, path ownership/mapping is wrong, source secrets/routing are copied, or service starts.
config
Install and self-test the archive restore wrapper
Install a fixed-argument restore wrapper that accepts only PostgreSQL WAL, timeline-history, and backup-history filenames; reads only the selected cluster namespace; verifies the retrieved object; writes a private temporary file beside `%p`; and atomically renames it only after success.
Why this step matters
During archive recovery PostgreSQL repeatedly invokes `restore_command` for WAL and timeline files. A wrapper that writes directly to `%p`, accepts arbitrary names, treats absence as success, or hides checksum/authentication failure can make recovery consume partial or wrong history. The wrapper therefore validates the exact filename grammar, binds retrieval to one immutable cluster identifier, delegates to a reviewed non-shell client with fixed arguments, publishes beside `%p` atomically, and preserves nonzero exit status. A missing future WAL file can be a normal end-of-archive condition, but corruption, authentication failure and wrong namespace must remain distinguishable in the delegated tool's structured logs.
What to understand
Run self-tests with valid, absent, corrupt, traversal, option-like, concurrent, interrupted and full-filesystem fixtures before every wrapper or repository-client release.
The recovery identity may read and verify one cluster prefix only; it cannot upload, overwrite, delete, change retention, decrypt another cluster, or list unrelated namespaces.
The delegated archive-get client must authenticate repository metadata and content digest before returning zero and must cap logs without printing endpoints, tokens or database content.
The temporary file is created in the destination directory so the final rename is same-filesystem and atomic; a trap removes every failed partial.
Keep the script and delegated binary root-owned or otherwise protected from the database service account even though execution runs as `postgres`.
System changes
- Installs a recovery executable with read access to the selected archive namespace.
- Self-tests create and remove only disposable fixtures before recovery starts.
Syntax explained
--cluster-id- Pins every read to the immutable PostgreSQL system-identifier namespace instead of a mutable hostname.
--verify- Requires the delegated client to authenticate and checksum the complete object before success.
same-directory temporary file- Prevents PostgreSQL from seeing partial bytes and makes the final rename atomic on one filesystem.
/usr/local/sbin/restore-walValues stay on this page and are never sent or saved.
#!/bin/sh
set -eu
if [ "${1:-}" = "--self-test" ]; then
exec /opt/backup/bin/archive-get --self-test --cluster-id "{{archiveClusterId}}"
fi
[ "$#" -eq 2 ] || { echo "usage: restore-wal NAME DESTINATION" >&2; exit 64; }
name=$1
dest=$2
printf '%s
' "$name" |
grep -Eq '^([0-9A-F]{24}|[0-9A-F]{8}.history|[0-9A-F]{24}.[0-9A-F]{8}.backup)$' ||
{ echo "invalid archive name" >&2; exit 64; }
data_root="{{dataDirectory}}"
wal_root=$(realpath "$data_root/pg_wal")
case "$dest" in
/*) dest_abs=$dest ;;
*) dest_abs="$data_root/$dest" ;;
esac
dest_parent=$(realpath "$(dirname "$dest_abs")")
[ "$dest_parent" = "$wal_root" ] ||
{ echo "destination is outside the selected pg_wal" >&2; exit 64; }
dest_abs="$dest_parent/$(basename "$dest_abs")"
[ ! -e "$dest_abs" ] || { echo "destination already exists" >&2; exit 73; }
umask 077
tmp="${dest_abs}.partial.$$"
trap 'rm -f "$tmp"' EXIT HUP INT TERM
/opt/backup/bin/archive-get --cluster-id "{{archiveClusterId}}" --name "$name" --destination "$tmp" --verify
[ -s "$tmp" ] || { echo "empty restored object" >&2; exit 74; }
chmod 0600 "$tmp"
mv "$tmp" "$dest_abs"
trap - EXIT HUP INT TERMsudo install -o postgres -g postgres -m 0750 operations/restore-wal /usr/local/sbin/restore-wal && sudo -u postgres /usr/local/sbin/restore-wal --self-testPASS accepted WAL, timeline history, and backup history names PASS rejected traversal, whitespace, option, and suffix variants PASS valid object verified and atomically published PASS missing object returned nonzero without a destination PASS corrupt object returned nonzero and removed its partial file PASS repository identity cannot list, write, or delete
Checkpoint: Archive retrieval fails closed and publishes only verified bytes
sudo -u postgres /usr/local/sbin/restore-wal --self-test && stat -c '%a %U %G %n' /usr/local/sbin/restore-walContinue whenEvery unsafe filename and injected failure returns nonzero, no partial destination remains, a valid object matches its authenticated digest, and the recovery identity has read-only one-prefix scope.
Stop whenSelf-test is absent, missing/corrupt objects return zero, partial files survive, destination scope is broad, arbitrary shell text is evaluated, or credentials/content appear in logs.
If this step fails
A valid object is retrieved but checksum verification fails.
Likely causeThe repository object, metadata, decryption key, transfer, or selected cluster namespace does not match the recovery plan.
Preserve the object identifier, expected/actual digest and structured client error without exposing content.Compare the signed backup catalog and repository audit trail from an independent identity.
ResolutionQuarantine the object and stop recovery until a complete authenticated chain is selected; never bypass verification.
A failed retrieval leaves a partial destination.
Likely causeThe client wrote directly to `%p`, the trap is ineffective, or the temporary path crosses filesystems.
Inject interruption and full-disk failures while watching the destination directory.
ResolutionRemove only the disposable partial, repair same-directory atomic publication, and repeat all self-tests before starting PostgreSQL.
Security notes
- The script handles raw WAL containing the complete database change stream. Protect its credentials, logs, temporary files and recovered bytes as production data.
Alternatives
- Use an established PostgreSQL archive-get command with equivalent exact namespace, filename, digest, atomic-write, least-privilege and self-test guarantees.
Stop conditions
- Self-test is absent, missing/corrupt objects return zero, partial files survive, destination scope is broad, arbitrary shell text is evaluated, or credentials/content appear in logs.
config
Configure restore command, one target, timeline, pause action, and signal
Require the tested fail-closed restore wrapper, configure exactly one target and reviewed timeline, choose inclusive semantics explicitly, set `recovery_target_action=pause`, create `recovery.signal`, validate permissions, and scan configuration for conflicting recovery settings.
Why this step matters
PostgreSQL archive recovery requires `restore_command` and `recovery.signal`. The restore wrapper must return nonzero promptly when a requested file is absent so PostgreSQL can interpret end-of-archive behavior; it must distinguish absence from corruption/authentication failure in operator logs. Exactly one target prevents configuration errors. `pause` creates a controlled read-only review state; automatic promote would cross the rollback boundary before business validation. Timeline is explicit from the signed plan. Old `primary_conninfo` or `standby.signal` can make the server stream from a live primary and override the intended archive-only recovery, so remove or isolate those settings deliberately.
What to understand
Validate `%f` filename and write `%p` atomically with correct owner/mode; never expose a partial restored segment.
Use read-only scoped archive credentials and authenticated decryption; checksum content before publication to `%p`.
Configure one of time/name/LSN/XID only; state inclusive behavior for time/XID/LSN.
Set reviewed timeline from history, not an unexplained default; make all required `.history` files retrievable.
Create only `recovery.signal`; remove `standby.signal` and stale streaming settings for this isolated targeted recovery.
System changes
- Validates the installed restore wrapper and creates recovery configuration and signal but does not start PostgreSQL.
Syntax explained
restore_command- Fetches each requested WAL/history file; nonzero indicates unavailable/retry according to recovery flow.
recovery_target_action=pause- Pauses recovery at target for read-only validation instead of making the cluster writable.
recovery_target_timeline- Selects the reviewed WAL history branch.
/etc/postgresql/18/recovery/conf.d/50-pitr.confValues stay on this page and are never sent or saved.
restore_command = '/usr/local/sbin/restore-wal "%f" "%p"'
recovery_target_name = '{{recoveryTargetName}}'
recovery_target_timeline = '{{recoveryTimeline}}'
recovery_target_action = 'pause'
# For a time target instead, remove recovery_target_name and set exactly:
# recovery_target_time = '{{recoveryTargetTime}}'
# recovery_target_inclusive = falseValues stay on this page and are never sent or saved.
sudo -u postgres /usr/local/sbin/restore-wal --self-test && sudo -u postgres touch {{dataDirectory}}/recovery.signal && sudo -u postgres grep -REn '^(restore_command|recovery_target_|primary_conninfo|primary_slot_name)' {{dataDirectory}}/postgresql.auto.conf /etc/postgresql/18/recovery 2>/dev/null/etc/postgresql/18/recovery/50-pitr.conf:1:restore_command = '/usr/local/sbin/restore-wal "%f" "%p"' /etc/postgresql/18/recovery/50-pitr.conf:2:recovery_target_name = 'before_release_20260728_1140z' /etc/postgresql/18/recovery/50-pitr.conf:3:recovery_target_timeline = '1' /etc/postgresql/18/recovery/50-pitr.conf:4:recovery_target_action = 'pause' /var/lib/postgresql/18/recovery/recovery.signal
Checkpoint: Recovery has one target and cannot auto-promote
test "$(grep -RhE '^recovery_target_(time|name|lsn|xid)[[:space:]]*=' /etc/postgresql/18/recovery {{dataDirectory}}/postgresql.auto.conf | wc -l)" -eq 1 && grep -Rh "recovery_target_action = 'pause'" /etc/postgresql/18/recoveryContinue whenOne target, explicit timeline/inclusivity, pause action, exact archive prefix, atomic restore wrapper, recovery.signal, no standby.signal/streaming override, and safe permissions.
Stop whenMultiple/no targets, automatic promote, wrong/unknown timeline, stale streaming config, restore wrapper accepts arbitrary paths or masks corruption, or credentials leak.
If this step fails
Both standby.signal and recovery.signal exist.
Likely causeBase-backup recovery configuration was not removed; standby mode takes precedence.
Keep service stopped and inspect all signal/connection sources.
ResolutionRemove only the unintended standby signal/settings from the disposable target and revalidate archive-only plan.
Restore wrapper returns success for a missing file.
Likely causeIt masks repository absence and can make recovery consume an incomplete/empty destination.
Run missing/corrupt/valid self-tests with exact exit codes and destination checks.
ResolutionFix fail-closed semantics before starting recovery.
Security notes
- The restore identity must not list/read other clusters or modify/delete repository content.
Alternatives
- Use an established archive-get utility configured to the exact stanza/prefix with equivalent verification.
Stop conditions
- Multiple/no targets, automatic promote, wrong/unknown timeline, stale streaming config, restore wrapper accepts arbitrary paths or masks corruption, or credentials leak.
verification
Start isolated recovery and observe progress without interference
Start the recovery instance with production routing blocked. Follow startup logs, confirm recovery mode and replay LSN/timestamp, observe requested WAL and timeline changes, monitor disk/I/O, and wait for the documented target-pause message. Do not call promote or create a trigger file.
Why this step matters
Recovery is an execution workload that can fail on missing WAL, wrong timeline, corrupt archive, incompatible extensions, tablespace paths, storage exhaustion or target absence. Logs provide the exact requested segment and stopping reason. Recovery mode and replay LSN show progress, but polling should not create load or alter state. A pause is the intended state and must not trigger an availability alarm that automatically restarts/promotes the cluster. If recovery passes the expected LSN or reaches end of WAL, the candidate is rejected and rebuilt from the clean base backup; physical replay cannot be undone.
What to understand
Confirm the service uses the isolated data/config directory and all production ingress/side-effect egress remains blocked.
Stream logs to protected evidence and alert on invalid record, missing file, checksum, timeline, tablespace or extension errors.
Track replay LSN and target relationship, archive retrieval rate, remaining bytes, disk, I/O and estimated completion.
Expect transient requests for non-WAL history/next files according to PostgreSQL semantics; distinguish normal absence from chain gap.
Require explicit log message and `pg_is_wal_replay_paused()=true` at target.
System changes
- Starts PostgreSQL in targeted archive recovery and replays WAL into the isolated copy.
Syntax explained
pg_is_in_recovery- Remains true while archive recovery has not been promoted.
pg_is_wal_replay_paused- Confirms the configured target action paused replay for review.
sudo systemctl start postgresql@18-recovery && sudo -u postgres psql -X -c "SELECT pg_is_in_recovery(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp(),pg_is_wal_replay_paused();" && journalctl -u postgresql@18-recovery --since '10 minutes ago' --no-pager | tail -n 40pg_is_in_recovery | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp | pg_is_wal_replay_paused -------------------+------------------------+-------------------------------+------------------------- t | 4B/26004C10 | 2026-07-28 13:29:59.984+00 | t LOG: starting point-in-time recovery to "before_release_20260728_1140z" LOG: restored log file "000000010000004B00000025" from archive LOG: recovery stopping at restore point "before_release_20260728_1140z" LOG: recovery has paused
Checkpoint: Recovery pauses exactly at the intended target
sudo -u postgres psql -XAtqc "SELECT pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_replay_lsn();"Continue whenRecovery true, pause true, replay LSN/time/name matches plan, logs show target reached without gap/corruption, and host remains isolated.
Stop whenServer becomes writable, overshoots/ends before target, requests unavailable WAL, reports corruption/timeline mismatch, fills disk, or side-effect isolation changes.
If this step fails
Recovery says target was not reached.
Likely causeArchive ended early, target/timeline is wrong, or required WAL is missing.
Preserve logs and compare exact last replay/requested WAL with signed plan/catalog.
ResolutionStop and rebuild from clean backup after correcting chain or target; do not promote the partial candidate.
Recovery pauses at an unexpected LSN.
Likely causeTarget name reused, timeline differs, or plan/config drifted.
Compare config digest, restore-point WAL evidence and timeline history.
ResolutionReject candidate, use a unique target/evidenced timeline and rerun from clean base.
Security notes
- Protect recovery logs because SQL/filenames/timestamps can expose incident and data history.
Alternatives
- Use recovery_target_action=shutdown for unattended replay, then validate after controlled restart; pause is clearer for interactive acceptance.
Stop conditions
- Server becomes writable, overshoots/ends before target, requests unavailable WAL, reports corruption/timeline mismatch, fills disk, or side-effect isolation changes.
verification
Validate database and application state while recovery is paused
Connect only with a dedicated read-only validation role through the isolated path. Verify system identifier, recovery/pause state, expected good marker, absence of unwanted marker, schema/extension versions, constraints, representative counts/totals, latest timestamps, sequences, and application read-only smoke tests. Keep outbound jobs disabled.
Why this step matters
PostgreSQL crash consistency does not prove business correctness. The candidate may stop at the wrong transaction, omit valid concurrent work, contain unwanted rows, have stale sequences, or fail because an extension/application expectation changed. Validate against a predeclared matrix and compare with incident evidence. Queries must be read-only and bounded; a paused recovery snapshot can be affected by long queries and resource pressure, and side-effect-capable application code must remain disconnected from external services. Capture results, query versions and approvers so promotion is a defensible decision.
What to understand
Prove recovery and pause state before and after validation; never run DDL/DML against the candidate.
Verify good marker present and unwanted event absent with transaction/LSN/timestamp evidence.
Check schema migrations, extension versions, constraints, row counts, domain totals, latest timestamps and critical sequences.
Run application read-only smoke tests with email/payment/webhook/queue/network side effects physically blocked.
Have database and application/data owners sign the result and quantify known good transactions lost after target.
System changes
- None; runs bounded read-only validation and writes evidence outside the database.
Syntax explained
paused recovery- Stable review point that remains read-only until explicit promote/resume decision.
business invariant- Application-owned condition that distinguishes correct data from merely consistent storage.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d recovery_fixture -c "SELECT pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_replay_lsn();" -c "SELECT id,state,created_at FROM pitr_probe ORDER BY created_at;" pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_replay_lsn
-------------------+-------------------------+------------------------
t | t | 4B/26004C10
id | state | created_at
--------------+-------+-------------------------------
20260728-a | good | 2026-07-28 13:29:58.802+00
(1 row)
PASS unwanted marker 20260728-b absent
PASS 24 business invariants
PASS read-only application smoke testCheckpoint: The candidate matches the accepted business recovery point
./operations/validate-recovery --plan recovery-plan.json --database-url service=recovery_readonly --require-no-side-effectsContinue whenAll database/application invariants pass, unwanted event absent, expected loss quantified/approved, recovery remains paused and isolated, and two owners sign.
Stop whenAny invariant fails, unwanted change exists, expected good state is missing beyond approved RPO, validation writes or external side effects occur, or ownership disagrees.
If this step fails
Constraints pass but financial/domain totals differ.
Likely causeTarget excludes concurrent valid transactions or business event ordering differs from the assumed timestamp.
Compare audit ledger, transaction commits and candidate targets without modifying this restore.
ResolutionBuild another isolated candidate at an evidenced point or plan logical reconciliation with owners.
Application smoke test tries to send external messages.
Likely causeFeature flags/network isolation/job disabling is incomplete.
Block the destination, preserve attempt logs and inventory the initiating component.
ResolutionFix isolation and reset the candidate if the test modified local state; never allow the production side effect.
Security notes
- Validation outputs may contain sensitive records; store aggregates/redacted evidence and restrict raw access.
Alternatives
- Export selected tables from candidates for offline comparison when application code cannot safely run in isolation.
Stop conditions
- Any invariant fails, unwanted change exists, expected good state is missing beyond approved RPO, validation writes or external side effects occur, or ownership disagrees.
warning
Promote only the approved candidate and establish a new timeline
After signed acceptance, call promotion once, verify recovery ended and a new timeline was created, keep production traffic closed, perform a synthetic write/read and full post-promotion invariants, then configure archiving/backups for the new timeline before any client cutover.
Why this step matters
Promotion crosses the principal rollback boundary: the candidate becomes writable and begins a new timeline. Before promotion, the team can discard it and choose another target. After new writes, returning to the old cluster or another candidate is a new recovery/failover requiring reconciliation. PostgreSQL removes recovery signaling as it completes and writes timeline history that must enter the archive. Do not route clients until the new timeline is archived, synthetic writes/invariants pass, backup tooling recognizes authority, and the old production cluster is fenced/preserved.
What to understand
Verify signed target acceptance, exact node/data directory, old production fence and continued side-effect block.
Promote once, wait boundedly, inspect recovery state, transaction read-only and timeline/control/log evidence.
Commit a synthetic post-promotion marker and verify readback, constraints, extensions and application invariants.
Force/archive a WAL switch and verify new timeline `.history` and segment through recovery identity.
Schedule an immediate new base backup so future recovery has a tested starting point on the new timeline.
System changes
- Ends recovery, creates a new timeline and enables writes on the recovered cluster.
Syntax explained
pg_promote- Ends recovery; it does not validate business state, archive, routing or side effects.
timeline history file- Records branch origin and is required for future recovery across timelines.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -c "SELECT pg_promote(true,60);" -c "SELECT pg_is_in_recovery(),current_setting('transaction_read_only'),pg_current_wal_lsn();"pg_promote ------------ t pg_is_in_recovery | transaction_read_only | pg_current_wal_lsn -------------------+-----------------------+-------------------- f | off | 4B/26010088 LOG: selected new timeline ID: 2 LOG: archive recovery complete
Checkpoint: The new timeline is writable, archived, and recoverable
sudo -u postgres psql -XAtqc "SELECT NOT pg_is_in_recovery(), current_setting('transaction_read_only')='off'; SELECT pg_switch_wal();" && operations/archive-audit --require-latest-timelineContinue whenOne approved node writable, new timeline/history archived, post-promotion marker/invariants pass, old source fenced, and new base backup scheduled.
Stop whenApproval or node identity is ambiguous, archive/history fails, synthetic write/invariants fail, old production is reachable, or backup tooling targets wrong cluster.
If this step fails
Promotion times out but logs show activity.
Likely causeRecovery completion is delayed or target node/process is not the expected one.
Query state once, inspect startup/log/control data and retain all fencing.
ResolutionDiagnose without promoting another candidate or reopening old production.
Timeline history is not archived.
Likely causeArchive configuration/credential/prefix did not follow recovered configuration.
Inspect `pg_stat_archiver`, wrapper logs and new timeline filename.
ResolutionKeep client traffic closed and repair/verify new timeline archive before cutover.
Security notes
- Promotion requires tightly scoped, time-limited administrative access and dual approval.
Alternatives
- Keep candidate paused and export selected data for logical repair when cluster-wide promotion would discard too much valid work.
Stop conditions
- Approval or node identity is ambiguous, archive/history fails, synthetic write/invariants fail, old production is reachable, or backup tooling targets wrong cluster.
verification
Cut over clients gradually and reset the recovery chain
Move the writer endpoint to the promoted cluster only after old production is fenced, recycle connection pools, enable canary traffic, monitor correctness and side effects, then take and verify a new base backup on the new timeline. Retain original source and old chain until acceptance and policy allow release.
Why this step matters
Client cutover is when the recovered state begins interacting with current external reality. Pools and cached endpoints can keep old connections, while re-enabling schedulers can duplicate work that occurred after the recovery point. Bring traffic and side effects back in explicit stages, reconcile lost interval events, and watch domain invariants. The old base backup plus branched WAL remains useful evidence, but a fresh verified backup on the promoted timeline reduces future recovery complexity and proves the new archive path. Do not delete the original incident cluster merely to save space during an unstable cutover.
What to understand
Verify endpoint has exactly one backend and old source rejects connections from every client segment.
Recycle connection pools and confirm sessions in `pg_stat_activity` originate only from approved applications.
Enable a small read/write canary, then staged traffic with errors, latency, locks, WAL, archive and domain monitoring.
Re-enable jobs/side effects one class at a time after reconciliation of the lost interval and duplicate-protection checks.
Take/verify/catalog a new base backup and recovery read; preserve old cluster/chain under incident retention.
System changes
- Routes production clients and side-effect jobs to recovered state; creates a new base backup.
Syntax explained
staged canary- Limits blast radius while real clients exercise recovered data and dependencies.
new base backup- Establishes a current restore anchor on the promoted timeline.
./operations/move-writer-endpoint --target recovery-a --require-old-fenced && ./operations/canary-traffic --percent 5 --duration 10m && operations/create-and-verify-base-backup --reason post-pitr-timeline-2PASS one writer backend: recovery-a PASS 5% canary, error rate 0.00%, invariant checks 24/24 PASS background jobs enabled in reviewed order PASS timeline 2 WAL archived PASS post-PITR base backup verified: 2026-07-28T15:21:44Z
Checkpoint: Service and recovery protection are restored
./operations/post-pitr-acceptance --require-single-writer --require-new-verified-backup --require-old-fencedContinue whenStaged traffic/invariants healthy, jobs reconciled, one writer, new timeline archive/base backup verified, and original evidence retained.
Stop whenAny client reaches old source, invariant/error/side-effect threshold fails, new archive/backup chain fails, or reconciliation owner is absent.
If this step fails
A background job repeats post-target work.
Likely causeScheduler state was recovered to before its completion and external deduplication is absent.
Pause job, inspect idempotency keys/audit/external system state and recovery-loss interval.
ResolutionReconcile with application owner and add durable idempotency before re-enabling that job class.
New base backup uses the old archive/catalog identity.
Likely causeBackup automation cached cluster/timeline/endpoint metadata.
Compare system identifier, timeline, endpoint and catalog entry before retention changes.
ResolutionReject the backup entry, correct automation and create a new verified post-PITR backup.
Security notes
- Review credentials restored from backup; rotate those exposed or reverted by the recovery point.
Alternatives
- Use a controlled blue/green proxy cutover with rapid return only before any new writes and while original state remains authoritative.
Stop conditions
- Any client reaches old source, invariant/error/side-effect threshold fails, new archive/backup chain fails, or reconciliation owner is absent.
verification
Repeat the full restore and retain measurable evidence
Run the procedure on schedule and after material changes, including injected archive gap, corruption, wrong timeline, missing key, full disk and side-effect attempt. Record transfer/replay/validation durations, RPO, target accuracy, every failed gate, and remediation. A backup remains unverified when its chain has not been restored.
Why this step matters
Recovery readiness decays when database size, WAL rate, extensions, repository, encryption keys, network, staffing or application invariants change. A recurring drill measures actual transfer, replay, validation and decision time. Failure injection proves controls fail closed instead of silently selecting another object or promoting partial state. Each retained backup need not be individually promoted, but retention policy must distinguish verified chains from untested artifacts. Evidence should be tamper-evident and contain enough metadata to reproduce the result without copying sensitive rows.
What to understand
Select a backup and target according to production policy rather than the smallest/easiest fixture.
Restore on equivalent capacity and use realistic WAL volume, tablespaces, extensions and encrypted repository path.
Inject one failure at a time and prove recovery stops with an actionable diagnostic and no promotion.
Measure phase durations, bytes/rates, RPO, RTO, owner response and validation completeness.
Track remediation to closure and rerun affected gates; update procedures/version/source review.
System changes
- Creates and destroys only approved isolated recovery candidates and evidence.
Syntax explained
failure injection- Controlled proof that gaps, corruption and unsafe topology halt the procedure.
measured RTO- Observed end-to-end recovery duration including validation and routing, not transfer alone.
./operations/run-pitr-drill --latest-eligible-backup --inject-tests gap,corrupt,wrong-timeline,missing-key,full-disk,side-effect --output pitr-drill-20260728.jsonPASS valid-chain restore and target pause PASS good marker present; unwanted marker absent PASS gap/corruption/wrong timeline/missing key/full disk stopped recovery PASS outbound side effect blocked Measured RPO 00:02:11 Measured RTO 00:54:37 Evidence pitr-drill-20260728.json signed
Checkpoint: Current backups have current restore evidence
jq -e '.valid_restore==true and .unsafe_false_positive_count==0 and .rpo_seconds<=300 and .rto_seconds<=7200 and .remediation_open==0' pitr-drill-20260728.jsonContinue whenValid chain restores to exact target, all unsafe injections stop, side effects blocked, RPO/RTO met, evidence signed, and remediation closed.
Stop whenAny gap/corruption can pass, promotion occurs automatically, secrets/raw sensitive data leak, RPO/RTO fail without acceptance, or remediation remains open.
If this step fails
A corrupted segment is accepted.
Likely causeRestore wrapper or repository lacks authenticated integrity comparison.
Quarantine the chain and compare immutable checksums, encryption/authentication and wrapper verification.
ResolutionFix validation, establish a new clean archive chain and rerun before claiming recovery readiness.
Drill completes but exceeds RTO.
Likely causeTransfer, replay, storage, validation or approval is slower than planned.
Analyze per-phase timings and resource saturation.
ResolutionImprove the bottleneck or update the agreed objective with business owners, then repeat measurement.
Security notes
- Use synthetic invariant details in broad reports and restrict raw restored data/evidence to recovery personnel.
Alternatives
- Use continuous automated restore verification plus periodic human-led target selection and promotion rehearsal.
Stop conditions
- Any gap/corruption can pass, promotion occurs automatically, secrets/raw sensitive data leak, RPO/RTO fail without acceptance, or remediation remains open.
Finish line
Verification checklist
operations/archive-audit --cluster-id 7589916241639021840 --verify-continuity --test-recovery-readNo gaps/conflicts; newest forced segment is independently retrievable with matching digest and encryption.sudo -u postgres pg_verifybackup {{baseBackup}}Manifest, files, checksums and required WAL ranges verify for the selected pre-target backup../operations/validate-recovery --plan recovery-plan.json --require-paused --require-no-side-effectsGood state present, unwanted state absent, business invariants pass, and recovery is paused/read-only../operations/post-pitr-acceptance --require-single-writer --require-new-verified-backup --require-old-fencedOne new timeline serves clients and has a verified archive plus new base backup while original evidence remains protected.Recovery guidance
Common problems and safe checks
`pg_stat_archiver` stops advancing while WAL accumulates.
Likely causeArchive destination, credentials, capacity, network, wrapper, or duplicate comparison is failing.
Inspect last failed WAL/time and PostgreSQL/wrapper logs.Test destination health and capacity without returning false success.
ResolutionRepair the durable path, rearchive pending segments automatically, and retrieve/compare one segment before clearing the incident.
A WAL destination object already exists with different content.
Likely causeCluster prefixes collided, repository corruption occurred, or a filename was reused across the wrong system identity.
Quarantine both objects and compare checksums/system identifiers/timeline provenance.
ResolutionFail closed and investigate; never overwrite a conflicting WAL filename.
Recovery requests a WAL file that is not in the archive.
Likely causeArchive has a real gap, wrong cluster/timeline prefix is selected, retention deleted required WAL, or restore command masks errors.
Record the exact filename and inspect backup start/end LSN plus timeline history.Search only authenticated catalog locations and primary evidence.
ResolutionSelect another complete backup chain or change the target only with business approval; do not fabricate or source WAL from an unrelated cluster.
Recovery reaches end of available WAL before the requested target.
Likely causeTarget is after archive coverage, target timezone is wrong, or required segment is not yet/durably archived.
Compare target UTC, last archived segment/LSN, logs, and archive catalog.
ResolutionKeep the cluster isolated, supply verified missing WAL if it exists, or select an evidence-backed earlier target and restart from a clean base copy.
Recovered data contains the unwanted change.
Likely causeTarget was inclusive, timestamp evidence was late, timeline was wrong, or the harmful transaction committed before the assumed point.
Review audit/transaction evidence and test an earlier target in a fresh restore.
ResolutionDiscard the candidate and rerun recovery from the untouched backup with corrected target semantics.
Recovered data omits expected good transactions.
Likely causeTarget is too early, archive chain is incomplete, or the wrong timeline/backup was chosen.
Compare marker/transaction/LSN evidence and application audit logs.
ResolutionRecover a new candidate to a later evidenced target without modifying the first candidate.
PostgreSQL starts normally instead of entering recovery.
Likely cause`recovery.signal` is absent/misplaced or the service uses another data directory.
Stop immediately and resolve live service `-D`, signal path, config path, and logs.
ResolutionDiscard any writable-test changes, restore a clean base copy, recreate signal/config, and start isolated.
Recovery pauses but clients cannot validate read-only data.
Likely causeNetwork isolation is too broad, authentication/configuration is missing, or recovery has not reached consistency.
Use a local socket first, inspect recovery state/logs, then allow only the validation subnet/role.
ResolutionAdd a narrow read-only validation path without enabling production routing or side effects.
Reference
Frequently asked questions
Is a successful archive command proof that PITR works?
No. It proves only that the command returned zero. PITR requires durable identical storage, a verified base backup, every needed WAL/history file, compatible restore infrastructure, correct target semantics, and an actual recovery test.
Should recovery_target_action be promote?
Not for an incident recovery awaiting validation. Use pause, inspect the read-only target and business invariants, then promote separately after approval.
Can the newest base backup always be used?
Only if its backup end is at or before the chosen target. A backup whose consistency point is after the target cannot recover backward.
Recovery
Rollback
Before promotion, stop and discard only the disposable candidate, correct the target or chain, and restore again from the immutable base backup; the original production cluster remains the rollback authority if it has not been superseded. After promotion and new writes, there is no simple rollback to the old timeline. Keep old production fenced, preserve both histories, and treat any return as a new recovery or logical reconciliation with explicit loss analysis.
- Before promotion, stop the recovery instance and retain logs/config/target evidence.
- Never resume backward; allocate a clean target and restore the untouched base backup again.
- Correct exactly one target, timeline, archive gap, credential or invariant issue and rerun all gates.
- If production was never superseded, reopen it only after confirming singular writer authority and incident safety.
- After promotion, declare the new timeline authoritative and keep old production isolated.
- Reconcile any post-promotion writes before considering another recovery/cutover.
- Maintain both backup/WAL chains until acceptance, security/legal investigation and new verified base backup are complete.
- Rotate credentials and external idempotency state reverted by the selected recovery point.
Evidence