Postgres Backup + Restore: 30-Minute Tutorial

Untested backups don’t exist. Practice the restore in 30 minutes; 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

Drop a table or rename. Anything to test the restore.

Confirm app fails; the restore needs to fix it.

Step 4: Restore

pg_restore -d mydb -c backup.dump

For SQL backup: psql mydb < backup.sql

Verify: data is back; app works.

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.