Test Your Database Restore Before You Need It

From the team behind aidrop.it — one workspace to build, host, and keep changing your code.

Test a database export by restoring it into an isolated database and running a real application task against it. Record which data you recovered and how long the work took. A successful download tells you that you have a file. The rehearsal tells you what you could recover after losing the original.

Start while the production database still works. You can compare records, find missing dependencies and ask the person who understands the schema. During an outage, you would have to answer those questions while customers wait. This is a useful next step after checking whether you can leave your host.

Define the loss you can tolerate

Choose how much recent work you could afford to lose, then choose how long the application could remain unavailable. Record both in ordinary units: minutes of orders and hours offline. Use those limits to decide how often someone must export the data and how much recovery work you need to automate.

A weekly export might suit a directory that changes twice a month. A booking application needs a different plan. Ask who holds the copy if your hosting account becomes unavailable, and who can read it if the person who downloaded it leaves. Keep credentials out of the recovery notes.

Restore into a separate destination

Use an empty, isolated test database with compatible PostgreSQL tooling and the extensions your application needs. Keep its connection details separate from production. Disable outbound mail, payments and scheduled jobs before starting an application against the restored data; a rehearsal should not send customers yesterday's receipts again.

For a plain SQL dump, PostgreSQL documents restoration with psql and an option to stop at the first SQL error. Custom-format archives use pg_restore. A per-database dump does not include cluster-wide roles, so check what else the destination needs. Follow the PostgreSQL SQL dump guide for the format you hold.

Check the application after the import

Open a known record through the application and follow its related data. Compare a small set of business totals with the source as of the export. Check an uploaded attachment, too: a database row containing a file URL does not include the file. Inventory where the application keeps its state before declaring the drill complete.

Record the application revision used for the test. Restoring last month's schema and running today's code can introduce another failure. Separate the time spent importing from the time spent fixing configuration, locating files and getting the application to answer. The second part belongs in your recovery estimate too.

Keep a record of one restore drill

Copy this worksheet into your repository and fill it in during the rehearsal. Link to the protected location of the export, without copying customer data into the document. Leave a failed check visible, with an owner and a next action, so the next person can finish the work.

RESTORE DRILL
Date / operator:
Maximum acceptable data loss:
Maximum acceptable downtime:
Export time / protected storage reference:
Dump format / PostgreSQL version / required extensions:
Isolated destination (confirm it is not production):
Application commit / schema migration version:
Outbound mail, payments and scheduled jobs disabled:
Import completed without errors:
Known record and related records checked:
Business totals compared as of export:
Uploaded files recovered from:
Import duration / total time to working application:
Failed checks / owner / next action:
Next rehearsal date:
Test data cleanup owner:

FAQ

Does a persistent volume count as a backup? A volume can preserve data through an application restart. You still need a separate recovery copy for failures such as accidental deletion or loss of the storage holding that volume.

Does aidrop.it keep database backups for me? No. The copy is yours to take: your agent can read a small database out with shared_database_query, or deploy a pg_dump job inside the Project that ships dumps to storage you own; aidrop.it retains none of it. Store your copy and test restoration yourself.

How often should we rehearse restoration? Set a schedule based on the data you can afford to lose and the time you can spend recovering. Repeat after changes to the database version, extensions, storage or recovery procedure.