How to Restore a Database from a Backup

Before you touch anything: stop and assess

The instinct during data loss is to act fast. Resist it for sixty seconds. A panicked restore can overwrite the very data you might still recover. First, answer:

1. 1What exactly is lost? A dropped table, a bad migration, full corruption, or accidental deletes?
2. 2What's your most recent good backup, and what's its timestamp? This determines your data-loss window.
3. 3Can you restore to a *new* instance instead of overwriting production? Almost always, yes, and you should.

The golden rule: restore to a fresh instance first, verify, then cut over. Never restore directly over a live database you might still need to inspect.

Restoring a logical dump (Postgres)

If your backup is a pg_dump, restore it into a clean database.

# Create a fresh target database
createdb app_restore

# If it's a plain SQL dump (optionally gzipped)
gunzip -c backup-20260221.sql.gz | psql "$RESTORE_DATABASE_URL"

# If it's a custom-format dump (pg_dump -Fc), use pg_restore (parallelizable)
pg_restore --no-owner --jobs=4 -d "$RESTORE_DATABASE_URL" backup-20260221.dump

Useful flags:

• --no-owner avoids ownership errors when restoring to a different role.
• --jobs=N parallelizes a custom-format restore, much faster for large databases.
• --clean (use carefully) drops objects before recreating them.

For MySQL, the equivalent is mysql < dump.sql or, for physical backups, the appropriate restore tool for your backup method.

Point-in-time recovery (PITR)

If you have a base backup plus continuous WAL archiving, you can restore to a *specific moment*, for example, the instant before a destructive query. The concept:

1. 1Restore the base backup to a new data directory.
2. 2Configure recovery to replay WAL up to your target time.

# postgresql recovery settings (conceptual)
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-02-21 14:32:00'   # just before the mistake
recovery_target_action = 'promote'

PITR is the difference between "we lost everything since last night" and "we lost three minutes." It's worth the setup precisely for the bad day.

Verify before you cut over

This is the step that separates a recovery from a second incident. On the restored instance, before pointing production at it:

• Row counts on critical tables, do they match expectations?
• Spot-check recent records, is the data current up to your recovery point?
• Referential integrity, run a few joins; check for orphaned rows.
• Application smoke test, point a staging copy of the app at the restored DB and click through key flows.

SELECT count(*) FROM orders;
SELECT max(created_at) FROM orders;   -- confirms how recent the data is

Only once you trust the restored instance do you cut traffic over to it.

Cutting over safely

1. 1Put the app in maintenance mode (or stop writes) to avoid split data.
2. 2Point the application's DATABASE_URL at the restored instance.
3. 3Run any forward migrations needed to match the current code.
4. 4Bring the app back and watch logs/metrics closely.
5. 5Keep the old (damaged) database around, read-only, until you're certain, you may still need to salvage something from it.

Restoring on a managed platform

Managed databases simplify restores considerably because the platform owns the backup machinery. On PandaStack, managed databases (PostgreSQL, MySQL, MongoDB, Redis via KubeBlocks) take scheduled and manual backups with plan-based retention (7 days Free, 15 Pro, 30 Premium). The recovery workflow is to restore from one of those backups rather than hand-rolling pg_restore.

Because PandaStack injects DATABASE_URL into your service, cutting over to a restored database is a matter of updating the connection the service uses, no code change required. And because the platform keeps deploy history with rollbacks, you can also roll the *application* back to the version that matched the data, which matters when a bad deploy and a bad migration arrived together.

A practical habit: take a manual backup right before any risky migration. On PandaStack that's a one-click manual backup, and it gives you a clean, known-good restore point seconds before the change you're worried about.

Common restore mistakes

• Restoring over production directly, destroys forensic evidence and your fallback. Restore to a new instance.
• Skipping verification, cutting over to a restore that's missing data or stale.
• Forgetting migrations, restored schema doesn't match current app code. Reconcile before reopening.
• Not testing restores until the incident, your first restore should be a drill, not a disaster.
• Deleting the damaged DB too soon, keep it read-only until fully confident.

A restore runbook

1. 1Assess scope and pick the target recovery point.
2. 2Restore to a new instance (logical restore or PITR).
3. 3Verify: row counts, recency, integrity, app smoke test.
4. 4Maintenance mode / stop writes on the app.
5. 5Cut over DATABASE_URL to the restored instance; run forward migrations.
6. 6Resume traffic; watch logs and metrics.
7. 7Retain the damaged DB read-only until confirmed healthy.
8. 8Write a postmortem and update your RPO/RTO numbers.

Conclusion

A restore is judged entirely on the bad day. The habits that make it succeed are unglamorous: restore to a fresh instance, verify hard before cutting over, keep migrations in sync, and never delete your fallback prematurely. Managed databases remove much of the mechanical risk, but the runbook discipline is still yours to own.

PandaStack's managed databases give you scheduled and manual backups plus injected connection strings, so restoring and cutting over is straightforward. Try a managed database on the free tier at https://dashboard.pandastack.io.

References

• PostgreSQL pg_restore: https://www.postgresql.org/docs/current/app-pgrestore.html
• PostgreSQL point-in-time recovery: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY
• MySQL restore overview: https://dev.mysql.com/doc/refman/8.0/en/restore.html
• Google SRE workbook, data integrity: https://sre.google/workbook/data-processing/