Backend

Your Backup Isn't Real Until You Restore It to a Temp Database and Check

By 一只牛博 · Aug 22, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Most teams discover their backups are broken only during an incident. A restore-to-temp-database verification routine catches empty files, missing constraints, and data corruption before they become a production outage, and it costs nothing except a few minutes of scripted checks.

Summary

Scheduled backup scripts and green monitoring dashboards create a false sense of security. The gap between a backup that "ran successfully" and one that "actually works" is a restore nobody performs until disaster strikes — and that's when people discover empty files, missing objects, or version mismatches. A systematic verification workflow closes this gap: capture a business-metric baseline before backup, inspect the archive's table of contents with `sys_restore -l`, restore to a dedicated temporary database, reconcile aggregate data against the baseline, confirm every constraint and index is present and enforced, and record file size and a SHA-256 hash for traceability. The hash proves file integrity over time but does not prove restorability — only the full restore-and-check loop does that.

Takeaways

— Capture a business-metric baseline (row count, sum of a monetary column, max timestamp) from the source database before running the backup; row counts alone miss shifted or missing values.

— Use `sys_restore -l` on a custom-format dump to inspect the archive's table of contents before restoring — it confirms the backup isn't an empty shell and lists every object inside.

— Always restore to a newly created temporary database, never onto the source database, to avoid accidental overwrites.

— A successful restore command (`-v` with no errors) is a necessary but insufficient condition; it does not guarantee data matches the source.

— Reconcile aggregate business metrics between source and restored database — matching row counts, sum totals, and timestamps confirms data integrity.

— Explicitly check that tables, views, constraints, and indexes are present in the restored database; constraints can be defined but not enforced if something went wrong during restore.

— Deliberately insert a row that violates a foreign key or check constraint to prove the constraint is actively enforcing, not just defined in the catalog.

— Record the backup file's size and SHA-256 hash for later integrity checks, but understand the hash only proves the file hasn't changed — it does not prove the database inside can be restored.

Conclusions

Backup verification is almost never automated because the restore step feels destructive and teams fear touching production; a temporary database sidesteps that fear entirely.

The distinction between a constraint being defined in the catalog and a constraint actually blocking invalid writes is a subtle failure mode that static checks miss — only a live insert test catches it.

File-level integrity checks like SHA-256 are commonly mistaken for proof of backup validity, but they answer a different question: file tampering, not database restorability.

Custom-format dumps (`-F c`) enable pre-restore inspection via `sys_restore -l`, a capability plain-text SQL dumps lack without manual parsing — format choice directly affects verifiability.

Concepts & terms

sys_restore -l (TOC listing)

Lists the Table of Contents of a custom-format PostgreSQL/KingbaseES dump file without performing a restore. Shows every object in the archive — schemas, tables, sequences, views, constraints, indexes, and data entries — allowing pre-restore validation that the backup is structurally complete.

Custom format backup (-F c)

A compressed, flexible backup format in PostgreSQL-compatible databases that supports selective restore, parallel restore, and inspection via `sys_restore -l`. Contrasts with plain-text SQL dumps, which are human-readable but lack these capabilities.

Business-metric baseline

A set of aggregate values (row counts, sum of monetary columns, max timestamps) captured from the source database before backup. Used after restore to verify data integrity beyond simple row counts, which can match while individual values are shifted or missing.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗