TopolearnA backup you haven't restored is a hope, not a backup; pg_dump is the everyday tool for it.
pg_dump makes a logical backup of one database: SQL definitions and data, consistent as of the moment it started (it uses a snapshot, so it doesn't block normal reads and writes).
Formats:
- plain (
-Fp, default): a SQL script you restore withpsql. Readable, but all-or-nothing. - custom (
-Fc): compressed archive restored with pg_restore, which can restore selected tables (-t), list contents (-l), and run in parallel (-j). The usual choice. - directory (
-Fd): one file per table; supports parallel dump too (-j 4).
pg_dump covers one database and not cluster-wide objects. Roles and tablespaces come from pg_dumpall --globals-only.
Useful restore options: --no-owner and --no-privileges when restoring into an environment with different roles; --clean --if-exists to drop objects before recreating; -d to target a database. A newer pg_restore can read dumps from older servers, which makes dump/restore a simple (if slow) way to upgrade major versions.
Limits: for big databases, dump and restore take hours and you can only restore to the moment of the dump. Production systems combine physical backups with WAL archiving for point-in-time recovery (an Expert lesson). Whatever you use, automate a regular test restore and check the result.
Example
# custom-format dump of one database, plus cluster-wide roles
pg_dump -Fc -d shop -f shop.dump
pg_dumpall --globals-only -f globals.sql
# inspect, then restore into a fresh database with 4 parallel jobs
pg_restore -l shop.dump | head
createdb shop_restore
pg_restore -d shop_restore -j 4 --no-owner shop.dump
# restore only one table's data from the archive
pg_restore -d shop_restore -t orders --data-only shop.dump
# quick sanity check of the restore
psql -d shop_restore -c "SELECT count(*) FROM orders;"Remember
- pg_dump is a consistent logical backup of one database
- Use -Fc (or -Fd) to restore selectively and in parallel with pg_restore
- Dump roles separately with pg_dumpall --globals-only and test restores
Watch out for
- Backing up with pg_dump but never dumping roles, so restored privileges reference roles that don't exist.