Manual Postgres restore runbook
HOW TO RESTORE WITHOUT OFFSITEDB · STANDARD TOOLS · INCIDENT-SAFE STEPS
OffsiteDB is designed so you can leave — including during a bad day. Your backups live in your bucket and restore with stock Postgres tooling once decrypted. This runbook is the manual path: get the artifact, restore it into a clean target, verify it, then decide how to recover production.
0. Do not overwrite production first
Create a new target database or restore into staging. Most incidents are made worse by destroying the current state before you understand what is broken. Keep the live database frozen or read-only if needed, but restore beside it first.
1. Identify the right artifact
- Pick the newest backup before the bad migration, delete, or corruption event.
- Prefer a tagged pre-migration checkpoint when one exists.
- Confirm source database name, timestamp, row counts, and restore-drill status.
2. Prepare the target
createdb restored_prod_2026_06_14 psql restored_prod_2026_06_14 -c "select version();"
Use a compatible Postgres major version. Restoring from older Postgres into newer Postgres is normally safer than the reverse. Install required extensions before restore if your provider does not create them automatically.
3. Restore a custom-format dump
pg_restore --dbname "$RESTORE_DATABASE_URL" --clean --if-exists --no-owner --no-privileges --verbose prod-api_2026-06-14T04-00-00.dump
For plain SQL dumps, use psql instead:
psql "$RESTORE_DATABASE_URL" < prod-api_2026-06-14T04-00-00.sql
4. Verify the restore
psql "$RESTORE_DATABASE_URL" -c "select count(*) from information_schema.tables where table_schema not in ('pg_catalog','information_schema');"
psql "$RESTORE_DATABASE_URL" -c "select schemaname, relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc limit 25;"Compare table counts, row counts, and application smoke tests against your expected values. If this was a partial recovery, inspect only the affected tables before copying data back.
5. Choose the recovery shape
Full cutover
- Best when production is broadly corrupt.
- Restore into a clean database, run smoke tests, point the app at the restored database.
- Requires downtime or a write freeze.
Table-level repair
- Best after a bad delete or migration damaged a narrow area.
- Restore beside production, export selected rows/tables, import carefully.
- Preserves good writes that happened after the backup.
6. Document the drill
Record artifact name, timestamp, target database, restore duration, table/row checks, who ran it, and what failed. The documentation matters: next time, the runbook will be calmer.
Want this tested before the emergency?
OffsiteDB runs this restore path automatically for every snapshot and keeps the result in your ledger. Compare the sample report, then start a free trial when you want the drill automated.