pg_dump Utility
Learning Focus
Use this lesson to take logical backups with pg_dump and restore them reliably with pg_restore.
Concept Overview
pg_dump produces a logical backup (schema + data). It is great for:
- migrations
- small/medium DB backups
- selective restores (specific schemas/tables)
Common Formats
| Format | Flag | Notes |
|---|---|---|
| Custom | -Fc | recommended; supports selective/parallel restore |
| Directory | -Fd | directory output; good for parallel restore |
| Plain SQL | default | human-readable; less flexible |
Examples
Custom format dump + restore:
pg_dump -Fc -d pg_lab -f pg_lab.dump
createdb pg_lab_restore
pg_restore -d pg_lab_restore pg_lab.dump
Schema-only:
pg_dump -Fc -s -d pg_lab -f pg_lab_schema.dump
Data-only:
pg_dump -Fc -a -d pg_lab -f pg_lab_data.dump
Globals (roles, etc.):
pg_dumpall --globals-only > globals.sql
Common Mistakes & Troubleshooting
| Mistake | What happens | Fix |
|---|---|---|
| Forgetting globals | Restored DB works but roles missing | Back up globals separately |
| Never testing restores | Backups may be unusable | Restore-test regularly |
Best Practices
- Prefer
-Fcor-Fd. - Encrypt and store backups off-site.
- Test restores and measure RTO.
Quick Reference
pg_dump -Fc -d db -f db.dump
pg_restore -d db_restore db.dump
pg_dumpall --globals-only