What is pgBackRest restore testing, and why isn't a successful backup enough?
pgBackRest restore testing means restoring a real backup onto an isolated host, replaying archived WAL up to a chosen recovery target, and then proving the resulting cluster starts and serves correct data. The output is evidence that recovery works. A green backup job only shows that files were written to a repository.
pgBackRest includes two commands that are useful but often mistaken for restore tests:
| Check | What it confirms | What it does not prove |
|---|---|---|
pgbackrest check |
The configuration is valid and WAL archiving is working, by forcing a WAL switch and confirming the segment reaches the repository | That any existing backup can be restored |
pgbackrest verify |
Backup files and archived WAL in the repository match their recorded checksums | That PostgreSQL can start from them, replay WAL to a target, or return correct query results |
| Scheduled restore test | The cluster starts, recovery reaches the target, and the data passes validation | Nothing about future backups. It covers only the backup that was tested |
The gap matters because restores tend to fail for reasons the repository checks never examine. Common examples are a missing encryption passphrase on the recovery host, a WAL gap that sits outside the range you checked, a tablespace path that doesn't exist on the new machine, an extension that isn't installed there, or a recovery target that falls before the backup finished.
Any team that relies on pgBackRest to meet stated recovery time or recovery point objectives needs this testing. It also saves engineering time. A routine run surfaces a broken restore path during working hours, when a fix might take an afternoon. The alternative is finding the same problem during an outage, under pressure, with production down.
How does a scheduled restore test work end to end?
A scheduled restore test is a short-lived pipeline. Every stage either produces evidence or fails loudly.
- Trigger. A scheduler (cron, a CI pipeline, or a workflow engine) starts the job and records a run ID so results can be traced later.
- Provision. An isolated host or container is created with the same PostgreSQL major version as production and the same extensions installed. A physical backup will not start under a different major version, and missing extension libraries surface as errors when the affected objects are used.
- Restore. pgBackRest reads the selected backup from the repository and configures the cluster to fetch WAL from the archive through
restore_command. - Recover. PostgreSQL replays WAL up to the chosen target and then promotes.
- Validate. Checks run against the promoted cluster.
- Report. Durations, the recovered timestamp, and pass/fail results are sent to monitoring.
- Destroy. The environment is torn down, so no stale copy of production data is left behind.
Isolation is a correctness requirement
The restored cluster contains real data and believes it is production. It needs read access to the repository and nothing else. It must have no network route to production databases, application hosts, or anything that could receive writes or replication traffic from it.
The archive needs the same care. After promotion, the cluster starts a new timeline. If archive_command is still enabled and the pgBackRest configuration points at the production stanza with write credentials, the test cluster will push WAL from that divergent timeline into the production archive. To prevent this, disable archiving on the restored cluster and give the test host repository settings that are read-only or point somewhere else entirely.
+-------------+ 1 trigger +------------------------------------------+
| Scheduler |------------->| ISOLATED RESTORE TARGET (ephemeral) |
| cron/CI/WF | run ID | same PG major version + extensions |
+-------------+ | |
| 2 provision host/container |
+--------------------+ | 3 pgbackrest restore |
| pgBackRest repo |------>| restore_command fetches WAL |
| (backups + WAL) | read | 4 replay WAL to target, promote |
| | only | -> new timeline |
| |<--X---| archive_command disabled; repo creds |
+--------------------+ no | read-only or a separate repo |
push | 5 run validation checks |
| 6 emit metrics -------------------+ |
| 7 destroy environment | |
+------------------------------------|-----+
X no network route |
| v
+--------------------+ | +-------------+
| Production DBs / |<------X--------+ | Monitoring |
| application hosts | +-------------+
+--------------------+
Scheduled restore test pipeline: an isolated target restores from the repository with read-only access, cannot push WAL back or reach production, reports metrics to monitoring, and is destroyed after validation.
How do you run a pgBackRest restore for point in time recovery testing?
The restore itself is a single command, but the options you choose determine what the test proves.
- Pick the backup explicitly. With no
--set, pgBackRest restores the latest backup that can reach the target. Passing--setwith a label frompgbackrest inforestores a specific backup, so you can test sets other than the newest one. - Reuse the data directory with
--delta. On a persistent test host,--deltacompares the existing files against the manifest and replaces only what differs. This is much cheaper than wiping and copying everything. Without it, pgBackRest refuses to restore into a non-empty directory. - Recover to a point, not just to the end of WAL. Use
--type=timewith a--targettimestamp, or--type=lsnwith an LSN. Choose a point shortly before the test window. Replaying to that point shows that the archived WAL between the backup and the target is complete and can be applied. - Decide what happens at the target.
--target-action=promoteopens the cluster for verification queries. Usepauseif you want to inspect the cluster before promoting it. - Override archiving. Add
--archive-mode=offto the restore, or writearchive_mode = offinto the restored cluster's configuration. This stops the promoted test cluster from pushing WAL.
Rotate targets deliberately. If you always restore the newest backup, you only test that one set. Cycle through full, differential, and incremental sets. Also pick targets near the oldest WAL your retention policy still promises to keep.
This rotation adds orchestration work. The scheduler has to parse pgbackrest info, record which set and WAL range it tested last, and handle sets that expire mid-run.
#!/usr/bin/env bash
# Restore a chosen backup set to a point in time on a persistent test host.
set -euo pipefail
STANZA=main
PGDATA=/var/lib/pgsql/restore-test # test cluster, never production
SET_LABEL=20261101-010000F_20261105-010000I # from `pgbackrest info`; rotate F/D/I
TARGET_TIME='2026-11-08 23:45:00+00' # shortly before the test window
# Stop the previous test cluster so --delta can update files in place.
pg_ctl -D "$PGDATA" stop -m fast || true
# --set: test this specific backup, not just the newest one
# --delta: reuse the existing data directory, replace only changed files
# --type/--target: replay archived WAL up to a point, proving it is complete
# --target-action: promote so verification queries can run (or use pause)
# --archive-mode=off: the promoted test cluster must not push WAL to the repo
pgbackrest --stanza="$STANZA" \
--pg1-path="$PGDATA" \
--set="$SET_LABEL" \
--delta \
--type=time --target="$TARGET_TIME" \
--target-action=promote \
--archive-mode=off \
restore
# LSN-based alternative:
# --type=lsn --target='0/5A000028'
# Belt and braces: an explicit override that wins over earlier settings.
cat >> "$PGDATA/postgresql.conf" <<'EOF'
# restore-test override: never archive from this cluster
archive_mode = off
EOF
pg_ctl -D "$PGDATA" -l "$PGDATA/restore-test.log" start -w
# Expect 'f' once recovery reached the target and promoted.
psql -d postgres -Atc 'SELECT pg_is_in_recovery();'
A pgBackRest restore of a specific backup set into a reused data directory, recovering to a timestamp with --delta, --type=time, --target-action=promote and archiving disabled on the test cluster.
How do you prove the restored data is correct and usable?
Before checking any data, confirm that recovery actually finished where you intended. Query pg_is_in_recovery() and compare the result to your recovery_target_action. A paused cluster should still report true, and a promoted one should report false. Read the server log for the line saying where recovery stopped, and check that it matches your target time or LSN. Search the same log for missing-WAL and checksum errors. A restore that stopped early because a WAL segment was absent can still start up and accept connections, and it will look healthy until you compare it with the target.
Once recovery is confirmed, run data checks from cheapest to most expensive:
| Check | What it proves | Cost |
|---|---|---|
Row counts, max(id), max(updated_at) |
Recent writes are present; nothing is grossly truncated | Low |
| Canary row assertion | PITR stopped at the right point: the row written before the target exists, the row written after it does not | Low |
| amcheck on critical indexes | B-tree structure is consistent with itself | Medium |
pg_checksums --check |
Every page passes its checksum. Only works if data checksums are enabled, and requires a stopped cluster | High |
pg_dump to /dev/null |
Every table row can be read and decoded. Indexes are not covered | High |
| Application smoke queries | Real code paths, extensions, and permissions work | Varies |
The deeper checks catch corruption that counts never touch, but they scale with database size and can push the test past its scheduled window. Choose your checks knowing that narrow ones can pass while something they never read is broken: a table you didn't count, a missing extension, or a role the application needs.
#!/usr/bin/env bash
# Post-restore verification, cheapest checks first. Assumes recovery_target_action=promote.
set -euo pipefail
PGDATA=/var/lib/postgresql/16/restore-test
LOG=/var/log/postgresql/restore-test.log
export PGHOST=/var/run/postgresql PGPORT=5433 PGDATABASE=appdb PGUSER=postgres
q() { psql -X -v ON_ERROR_STOP=1 -tAc "$1"; }
fail() { echo "FAIL: $*" >&2; exit 1; }
# 1. Recovery finished as configured: promoted => false (a paused target would report true)
[ "$(q 'SELECT pg_is_in_recovery()')" = "f" ] || fail "cluster still in recovery"
# Print where recovery stopped so it can be compared with the target time/LSN
grep -E 'recovery stopping|consistent recovery state' "$LOG" || fail "no recovery stop line in log"
# A restore that ran out of WAL or hit bad pages can still accept connections
if grep -Ei 'checksum|could not (open|read|restore)' "$LOG"; then fail "WAL or checksum errors in log"; fi
# 2. Cheap: recent writes present, nothing grossly truncated
q "SELECT count(*), max(id), max(updated_at) FROM orders"
# 3. Cheap: canary rows written just before and just after the PITR target
[ "$(q "SELECT count(*) FROM restore_canary WHERE label = 'before_target'")" = "1" ] || fail "pre-target canary missing"
[ "$(q "SELECT count(*) FROM restore_canary WHERE label = 'after_target'")" = "0" ] || fail "post-target canary present"
# 4. Medium: B-tree consistency on critical indexes (true = also verify heap tuples are indexed)
q "CREATE EXTENSION IF NOT EXISTS amcheck"
q "SELECT bt_index_check('orders_pkey'::regclass, true)"
# 5. Application smoke query: exercises extensions and the app role's permissions
psql -X -v ON_ERROR_STOP=1 -U app_readonly -tAc "SELECT count(*) FROM orders WHERE created_at > now() - interval '1 day'"
# 6. High: read and decode every table row (indexes are not read)
pg_dump -f /dev/null "$PGDATABASE"
# 7. High: page checksums; needs checksums enabled and a cleanly stopped cluster, so it runs last
pg_ctl stop -D "$PGDATA" -m fast
pg_checksums --check -D "$PGDATA"
echo "restore verification passed"
A scheduled verification script that first confirms recovery stopped where intended, then runs data checks in increasing order of cost, with the offline pg_checksums pass last because it requires stopping the restored cluster.
How do you measure RTO and RPO evidence and alert on failures?
Record four numbers on every run and store them as time series, not just log lines:
- Wall-clock restore duration: from the start of the restore until files are in place.
- WAL replay duration: from server start until recovery completes and the instance accepts connections.
- Backup age: how old the restored backup was when the test ran.
- Recovery gap: the distance between the recovery target and the newest canary row you actually found.
The first two are your RTO evidence. The last two are your RPO evidence. Trends matter more than any single value. Data size and WAL volume grow slowly, so a restore can drift past its objective without any one run failing.
Illustrative estimate, assumed inputs: restoring 400 GB at 200 MB/s takes about 34 minutes. Replaying 50 GB of WAL at 30 MB/s adds about 28 minutes, so the total is roughly an hour. These durations are indicative only. A test host with different disks, CPU, or network path to the repository will not predict production RTO precisely.
Schedule the test with cron, a CI pipeline, or an orchestrator. Pick whichever one already has on-call ownership. Pair it with a dead-man's switch: the job pings a heartbeat check on success, and silence past the expected interval pages someone. A missed run must count as a failure. Otherwise a broken scheduler looks exactly like a healthy one. Send pass/fail status and the four metrics to your existing monitoring rather than to a separate dashboard nobody checks.
Finally, watch for slow decay. Common causes include expired repository credentials, the test host's PostgreSQL or pgBackRest version drifting from production's, a scheduler someone disabled during an incident, and a scratch volume that has quietly filled up.
#!/usr/bin/env bash
# Scheduled restore test: records RTO/RPO evidence and pings a dead-man's switch only on full success.
set -euo pipefail
STANZA=main
PGDATA=/srv/restore-test/pgdata # scratch volume, never production
export PGHOST=/tmp PGPORT=5433 # assumes the scratch instance listens here
TARGET="$(date -u -d '15 minutes ago' '+%Y-%m-%d %H:%M:%S+00')"
PROM=/var/lib/node_exporter/textfile/pg_restore_test.prom
HEARTBEAT_URL=""
# On any failure, publish pass=0 so monitoring sees a failure, not just a gap.
trap 'echo "pg_restore_test_pass 0" > "$PROM.tmp" && mv "$PROM.tmp" "$PROM"' ERR
# Backup age: seconds since the newest backup finished (RPO evidence).
stop=$(pgbackrest --stanza="$STANZA" --output=json info | jq '.[0].backup[-1].timestamp.stop')
backup_age=$(( $(date +%s) - stop ))
t0=$(date +%s)
pgbackrest --stanza="$STANZA" --pg1-path="$PGDATA" --delta \
--type=time --target="$TARGET" --target-action=promote restore
t1=$(date +%s) # files in place: restore duration ends
pg_ctl -D "$PGDATA" -l /srv/restore-test/pg.log -o "-p $PGPORT" start
until [ "$(psql -At -d postgres -c 'select pg_is_in_recovery()' 2>/dev/null)" = f ]; do
(( $(date +%s) - t1 > 7200 )) && { echo 'WAL replay timed out' >&2; false; }
sleep 5
done
t2=$(date +%s) # promoted and accepting writes: replay ends
# Recovery gap: recovery target minus newest canary row actually recovered.
gap=$(psql -At -d appdb -c "select extract(epoch from timestamptz '$TARGET' - max(ts))::int from monitoring.canary")
pg_ctl -D "$PGDATA" -m fast stop
# Publish all metrics atomically to the node_exporter textfile collector.
printf 'pg_restore_test_pass 1\npg_restore_test_restore_seconds %d\npg_restore_test_replay_seconds %d\npg_restore_test_backup_age_seconds %d\npg_restore_test_recovery_gap_seconds %d\n' \
$((t1 - t0)) $((t2 - t1)) "$backup_age" "$gap" > "$PROM.tmp" && mv "$PROM.tmp" "$PROM"
# Heartbeat last: if anything above failed or the job never ran, silence triggers the page.
curl -fsS -m 10 --retry 3 "$HEARTBEAT_URL" > /dev/null
A cron- or CI-driven bash job that times the pgBackRest restore and WAL replay, computes backup age and canary recovery gap, exports them as monitoring metrics, and pings a dead-man's-switch heartbeat only when every step succeeds.
What does restore testing cost, and when should you scale it back?
A full restore pulls the entire repository across the network, needs a host with disk at least as large as production, and keeps that host busy while WAL replays. In cloud object storage, cross-region or internet egress is often the largest line item. To estimate your cost, use this formula:
cost per run = (repo GB × egress $/GB) + (restore hours × instance $/hr) + (volume GB × storage $/GB-hr × hours)
Illustrative estimate (assumed numbers, not a benchmark): 1,500 GB repo × $0.09 = $135, plus 6 h × $2 = $12, plus 3,000 GB × $0.0001 × 6 h ≈ $2. That is about $149 per run, or roughly $600 a month at weekly cadence. If egress dominates, run the test host in the same region as the repository.
| Approach | What it proves | What it misses |
|---|---|---|
Frequent selective restore (one database via --db-include) |
Credentials, decryption, WAL fetch, scripts | Full-size timing, space exhaustion, other databases |
| Infrequent full restore | Real RTO, complete integrity | Regressions between runs |
The table shows why the two are not equivalent. Sampled restores do not measure your real RTO.
Restored data is unmasked production data:
- Repository access: Give the test host scoped, read-only access to the repository where the backend supports it.
- Encryption passphrase: Inject the repository passphrase at runtime from a secret store. Do not bake it into images.
- Access control: Restrict who can reach the test host.
- Masking: Masking after restore does not undo the exposure. The original values have already been written to disk and may also appear in logs.
- Cleanup: Destroy the volume after every run.
Reducing frequency is reasonable for stable, low-change systems. It is not reasonable after a major-version upgrade, a repository or storage migration, or a change to encryption settings. Weigh the monthly bill against the cost of discovering during an outage that restores take three times longer than you assumed.
What are the alternatives, and how does a DR drill differ from automated restore testing?
Automated restore testing is one layer of verification. The alternatives below each answer a narrower question.
| Approach | What it proves | What it misses |
|---|---|---|
check / verify only |
Archiving works and repository files are intact | Whether Postgres starts and replays to a consistent state |
| Standby replica | Fast failover from hardware or host loss | Logical corruption, DROP TABLE and bad migrations, which replicate immediately |
| Automated restore test | Backups restore, WAL replays and data passes assertions | Human access, cutover and runbook accuracy |
| Human-run DR drill | The team can recover production under pressure | Daily regressions between drills |
A drill tests the people and plumbing around the restore. It checks whether the runbook still matches reality and whether on-call engineers have working credentials for the repository and the target hosts. It also checks whether the team can move applications over through DNS or connection-string changes, and how long that takes. Drills should also cover repository loss: assume the primary repo is gone and restore from the secondary repo. Automation rarely exercises that path, and it is often the one most likely to have stale credentials.
Condensed checklist
- Restore into an isolated network with no route to production
- Override
archive_commandso the restored instance cannot push WAL into the live repo - Rotate restore targets across backups, repos and recovery times
- Confirm that recovery finished and the instance left recovery mode
- Layer validation: startup,
amcheck, row counts and application queries - Assert on canary rows written at known timestamps
- Export duration and recovery-point lag as metrics
- Alert on failures and on missed runs
- Use scoped, read-only, rotated repository credentials
- Tear down instances and scrub copied data after every run
- Hold human DR drills on a fixed cadence, such as quarterly
When to avoid it
Scheduled restore testing earns its cost only when the backups protect data you cannot rebuild and someone will act on a failure. Skip it, or defer it, in these cases.
The data is disposable
Some databases can be rebuilt faster than they can be restored: CI fixtures, preview environments, read models projected from an event log, or caches populated from another system of record. Warning sign: nobody can name a row that would be lost if the cluster vanished. Spend the effort on verifying the rebuild path instead.
pgBackRest is not your actual recovery path
If you run on a managed service that does not expose the WAL archive or physical backups, a pgBackRest pipeline you bolt on alongside it tests a recovery route you would never use in an incident. Test the provider's restore mechanism instead.
The basics are still broken
Restore tests sit on top of a working archive. If archive_command failures appear in the logs, WAL has gaps, or backups regularly fail to complete, a scheduled restore will simply fail every night and teach the team to ignore it. Fix archiving and backup stability first.
You cannot isolate the copy
A restore creates a full copy of production data. If the only available host is in a lower-trust network, shared with other teams, or outside your compliance boundary, the test creates the exposure it was meant to guard against.
Nobody owns the result
If failure alerts land in an unwatched channel or the on-call rotation has no runbook for "restore test failed," the schedule produces no protection.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to pgbackrest restore testing, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.