OnCallReadyStart learning

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.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

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.