PG_DUMP(1) PostgreSQL 18.6 Documentation PG_DUMP(1) (NAME) pg_dump - PostgreSQL SQL (SYNOPSIS) pg_dump [connection-option...] [option...] [dbname] (DESCRIPTION) pg_dump PostgreSQL . . pg_dump ( ). pg_dump (production) . Chapter 25 . pg_dump . (global) ( tablespace) pg_dumpall(1) . (archive) . (plain-text) SQL . psql(1) . SQL . pg_restore(1) . pg_restore . . pg_restore pg_dump . pg_dump pg_restore / . "custom" (-Fc) "directory" (-Fd) . . "directory" (parallel dumps) . pg_dump ( ) . (superusers) . . (superusers) SQL . --file pg_restore . dump restore (superusers) . . dbname . PGDATABASE . . -a --data-only ( ) . (large objects) (sequence) . --section=data . -b --large-objects --blobs () . --schema --table --schema-only --statistics-only --no-data . -b . --data-only --schema-only --statistics-only . -B --no-large-objects --no-blobs () . -b -B -b . -c --clean DROP . . --if-exists . () . pg_restore . -C --create . ( .) --clean . --create (comment) ALTER DATABASE ... SET ... ALTER ROLE ... IN DATABASE ... SET ... . --no-acl . () . pg_restore . -e pattern --extension=pattern pattern . . -e . pattern \d psql ( / Patterns ) (wildcard) . . pg_extension_config_dump --extension . -e pg_dump () . - . -E encoding --encoding=encoding (character set encoding) . . ( PGCLIENTENCODING .) 23.3.1 . -f file --file=file . . . pg_dump . -F format --format=format . format : p plain SQL () . c custom pg_restore . . . d directory pg_restore . (Table of Contents) pg_restore . gzip lz4 zstd . gzip . t tar tar pg_restore . tar : tar . tar . tar . -j njobs --jobs=njobs dump njobs . dump . . pg_dump njobs + 1 max_connections . dump dump . (leader) pg_dump (ACCESS SHARE) (worker) dump dump . . . dump . (deadlock) . pg_dump NOWAIT . dump pg_dump dump . dump (synchronized snapshots) PostgreSQL 9.2 (primary) 10 (standby) . . pg_dump -j (worker job) . . -n pattern --schema=pattern pattern dump . . -n . pattern \d psql ( Patterns ) (wildcard) . Examples . -n pg_dump dump () . dump . large objects -n dump . --large-objects large objects dump . -N pattern --exclude-schema=pattern pattern dump . -n . -N . -n -N dump -n -N . -N -n -N dump . -O --no-owner . pg_dump ALTER OWNER SET SESSION AUTHORIZATION . / ( ) . -O . ( ) . pg_restore . -R --no-reconnect . -s --schema-only () dump . --data-only --statistics-only . --section=pre-data --section=post-data . ( --schema "schema" .) --exclude-table-data . -S username --superuser=username (superuser) . --disable-triggers . ( .) -t pattern --table=pattern pattern dump . -t . pattern \d psql ( Patterns ) . Examples ..sp . --include-foreign-data . -n -N -t -t . -t pg_dump () . . -T pattern --exclude-table=pattern pattern . -t . -T . -t -T -t -T . -T -t -T . -v --verbose (verbose) . pg_dump / . . -V --version pg_dump . -x --no-privileges --no-acl ( grant/revoke) . -Z level -Z method[:detail] --compress=level --compress=method[:detail] / . gzip lz4 zstd none . . . keyword keyword=value . level long . . 0 gzip 0 . (custom) gzip . gzip lz4 zstd . zstd long . tar . --binary-upgrade (in-place upgrade) . . . --column-inserts --attribute-inserts INSERT (INSERT INTO table (column, ...) VALUES ...) . PostgreSQL . INSERT . --disable-dollar-quoting (dollar quoting) SQL . --disable-triggers (schema) . pg_dump . . --disable-triggers (superuser) . -S (superuser) . () . pg_restore . --enable-row-security (row security) . pg_dump row_security off . . pg_dump row_security on . INSERT COPY FROM . --exclude-extension=pattern pattern . -e . --exclude-extension . -e --exclude-extension -e --exclude-extension . --exclude-extension -e --exclude-extension . --exclude-table-and-children=pattern -T/--exclude-table () pattern . --exclude-table-data=pattern pattern . -t . --exclude-table-data . . --schema-only --statistics-only . --exclude-table-data-and-children=pattern --exclude-table-data () pattern . --extra-float-digits=ndigits extra_float_digits . . --filter=filename . : -t/--table --table-and-children -T/--exclude-table --exclude-table-and-children -n/--schema -N/--exclude-schema --include-foreign-data --exclude-table-data --exclude-table-data-and-children -e/--extension --exclude-extension . STDIN - . --filter . : { include | exclude } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } PATTERN . : o extension: . -e/--extension --exclude-extension . o foreign_data: . --include-foreign-data . include . o table: . -t/--table -T/--exclude-table . o table_and_children: . --table-and-children --exclude-table-and-children . o table_data: pattern . --exclude-table-data . exclude . o table_data_and_children: pattern (). --exclude-table-data-and-children . exclude . o schema: . -n/--schema -N/--exclude-schema . # () . . . Patterns . Examples . --if-exists DROP ... IF EXISTS --clean. "does not exist" ( ) . --clean . --include-foreign-data=foreignserver foreignserver. --include-foreign-data . foreignserver \d psql ( Patterns ) (wildcard) . Examples . . --include-foreign-data . . --include-foreign-data pg_dump . . --inserts INSERT ( COPY). PostgreSQL . INSERT . . --column-inserts . --load-via-partition-root COPY INSERT . . . text (collation) . --lock-wait-timeout=timeout . timeout . SET statement_timeout . ( .) --no-comments COMMENT . --no-data . --no-policies (row security policies) . --no-publications (publications) . --no-schema ( ) . --no-security-labels . --no-statistics . . --no-subscriptions (subscriptions) . --no-sync pg_dump . pg_dump (crash) . . --no-table-access-method . . () . pg_restore . --no-tablespaces tablespace . tablespace . () . pg_restore . --no-toast-compression TOAST . . --no-unlogged-table-data (sequences) (unlogged) dump . ( schema) . unlogged dump standby . --on-conflict-do-nothing ON CONFLICT DO NOTHING INSERT . --inserts --column-inserts --rows-per-insert . --quote-all-identifiers . dump (major version) PostgreSQL pg_dump . pg_dump . . --quote-all-identifiers . --restrict-key=restrict_key \restrict psql dump . dump (plain- text) --format plain --format . pg_dump . - (alphanumeric) . ( dump) . psql dump . --rows-per-insert=nrows INSERT ( COPY) . INSERT . . INSERT . --section=sectionname . pre-data data post-data . . . data (large-object) (materialized views) (foreign tables) . post-data (rules) check not-null . pre- data . --sequence-data (sequence) dump . --no-data --schema-only --statistics-only . --serializable-deferrable serializable dump snapshot dump (roll back) serialization_failure . 13 . dump (disaster recovery) . dump . dump (commit) . (batch processing) . pg_dump - . - dump . . --snapshot=snapshotname dump snapshot ( 9.100 ). dump (logical replication slot 47 ) (session) . (parallel dump) snapshot snapshot . --statistics (optimizer statistics) . --statistics-only ( ) . . --strict-names (-e/--extension) (-n/--schema) (-t/--table) // . --filter . // pg_dump --strict-names . --exclude-extension -N/--exclude-schema -T/--exclude-table --exclude-table-data . (exclude pattern) . --sync-method=method fsync ( ) pg_dump --format=directory (sync) . Linux syncfs . syncfs recovery_init_sync_method . --no-sync --format directory . --table-and-children=pattern -t/--table () pattern . --use-set-session-authorization SQL SET SESSION AUTHORIZATION ALTER OWNER . dump . dump SET SESSION AUTHORIZATION (superuser) ALTER OWNER . -? --help pg_dump . . -d dbname --dbname=dbname . dbname . dbname (connection string) . . -h host --host=host . (Unix domain socket) . PGHOST . -p port --port=port TCP . PGPORT ( ) . -U username --username=username . -w --no-password . .pgpass . (batch jobs) . -W --password pg_dump . pg_dump . pg_dump . -W . --role=rolename dump . pg_dump SET ROLE rolename . ( -U) pg_dump . (superuser) dump . PGDATABASE PGHOST PGOPTIONS PGPORT PGUSER . PG_COLOR . always auto never. PostgreSQL libpq ( Section 32.15). (DIAGNOSTICS) pg_dump SELECT . pg_dump psql(1) . libpq . pg_dump . track_counts PGOPTIONS ALTER USER false . (NOTES) template1 pg_dump . template1 template0 : CREATE DATABASE foo WITH TEMPLATE template0; dump (schema) --disable-triggers pg_dump . . --statistics pg_dump dump . CREATE STATISTICS (CREATE_STATISTICS(7)) . ANALYZE dump Section 24.1.3 Section 24.1.6. pg_dump PostgreSQL pg_dump PostgreSQL pg_dump . pg_dump PostgreSQL dump . ( 9.2 .) pg_dump PostgreSQL (major version) dump dump . pg_dump -- dump . dump dump . --quote-all-identifiers PostgreSQL . dump (logical replication subscriptions) pg_dump CREATE SUBSCRIPTION connect = false replication slot . dump . . . (truncate) . slot two_phase = false . two_phase = true two_phase (subscriber) . pg_dump -X (--no-psqlrc) psql ..SH " (EXAMPLES)" mydb SQL: $ pg_dump mydb > db.sql ( ) newdb: $ psql -X -d newdb -f db.sql (custom-format): $ pg_dump -Fc mydb > db.dump (directory-format): $ pg_dump -Fd mydb -f dumpdir (worker job): $ pg_dump -Fd mydb -j 5 -f dumpdir ( ) newdb: $ pg_restore -d newdb db.dump : $ pg_restore -d postgres --clean --create db.dump mytab: $ pg_dump -t mytab mydb > db.sql (schema) detroit emp employee_log: $ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql east west gsm test : $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql (regular expression) : $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql ts_ : $ pg_dump -T 'ts_*' mydb > db.sql (upper-case) (mixed-case) -t (double quote) ( (Patterns) ). (shell) . $ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql mytable mytable2 filter.txt :.sp include table mytable* exclude table mytable2 $ pg_dump --filter=filter.txt mydb > db.sql (SEE ALSO) pg_dumpall(1), pg_restore(1), psql(1) PostgreSQL 18.6 2026 PG_DUMP(1)