To test a Supabase database backup, restore it into a clean, isolated target with errors treated as fatal. Compare schemas, roles, row counts and sequences with an inventory captured at backup time, then run application checks. Record the restore result and duration for each test.
Why a valid dump can still fail you
A dump may be readable and still be operationally useless. Missing roles can break ownership. Absent extensions can stop schema creation. A version mismatch can surface incompatible syntax. A restore may log errors, continue, and leave behind a database that looks populated but is incomplete.
PostgreSQL documents that pg_dump creates a consistent snapshot without blocking readers or writers. It also documents the boundary: pg_dump exports one database and does not include cluster-wide roles and tablespaces. A restore plan must account for everything outside that boundary.
1. Capture a source inventory
Before restoring, record what “complete” means. At minimum, capture the server version, extensions, schemas, tables, sequences, views, functions, row counts, and ownership expectations. For products built on Postgres, add domain checks such as user counts, tenant counts, or object inventory.
Take the inventory at the same logical point as the backup. Comparing a restored nightly dump with a live database the next afternoon produces noise, not evidence. Store the source inventory beside the backup artifact and identify both with the same run ID.
2. Restore into a clean, isolated target
A clean target exposes hidden dependencies. Restoring repeatedly into a long-lived test database can succeed only because an extension, role, or table survived a previous run. Provision a disposable database for each verification and remove it afterward.
- Match the production PostgreSQL major version unless you are deliberately testing an upgrade.
- Block application traffic and outbound integrations from the verification environment.
- Use temporary credentials and never send production emails, webhooks, or jobs.
3. Make restore errors fatal
For a custom-format archive, use pg_restore with an error policy that cannot silently leave a partial result. PostgreSQL’s --single-transaction option executes the restore as one transaction and implies exit-on-error: either all commands complete or none are applied.
If the database is too large for one transaction, fail on the first SQL error, keep the logs and throw the target away after a failure. The exact flag matters less than refusing to call a partial restore successful.
4. Compare structure and data
Start with inventory because it catches broad omissions quickly. Compare schemas, relations, extensions, and row counts. Then add checks that represent business value. A table count cannot tell you whether the latest subscription exists or whether a restored user can access the correct tenant.
- Schema objects: names and types, with expected exclusions documented.
- Data: row counts plus targeted invariants and representative records.
- Security: roles, ownership, grants, and row-level-security policies.
- Sequences: next values must not collide with restored rows.
- Large objects and external files: count, size, and checksum where applicable.
5. Run application-level recovery checks
Database equivalence is necessary but not sufficient. Point a safely isolated application build at the restored target and exercise a short recovery script: authenticate, read a core record, perform a transaction, verify permissions, and retrieve a file if the product uses object storage.
Keep the smoke test small and deterministic. Its purpose is to catch broken contracts between the application and the restored state, not to replace the full test suite.
6. Measure and retain evidence
Record artifact identity and checksum, source and target versions, start and finish times, restore logs, inventory differences, smoke-test results, and the final disposition. This turns “we tested it once” into evidence that can be reviewed after a change or incident.
Track restore duration over time. A backup that restores correctly in six hours still fails a two-hour RTO. Growth in recovery time is an operational signal, just like growth in database size.
Check the target version after a PostgreSQL upgrade
Supabase’s PostgreSQL 15.19 and 17.11 release notice lists four conditional changes: two for ltree indexes, one for btree_gist indexes on floating-point columns and one for legacy pgcrypto ciphers. It also limits how custom operators with a selectivity estimator can be recreated. Run its detection queries for the project before upgrading; a passing row-count comparison alone does not rule them out.
- Custom operators with a non-built-in selectivity estimator can fail to recreate during a logical restore on the new minor version because Supabase roles are not superusers. Treat that SQL error as a failed restore. Use the official detection query and remove the estimator or contact Supabase support before relying on the dump.
- Existing ltree indexes and btree_gist indexes on floating-point values containing NaN may return incomplete or wrong results after the upgrade. Use Supabase’s conditions and index queries to identify affected indexes, then reindex them on the upgraded source. A fresh scratch restore builds new indexes and cannot prove that old source indexes were rebuilt.
- If the application used legacy pgcrypto PGP ciphers such as blowfish or cast5, test decryption on the target version with a controlled sample. A successful restore and matching row counts do not prove that the values can still be decrypted. Follow Supabase’s migration instructions for affected ciphertext.
Run the next restore drill against the PostgreSQL minor version planned for recovery and keep its log. Separately verify application queries against the upgraded source, because the restored target and the existing source can fail in different ways.
How often should you test?
Automate a structural restore test for every backup when the database size and cost make that practical. Run deeper application recovery drills on a slower schedule and after meaningful changes to versions, extensions, backup tooling, or infrastructure. The test frequency should follow the rate at which your recovery assumptions can change.
A result you can trust
Use three outcomes: verified, failed, or verified with explicitly accepted differences. Never turn unexplained differences into success just to keep the dashboard green. Expected exclusions should be versioned policy; everything else deserves investigation.
I build ReviveDB, which runs this restore-and-compare check on every Supabase database backup. This post was first published on the ReviveDB blog.
Top comments (0)