pg_dump: extract a PostgreSQL database into a script file or other archive file
pg_dump [OPTION]... [DBNAME]
What it does
Dumps ONE database from a consistent snapshot (a REPEATABLE READ transaction) while the database is in use. It takes ACCESS SHARE locks on the tables it dumps (DDL waits until it finishes) and holds back the xmin horizon while it runs. Roles and tablespaces are not included: use pg_dumpall --globals-only for those.
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.dumpthe usual: a compressed custom-format archive
$ pg_dump -Fd -j 4 -d orders -f orders.dparallel dump to a directory
$ pg_dump -s -d ordersschema only, as SQL
$ pg_dump -t customers -d orders > customers.sqlone 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
Try pg_dump in a real terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.