PostgreSQL pg_dump and pg_restore cheat sheet: formats, parallel jobs, single tables, roles and the errors you'll hit

Use pg_dump for logical copies: refreshing a test database, moving one schema, keeping a table before a risky change, or migrating between major versions. It is not a substitute for physical backups with point-in-time recovery. This sheet collects the commands you run every week and the errors you see when they go wrong.

Tip

Use a pg_dump/pg_restore version that is the same as or newer than the server. A newer pg_dump can read older servers. An older one refuses to dump a newer server.

#Connection basics

Every tool accepts the same connection flags. Use ~/.pgpass or PGPASSWORD in automation so the command does not prompt for a password.

export PGHOST=db01.internal PGPORT=5432 PGUSER=dba
# ~/.pgpass line: host:port:database:user:password  (chmod 600)
pg_dump --version
psql -d appdb -c "select version();"

#Pick a format

#Dump commands

#Whole database

# Custom format, compressed by default
pg_dump -d appdb -Fc -f appdb_$(date +%F).dump

# Directory format with 8 parallel jobs (large databases)
pg_dump -d appdb -Fd -j 8 -f /backup/appdb_$(date +%F)

# PostgreSQL 16+ client: choose the compression method
pg_dump -d appdb -Fd -j 8 --compress=zstd:3 -f /backup/appdb_zstd

Each -j worker opens its own connection. Check max_connections and any connection limit set on the role.

#Part of a database

# One schema
pg_dump -d appdb -Fc -n sales -f sales_schema.dump

# One table (quote mixed-case names)
pg_dump -d appdb -Fc -t 'sales.orders' -f orders.dump

# Pattern: every table starting with audit_
pg_dump -d appdb -Fc -t 'public.audit_*' -f audit_tables.dump

# Everything except big log tables' data (keep their definitions)
pg_dump -d appdb -Fc --exclude-table-data='public.event_log*' -f appdb_nolog.dump

# Structure only / data only
pg_dump -d appdb -s -f appdb_schema.sql
pg_dump -d appdb -a -Fc -f appdb_data.dump

Warning

-t dumps the table and its indexes, constraints and triggers. It does not dump sequences that the table's columns use unless the sequence is owned by the table, and it does not dump objects the table depends on, such as types, functions and other schemas. Test the restore into an empty database.

#Roles, tablespaces and grants (cluster level)

pg_dump does not include roles. Dump them separately:

pg_dumpall --globals-only -f globals.sql     # roles + tablespaces
pg_dumpall --roles-only   -f roles.sql
# Managed services often block reading password hashes:
pg_dumpall --roles-only --no-role-passwords -f roles_nopw.sql

#Avoid blocking the application

pg_dump takes ACCESS SHARE locks on every table it dumps. That does not block reads or writes, but it blocks DDL such as ALTER TABLE and DROP. Any DDL that queues behind those locks then blocks normal queries. Fail fast instead of waiting:

pg_dump -d appdb -Fc --lock-wait-timeout=30s -f appdb.dump

Run large dumps against a standby where possible. On a hot standby, long dumps can be cancelled by replication conflicts. Check max_standby_streaming_delay or hot_standby_feedback before you rely on this.

#Restore commands

#Inspect before you restore

pg_restore -l appdb.dump | head -50           # table of contents
pg_restore -f - -s appdb.dump | less          # show the SQL it would run

#Restore into a new or existing database

# Create the target database first, then restore with 8 jobs
createdb -O app_owner appdb_test
pg_restore -d appdb_test -j 8 --no-owner --role=app_owner appdb.dump

# Let pg_restore create the database (connect to postgres first)
pg_restore -d postgres --create -j 8 appdb.dump

# Replace existing objects
pg_restore -d appdb_test --clean --if-exists -j 8 appdb.dump

# All-or-nothing (cannot be combined with -j)
pg_restore -d appdb_test --single-transaction --exit-on-error appdb.dump

Caution

--clean drops objects in the target before it recreates them. Check the -d target twice: --clean aimed at production instead of test destroys production data. Use a separate login with no rights on production for refreshes.

#Restore one table or schema from a full dump

pg_restore -d appdb_test -n sales -t orders appdb.dump

# Fine-grained: edit the list, then restore only what is left
pg_restore -l appdb.dump > toc.list
# comment out lines with ';' in toc.list
pg_restore -d appdb_test -L toc.list appdb.dump

#Plain SQL dumps

psql -d appdb_test -v ON_ERROR_STOP=1 -1 -f appdb_schema.sql
psql -d postgres -v ON_ERROR_STOP=1 -f globals.sql

Without ON_ERROR_STOP=1, psql keeps going after errors, and a broken restore looks successful.

#After the restore

-- Planner statistics: older releases do not dump them; check whether your
-- client version can (PostgreSQL 18 added statistics options to pg_dump)
ANALYZE;

-- Compare row counts on key tables with the source
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;

-- Sequences ahead of the max key?
SELECT last_value FROM sales.orders_id_seq;
SELECT max(id) FROM sales.orders;

#Errors and fixes

#Prevent it next time

• Keep the newest client tools on the jump host and pin their path in your scripts (for example /usr/pgsql-17/bin/pg_dump).
• Dump globals every time you dump databases. A dump without its roles is hard to restore.
• Use -Fc or -Fd, never only plain SQL, for anything you might need to restore selectively.
• Use --lock-wait-timeout and run dumps from a standby.
• Test restores: restore last night's dump into a scratch database every week and compare row counts.
• For real recovery points, pair logical dumps with physical backups and WAL archiving.