pg_dump(1)
Extract a PostgreSQL database into a script file or archive.
Synopsis
pg_dump [OPTION]... [DBNAME]Description
pg_dump is a utility for backing up a PostgreSQL database. It produces plain-text SQL scripts or custom-format archives that can be used to recreate the database from scratch or migrate it to another PostgreSQL installation.
By default, pg_dump outputs SQL commands to stdout. The output can be piped to psql to restore the database, or saved to a file for later use. Custom and directory formats allow parallel processing and selective restoration.
Common options
| Flag | What it does |
|---|---|
-f, --file=FILENAME | Write output to a file instead of stdout |
-F, --format={plain|custom|directory|tar} | Output format; plain is default SQL text, custom/directory/tar for archives |
-j, --jobs=NUM | Use parallel jobs for dumping (requires -F directory or -F tar) |
-t, --table=PATTERN | Dump only matching tables (can be used multiple times) |
-T, --exclude-table=PATTERN | Exclude matching tables from the dump |
-s, --schema-only | Dump only the schema, not the data |
-a, --data-only | Dump only the data, not the schema |
-c, --clean | Include DROP commands to clean objects before creating them |
-C, --create | Include CREATE DATABASE command at start of dump |
-U, --username=NAME | Connect as database user NAME |
-h, --host=HOSTNAME | Specify database server host |
-p, --port=PORT | Specify database server port (default 5432) |
Examples
Dump the entire database 'mydb' to a plain SQL file
pg_dump mydb > mydb_backup.sqlDump 'mydb' from one server and pipe directly to restore into 'targetdb' on another
pg_dump -U postgres -h 192.168.1.10 mydb | psql -U postgres targetdbCreate a custom-format archive suitable for selective restore with pg_restore
pg_dump -F custom -f mydb.dump mydbDump database using 4 parallel jobs into directory format for faster backup
pg_dump -F directory -j 4 -f mydb_backup mydbDump only the schema of the 'users' table
pg_dump -s -t 'users' mydb > users_schema.sqlDump only data from all tables matching 'logs*' pattern
pg_dump -a -t 'logs*' mydb > logs_data.sqlCreate a complete backup with DROP and CREATE database commands
pg_dump -c -C mydb > mydb_full_backup.sqlDump entire database excluding temporary and cache tables
pg_dump -T 'temp*' -T 'cache*' mydb > mydb_production.sql