A Successful Backup Job Can Still Produce an Unrestorable Database
Sources and verification
- Source dates
- Oldest source check: 12 September 2026.
- Technical verification
- Separate technical verification has not been recorded.
Publication approval and technical verification are recorded separately. Automated link checks establish reachability, not accuracy. A checked example verifies only its stated test cases, not the whole article or your production setup.
In short
Use a PostgreSQL-supported logical or physical backup method, then restore selected backups into an isolated environment and test the required data and application behavior. Archiving a changing live data directory with ordinary tar does not establish consistency. Track backup completion separately from restore verification, and record the exact artifact, validation scope, and measured recovery time.
Key takeaways
- Ordinary file copies of a running PostgreSQL cluster are not automatically consistent backups.
- A dump listing or checksum is weaker evidence than a successful restore.
- Restore drills must use isolated databases with production integrations disabled.
Choose the backup method before adding a success signal
A backup script can finish with exit code zero after archiving files that never represented one consistent database state. PostgreSQL's file-system backup documentation (opens in a new tab) describes the consistency requirements; a plain archive of a changing live data directory is not a supported shortcut around them. Use an appropriate supported physical backup procedure or a logical dump for the actual recovery requirement.
pg_dump (opens in a new tab) can produce a consistent logical backup of one database. That is not automatically the whole deployment: inventory required roles, tablespaces, extensions, configuration, external objects, and any cross-database consistency requirements. If point-in-time recovery is required, design and validate the corresponding physical backup and WAL retention strategy rather than assuming a daily logical dump provides it.
Keep two independent records
The completion record should identify the source database, backup method and tool version, artifact checksum, encrypted storage location, and finish time. Advance that record only after the artifact is successfully stored and accessible through the intended recovery path.
The restore record should identify the exact artifact tested, target engine version, restoration start and finish, validation checks, and result. A checksum proves bytes match. A successful archive listing proves the metadata can be read. Neither establishes that the required database can be restored and used.
Restore into a deliberately isolated target
Prepare a separate restore host or isolated cluster with no production database credentials or network route to production services. Disable outbound email, webhooks, scheduled workers, and external integrations before starting an application against the restored data. Use only trusted backup artifacts and restrict access to the restored information.
For a custom-format dump, pg_restore (opens in a new tab) is the relevant restore tool. The following example assumes you have already provisioned an empty test database on a dedicated local PostgreSQL instance at port 55432. Confirm that identity first; these commands are exclusively for the isolated restore host.
psql 'host=127.0.0.1 port=55432 dbname=restore_drill user=restore_drill' \
--command='SELECT current_database(), inet_server_addr(), inet_server_port();'
pg_restore --exit-on-error --no-owner --no-privileges \
--dbname='host=127.0.0.1 port=55432 dbname=restore_drill user=restore_drill' \
/secure/restore-copy.dump
Use a restricted password file or the environment's approved authentication mechanism. The test intentionally omits ownership and privilege restoration, so it verifies data and schema within that reduced scope. A complete recovery exercise must separately verify the actual roles, privileges, required extensions, and application identity. Do not describe this reduced example as a complete disaster-recovery certification.
Validate facts that matter to recovery
| Check | Evidence to save |
|---|---|
| Restore completes without ignored errors | Exit status and restricted restore log |
| Expected schema and extensions exist | Schema and extension inventory |
| Known records from the backup period exist | Controlled fixture or scoped data assertion |
| Critical relationships and indexes exist | Application-specific integrity checks |
| Application can read expected data | Isolated smoke-test result |
| Recovery finishes in time | Measured end-to-end elapsed time |
Select older retained artifacts as well as the newest one, and exercise retrieval and decryption from the real backup destination. Otherwise the drill may depend on a local copy that would disappear with the source host.
Keep missing verification visible
Alert separately on stale backups and overdue restore exercises. If restoration fails, retain the failing artifact and logs for investigation; do not erase the evidence by overwriting the only copy. Fix the backup method, create a new artifact, and repeat the isolated restore before declaring recovery capability established. A fresh success heartbeat should never hide three months of untested recovery assumptions.
Did this help?
Your answer helps us improve this guide. We save only the page and your choice for 30 days.
No name, email, or incident details are requested.
Sources
Vendor facts change. Each source below shows the date this page last checked it.
- PostgreSQL file system level backup — PostgreSQL. Checked 12 September 2026.
- PostgreSQL pg_dump reference — PostgreSQL. Checked 12 September 2026.
- PostgreSQL pg_restore reference — PostgreSQL. Checked 12 September 2026.
Related
One practical idea, occasionally
The On-Call Brief: short field notes, templates, and operational lessons.
Follow the field guide
Email subscriptions are not open yet. Read new guides in your feed reader — no email address needed.
Subscribe with RSS