The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Plain SQL dump | pg_dump --dbname=appdb --format=plain --file=appdb.sql | View examples |
| Custom archive | pg_dump --dbname=appdb --format=custom --file=appdb.dump | View examples |
| Parallel directory dump | pg_dump --dbname=appdb --format=directory --jobs=4 --file=appdb.dir | View examples |
| Dump cluster globals | pg_dumpall --globals-only --file=globals.sql | View examples |
| Dump definitions only | pg_dump --dbname=appdb --schema-only --file=schema.sql | View examples |
| Dump data only | pg_dump --dbname=appdb --data-only --format=custom --file=data.dump | View examples |
| Inspect archive contents | pg_restore --list appdb.dump | View examples |
| Restore and create database | pg_restore --create --dbname=postgres appdb.dump | View examples |
| Restore in parallel | pg_restore --dbname=restore_test --jobs=4 appdb.dump | View examples |
| Drop archived objects first | pg_restore --clean --if-exists --dbname=restore_test appdb.dump | View examples |
| Restore plain SQL | psql --
SET=ON_ERROR_STOP=
ON --dbname=restore_test --file=appdb.sql | View examples |
| Take a physical base backup | pg_basebackup --pgdata=/backup/base --format=plain --wal-method=stream --progress | View examples |
| Verify backup manifest | pg_verifybackup /backup/base | View examples |
| Set a recovery time | recovery_target_time = '2026-08-12 14:30:00+00' | View examples |
| Pause at the recovery target | recovery_target_action = 'pause' | View examples |
A backup is useful only when it can satisfy a documented recovery objective in a tested target environment. Logical dumps rebuild selected database objects; physical backups plus continuous WAL support cluster recovery and point-in-time targets. Encrypt and monitor backup data, preserve server-version compatibility, and rehearse restores without touching production.
Step by step
Detailed examples
Choose a logical format from restore requirements
pg_dump creates a consistent snapshot of one database while normal activity continues. Plain format is readable SQL but restores serially; custom and directory formats support selective and parallel pg_restore. Dumps do not include cluster-wide roles and tablespaces, and large-object and extension requirements need explicit review.
pg_dump --dbname=appdb \
--format=custom \
--file=appdb-2026-08-12.dump \
--verbose 2>appdb-2026-08-12.dump.log
pg_restore --list appdb-2026-08-12.dump | sed -n '1,30p'Capture globals and define intentional exclusions
pg_dump is per database; pg_dumpall --globals-only captures roles and tablespaces. Ownership and grants can fail when roles are missing at restore. Schema-only/data-only and include/exclude filters are migration tools, not automatically complete disaster-recovery backups. Record extension binaries, configuration, and secrets separately.
pg_dumpall --globals-only --file=globals.sql
pg_dump --dbname=appdb --format=custom --file=appdb.dump
sha256sum globals.sql appdb.dump > SHA256SUMSRestore only into an isolated, approved target
pg_restore operates on non-plain archives and can select, reorder, disable ownership, or use multiple jobs. --clean issues destructive drops; --create creates the database named in the archive. Restore globals deliberately, use a compatible or newer client/server path, capture errors, and never use a production connection string for rehearsal.
createdb restore_test
pg_restore --dbname=restore_test --jobs=4 --exit-on-error appdb.dump
psql --dbname=restore_test --command='SELECT count(*) FROM app.critical_table;'Note: Use an isolated server or cluster and an identity authorized only for the rehearsal target.
Combine a physical base backup with an unbroken WAL archive
Physical recovery restores an entire compatible cluster, not selected tables. A base backup needs every required WAL segment through the recovery target. archive_command must copy durably and return success only after safe storage; monitor failures and prevent premature retention cleanup. Timelines preserve history after recovery.
pg_basebackup --dbname='postgresql://backup@db.example/appdb' \
--pgdata=/backup/base-2026-08-12 \
--format=plain --wal-method=stream \
--checkpoint=fast --progressVerify artifacts, then verify a real restore
Checksums and pg_verifybackup detect missing or changed files but cannot prove logical usability, correct permissions, extension availability, or application behavior. Restore on a schedule, run integrity queries and application smoke tests, measure recovery time, and record the exact runbook and observed RPO/RTO.
sha256sum --check SHA256SUMS
pg_verifybackup /backup/base-2026-08-12
psql --dbname=restore_test --set=ON_ERROR_STOP=on --file=post_restore_checks.sqlMake recovery targets and promotion explicit
Recovery settings select time, transaction ID, LSN, name, or immediate consistency and define inclusive behavior. A wrong time zone or target action can recover to the wrong point or promote unexpectedly. Clone backup data, preserve the original, isolate recovered instances from clients, and validate before promotion or data extraction.
restore_command = 'restore-wal %f %p'
recovery_target_time = '2026-08-12 14:30:00+00'
recovery_target_inclusive = true
recovery_target_action = 'pause'Note: Commands and paths are environment-specific; validate them in a complete isolated recovery rehearsal.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: SQL Dumppostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Continuous Archiving and Point-in-Time Recoverypostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: pg_dumppostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: pg_restorepostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: pg_basebackuppostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.