54 pg_dump and pg_restore

pg_dump is the standard PostgreSQL backup tool. It produces a consistent snapshot of a single database, either as a plain SQL script or as a custom binary archive. Its companion pg_restore reads the binary archives back. For full-cluster backups (pg_dumpall), per-tablespace data, and point-in-time recovery, PostgreSQL offers separate tools — but pg_dump covers the great majority of “back up my database” needs. It is what you reach for first.

What you’ll learn

• The two output formats (plain .sql and custom .dump)
• pg_dump and pg_restore flags worth memorizing
• Dumping schema only vs data only vs both
• Including/excluding specific tables or schemas
• Restoring in parallel for speed

Setup. These are shell commands, not SQL. Run them from your terminal. The source file for this lesson is a thin wrapper that documents the commands.

Example

# Plain-text SQL dump (human-readable, runnable with psql)
pg_dump --dbname=postgres --format=plain --file=backup.sql

# Custom-format dump (compressed binary, restorable with pg_restore)
pg_dump --dbname=postgres --format=custom --file=backup.dump

# Schema only (DDL, no data) — useful for reproducing the shape of a database
pg_dump --schema-only --dbname=postgres --file=schema.sql

# Data only (no DDL)
pg_dump --data-only --dbname=postgres --file=data.sql

# Dump only specific tables (or use --exclude-table)
pg_dump --table=fruits --table=orders_example --dbname=postgres --file=subset.sql

# Dump only one schema
pg_dump --schema=app --dbname=postgres --file=app_schema.dump --format=custom

# Restore from a custom-format dump
pg_restore --dbname=postgres --jobs=4 --clean --if-exists backup.dump

# Restore a plain SQL dump
psql --dbname=postgres --file=backup.sql

# Dump every database in the cluster, plus roles and globals
pg_dumpall --file=cluster.sql

The custom format (--format=custom or -Fc) is what most teams use. It is compressed by default, supports parallel restore (--jobs=N), and lets you selectively restore individual objects (pg_restore --list backup.dump to inspect, then --use-list to pick what to restore). Plain format is easier to read and grep, but less flexible.

pg_dump uses a consistent snapshot and takes ACCESS SHARE locks on the tables it dumps. Concurrent data changes do not invalidate that snapshot and ordinary writers can continue. Conflicting DDL may wait or cause a dump to fail; parallel dumps use synchronized snapshots.

--clean --if-exists drops objects included in the archive before recreating them; it does not remove every unrelated object or guarantee an error-free restore. Restore into a fresh scratch database for a predictable result. --jobs=4 can speed up a custom- or directory-format restore.

For production backups, pg_dump is part of the strategy but not the whole story. Continuous archiving (archive_mode = on) plus base backups (pg_basebackup) give you point-in-time recovery. Cloud-managed PostgreSQL handles this transparently.

Run the example

To run (this is a shell example — see the SQL file for the documented commands):

$ pg_dump --dbname=postgres --format=custom --file=backup.dump
$ pg_restore --dbname=postgres --clean --if-exists --jobs=4 backup.dump

Common pitfalls

• Use pg_dumpall --globals-only for roles and tablespaces. Neither it nor pg_dump backs up replication slots; record and recreate those separately as needed.
• Restoring into a database that already has the schema produces conflicts. Use --clean --if-exists, drop and recreate the database, or restore into an empty one.
• A plain SQL dump restored with psql does not stop on the first error by default — pass -v ON_ERROR_STOP=1 to make it fail loud.
• Version compatibility: pg_dump cannot dump a server newer than its own major version. Dumps are intended for the same or newer PostgreSQL major version; restoring to an older major is not guaranteed. For upgrades, use the target major’s pg_dump if it supports the source server.

Downloads

SQL file: pg-dump-and-restore.sql · All lesson files · Setup guide