pg_dump - extract a PostgreSQL database into a script file or other archive file

Options you will use

-f, --file=FILENAME

output file or directory name (default: stdout).

-F, --format=c|d|t|p

output file format: custom, directory, tar, plain text (default). Custom (-Fc) is compressed and lets pg_restore list, pick and restore in parallel.

-j, --jobs=NUM

use this many parallel jobs to dump (directory format only).

-Z, --compress=METHOD[:DETAIL]

compress as specified (gzip, lz4, zstd, none).

-a, --data-only

dump only the data, not the schema.

-s, --schema-only

dump only the schema, no data.

-C, --create

include commands to create the database in the dump.

-c, --clean

clean (drop) database objects before recreating.

--if-exists

use IF EXISTS when dropping objects.

-n, --schema=PATTERN

dump the specified schema(s) only.

-t, --table=PATTERN

dump only the specified table(s).

-T, --exclude-table=PATTERN

do NOT dump the specified table(s).

-O, --no-owner

skip restoration of object ownership in plain-text format.

-x, --no-privileges

do not dump privileges (grant/revoke).

--lock-wait-timeout=TIMEOUT

fail after waiting TIMEOUT for a table lock.

-d, --dbname=DBNAME

database to dump.

-h, --host=HOSTNAME

database server host or socket directory (default: the Unix socket in /var/run/postgresql). A value starting with / is a socket directory; anything else is TCP.

-p, --port=PORT

database server port (default 5432).

-U, --username=USERNAME

database user name (default: your OS user name).

-w, --no-password

never prompt for a password (fail instead: what scripts want).

-W, --password

force a password prompt (normally automatic).

Examples

$ pg_dump -Fc -d orders -f orders.dump

the usual: a compressed custom-format archive

$ pg_dump -Fd -j 4 -d orders -f orders.d

parallel dump to a directory

$ pg_dump -s -d orders

schema only, as SQL

$ pg_dump -t customers -d orders > customers.sql

one table as SQL

Gotchas

• Use a pg_dump at least as new as the server (an older client refuses: "server version mismatch").
• pg_dump | gzip in a script hides a failed dump unless the script has set -o pipefail.
• Plain dumps start with \restrict <key> and end with \unrestrict (security releases of August 2025): restore them with psql -f, not by pasting.

Taught in

• 25.43 Backups: pg_dump, pg_restore and testing restores