pg_dump Command Builder

pg_dump in depth: formats, parallel dumps, and restore strategy

pg_dump is the PostgreSQL logical-backup utility. Unlike a binary base backup, it captures a consistent SQL representation of one database — schema, data, indexes, constraints, sequences, owners, privileges — that any compatible PostgreSQL server can replay. The general shape is pg_dump [options] db_name > out.sql. It uses a long-running REPEATABLE READ transaction internally, so concurrent reads and writes are not blocked; the snapshot is consistent at the moment the dump begins.

Output formats: pick wisely

The -F flag picks one of four formats and the choice has real consequences:

-F p   plain SQL          text, large, restore with psql.
-F c   custom (default-ish) compressed binary, allows parallel
                            restore, selective restore. RECOMMENDED.
-F d   directory           parallelizable dump (-j) AND parallel restore;
                            one file per relation. Best for big DBs.
-F t   tar                 tar archive of the directory format; less
                            commonly used.

Use -F c for almost every backup. It compresses, supports selective restore via pg_restore -t table, and pg_restore can parallelize the data-load phase. Use -F d -j 4 when you need parallel dumping of a large database.

Connection and selection flags

• -h host, -p port, -U user, -W (prompt for password), -d dbname.
• -f file — output file (omit to write to stdout for piping).
• --schema-only / -s — DDL only.
• --data-only / -a — INSERT/COPY data only.
• --clean + --if-exists — emit DROP IF EXISTS before each CREATE.
• --no-owner — strip ownership statements (handy when restoring as a different role).
• --no-privileges — strip GRANTs.
• -t schema.table — dump only specific tables (repeatable, supports patterns).
• -n schema — only a specific schema; --exclude-schema, --exclude-table for the reverse.
• -j JOBS — parallel workers (only with -F d).
• --compress=9 — compression level (custom/directory formats only).
• --encoding=UTF8 — explicit client encoding.

Restore: pg_restore vs psql

The restore command depends on the dump format:

# Custom or directory format -> pg_restore
pg_restore -d mydb -j 4 backup.dump
pg_restore -d mydb -t users backup.dump        # only one table
pg_restore -l backup.dump > toc.list           # list of objects
pg_restore -L toc.list -d mydb backup.dump     # selective restore

# Plain SQL -> psql
psql -d mydb -f backup.sql
psql -d mydb < backup.sql

# Dump the whole cluster (roles, tablespaces, every DB)
pg_dumpall > cluster.sql
psql -f cluster.sql postgres

pg_dumpall is the superset: roles, tablespaces, replication settings, plus every database. Use it for cluster-level migrations; for routine backups of one application database stick with pg_dump -F c.

Worked examples

# Daily compressed custom-format backup
pg_dump -h db.prod -U backup -F c -f /backups/mydb-$(date +%F).dump mydb

# Parallel directory dump (4 workers) of a 100 GB database
pg_dump -h db.prod -U backup -F d -j 4 -f /backups/mydb.d mydb

# Schema only, ready to bootstrap a staging environment
pg_dump --schema-only --no-owner --no-privileges mydb > schema.sql

# Stream to S3 without a local file
pg_dump -F c mydb | aws s3 cp - s3://backups/mydb.dump

# Restore with 4 parallel workers, dropping objects first
pg_restore --clean --if-exists -d mydb -j 4 backup.dump

Logical vs physical, version compatibility, ecosystem

Logical backup (pg_dump) is portable across versions and architectures, ideal for migrations and selective restores. Physical backup (pg_basebackup, pgBackRest, WAL-G) copies the actual data files plus WAL — much faster on huge databases and the only practical approach in the TB range, but the restore must run on the same major version and CPU architecture.

Version rule: pg_dump may target an older server (it knows how to query forward-compatibly), but the client must be greater than or equal to the server when in doubt — always run a pg_dump at least as new as the server. Restore order matters too: dumping with pg_dump 16 and restoring with pg_restore 14 may fail. Production-grade tooling: pgBackRest (incremental, parallel, full restore in minutes), Barman, WAL-G (continuous archiving plus point-in-time recovery to S3/GCS). Managed services: AWS RDS PostgreSQL automated snapshots, GCP Cloud SQL exports, Azure Database for PostgreSQL backups.

FAQ

Is the dump a hot backup? Yes. pg_dump opens a REPEATABLE READ snapshot; concurrent transactions are unaffected and the dump sees a consistent view as of the moment the snapshot was taken.

How do I dump only one schema? pg_dump -n schema_name -F c mydb > schema.dump. Use -N to exclude schemas (often -N pg_temp_* in scripted backups).

Can the dump be parallelized? Yes — but only with the directory format: pg_dump -F d -j 4 -f mydb.d mydb. The plain and custom formats are written by a single worker. pg_restore can parallelize loading both custom and directory dumps via -j N.

Does the custom format already compress? Yes — by default zlib level 6. Bump it with --compress=9 for slow networks. No need to pipe through gzip on top.

Can pg_restore continue past an error? By default it does continue and logs the failure; pass --exit-on-error if you want it to stop on the first failure. Pair with --single-transaction to wrap the whole restore in one tx (slower, all-or-nothing semantics).