Postgres Backup + Restore

Untested backups don’t exist. Practice the restore; build the muscle memory before the incident.

Step 1: pg_dump

pg_dump mydb > backup.sql

For larger DBs: pg_dump -Fc mydb > backup.dump (custom format).

Step 2: Verify backup

• Inspect: pg_restore -l backup.dump | head
• Verify size; verify object count.

Step 3: Simulate loss

The restore drill only counts if it starts from real damage. Skip the simulation and you are testing the happy path, which is not the path that matters.

• Pick a target. Drop a table, rename a schema, or DELETE without WHERE in a sandbox copy.
• Confirm impact. The app should error or return wrong data; if not, you simulated the wrong thing.
• Time the failure. Note when the simulated loss happened; restore needs to land before customers notice.
• Document. Write down what you broke; the postmortem-style record turns the drill into a runbook.

Step 4: Restore

The restore is the only step that proves the backup. Until restore completes successfully, the backup is theoretical.

• Custom format. pg_restore -d mydb -c backup.dump drops and recreates objects.
• SQL format. psql mydb < backup.sql replays the dumped statements.
• Parallel restore. pg_restore -j 4 for large dumps; restoring at multi-CPU pace shortens RTO.
• Verify. Row counts match expected; app works against the restored database; record the wall-clock RTO for capacity planning.

Antipatterns

• Backup without ever testing restore. May not work.
• pg_dump on busy primary. Lock + IO.
• Backup files in same place as primary. Single point.

What to do this week

Three moves. (1) Run the tutorial end-to-end on your own laptop / sandbox. (2) Apply the pattern to one production workload. (3) Document the variations you needed; share with the team.