Skip to content

Runbook — Restore a Postgres database from pg_dump

Use when: a single DB has corrupted data, a bad migration shipped, or a destructive query ran in prod.

Prerequisites

  • SSH access to the VPS (ssh vps)
  • Knowledge of which DB to restore (e.g. jb_brain)
  • A backup file in /opt/backups/postgres/<db>_<date>.sql.gz

Steps

  1. Identify the target dump 

    ls -lh /opt/backups/postgres/ | grep <db_name>
    Pick the most recent dump before the bad event.

  2. Stop the app(s) writing to the DB 

    docker stop <app-container>
# Repeat for each consumer

  3. Snapshot the current (bad) state first — never delete what you cannot get back 

    docker exec infra-postgres pg_dump -U postgres <db_name> | gzip > /opt/backups/postgres/<db_name>_predeath_$(date +%s).sql.gz

  4. Drop and recreate the DB 

    docker exec infra-postgres psql -U postgres -c "DROP DATABASE <db_name>;"
docker exec infra-postgres psql -U postgres -c "CREATE DATABASE <db_name> OWNER <db_owner>;"

  5. Restore from the dump 

    gunzip -c /opt/backups/postgres/<db_name>_<date>.sql.gz | \
  docker exec -i infra-postgres psql -U postgres <db_name>

  6. Verify 

    docker exec infra-postgres psql -U postgres -d <db_name> -c "SELECT count(*) FROM <key_table>;"

  7. Start the apps back up 

    docker start <app-container>
docker logs <app-container> --tail 50

  8. Confirm app healthchecks pass before considering the incident resolved.

Rollback

If the restore made things worse: 

docker exec infra-postgres psql -U postgres -c "DROP DATABASE <db_name>;"
docker exec infra-postgres psql -U postgres -c "CREATE DATABASE <db_name> OWNER <db_owner>;"
gunzip -c /opt/backups/postgres/<db_name>_predeath_*.sql.gz | \
  docker exec -i infra-postgres psql -U postgres <db_name>

Post-mortem

Write the incident up in runbooks/post-mortems/ within 24h.