pg_dump

Dump database into an SQL-script file

$ pg_dump [db_name] > [output_file.sql]

$ pg_dump [[-U|--username]] [username] [db_name] > [output_file.sql]

$ pg_dump [[-h|--host]] [host] [[-p|--port]] [port] [db_name] > [output_file.sql]

$ pg_dump [[-F|--format]] [[c|custom]] [db_name] > [output_file.dump]

$ pg_dump [[-a|--data-only]] [db_name] > [path/to/output_file.sql]

$ pg_dump [[-s|--schema-only]] [db_name] > [path/to/output_file.sql]

pg_dump [OPTION]... [DBNAME]

-f FILENAME, --file=FILENAME
    Output file or directory name

-F FORMAT, --format=FORMAT
    Output format: p (plain), c (custom), d (directory), t (tar). Default: p

-h HOSTNAME, --host=HOSTNAME
    Database server host or socket directory

-p PORT, --port=PORT
    Database server port number

-U USERNAME, --username=USERNAME
    Connect as specified database user

-d, --data-only
    Dump only data, not schema

-s, --schema-only
    Dump only schema, not data

-a, --data-as-inserts
    Dump data as INSERT commands, not COPY

-c, --clean
    Include commands to clean before recreating

-C, --create
    Include commands to create database

-n PATTERN, --schema=PATTERN
    Dump only matching schemas

-t PATTERN, --table=PATTERN
    Dump only matching tables

-T PATTERN, --exclude-table=PATTERN
    Do not dump matching tables

-N PATTERN, --exclude-schema=PATTERN
    Do not dump matching schemas

-j NUM, --jobs=NUM
    Use parallel jobs for dump (with directory format)

-Z LEVEL, --compress=LEVEL
    Compression level for compressed formats

--blobs
    Include large objects

--no-blobs
    Exclude large objects

-v, --verbose
    Verbose mode

-V, --version
    Print version info

--help
    Show help

pg_dump is a utility program included in the PostgreSQL database system for backing up a PostgreSQL database. It extracts a PostgreSQL database into an SQL script file or other archive formats that can be used to recreate the database. Unlike low-level tools like pg_dumpall, pg_dump works on a single database at a time and does not include global objects like roles or tablespaces unless specified.

Key features include support for multiple output formats: plain text SQL scripts, custom-format archives, directory-format archives, and tar archives. It performs the dump by querying the database directly, making it efficient and allowing dumps over networks. pg_dump acquires shared locks on tables to ensure consistency without blocking normal reads, but it may acquire exclusive locks briefly for large objects.

Common use cases include database migration, backups before upgrades, and disaster recovery. Output can be compressed, filtered by schema/object, or include/exclude specific elements like data, schema, or BLOBs. It's invoked from the command line and requires appropriate database permissions.

pg_dump connects as a client and requires SELECT privileges on tables; does not dump global objects like roles/tablespaces (use pg_dumpall); acquires locks, potentially conflicting with DDL; plain format is not compressed; parallel dumps (--jobs) only for directory format and PostgreSQL 9.2+.

Plain (p): SQL script readable by psql.
Custom (c): Binary archive for pg_restore, supports selective restore.
Directory (d): Split files for large dumps, parallel-capable.
Tar (t): Standard tar archive for pg_restore.

Use psql for plain format: psql -f dump.sql dbname.
Other formats require pg_restore: pg_restore -d dbname dump.tar.

Introduced in PostgreSQL 6.0 (1998) with plain text format; custom and tar formats added in 7.1 (2001); directory format and parallel dump in 9.2 (2012); ongoing enhancements for parallelism, compression, and filtering in later versions.

pg_restore(1), pg_dumpall(1), psql(1), createdb(1), postgres(1)