🚀AdvancedBackup & Recovery
pg_dump and pg_restore
Back up and restore PostgreSQL databases using pg_dump and pg_restore.
Overview
pg_dump is PostgreSQL's logical backup tool it exports database schemas and data as SQL or a custom binary format. pg_restore restores custom-format dumps. pg_dumpall backs up all databases and global objects (roles). These are essential for migrations, disaster recovery, and moving data between environments.
Custom format (-Fc) is the most flexible: compressed, supports parallel restore (-j), and allows selective table restore. Use --schema-only or --data-only for partial exports.
Key Points
- •pg_dump -Fc dbname > backup.dump custom format (compressed)
- •pg_restore -d dbname backup.dump restore custom format
- •pg_dump -Fp plain SQL format (human readable)
- •pg_dump --schema-only / --data-only partial backups
- •pg_restore -j 4 parallel restore with 4 workers
- •pg_dumpall dump all databases + global objects
Examples
pg_dump and pg_restore commands
-- Backup single database (custom format, compressed)
-- pg_dump -Fc -h localhost -U postgres mydb > mydb.dump
-- Restore
-- pg_restore -Fc -h localhost -U postgres -d mydb_restored mydb.dump
-- Dump specific tables only
-- pg_dump -t orders -t customers mydb > orders_customers.sql
-- Schema only (no data)
-- pg_dump --schema-only mydb > schema.sql
-- Parallel restore (4 jobs)
-- pg_restore -j 4 -d mydb mydb.dump
-- Check backup info
-- pg_restore --list mydb.dump | head -20Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4