Back to Commands

pg_dump

databaseLinux/Unix
The pg_dump command is one of the most frequently used commands in Linux/Unix-like operating systems. pg_dump Extract a PostgreSQL database into a script file or other archive file
See ExamplesView Syntax

Quick Reference

Command Name:

pg_dump

Category:

database

Platform:

Linux/Unix

Basic Usage:

pg_dump [options] [arguments]

Common Use Cases

    Syntax

    pg_dump [connection-option...] [option...] [dbname]

    Options

    Option Description
    -a, --data-only Dump only the data, not the schema
    -b, --blobs Include large objects in dump
    -c, --clean Clean (drop) database objects before recreating
    -C, --create Include commands to create database in dump
    -E, --encoding=ENCODING Dump the data in encoding ENCODING
    -f, --file=FILENAME Output file or directory name
    -F, --format=c|d|t|p Output file format (custom, directory, tar, plain text)
    -n, --schema=SCHEMA Dump the named schema(s) only
    -N, --exclude-schema=SCHEMA Do NOT dump the named schema(s)
    -o, --oids Include OIDs in dump
    -O, --no-owner Skip restoration of object ownership
    -s, --schema-only Dump only the schema, no data
    -S, --superuser=NAME Superuser user name to use in the dump
    -t, --table=TABLE Dump the named table(s) only
    -T, --exclude-table=TABLE Do NOT dump the named table(s)
    -v, --verbose Verbose mode
    -x, --no-privileges Do not dump privileges (grant/revoke)
    -Z, --compress=0-9 Compression level for compressed formats
    --binary-upgrade For use by upgrade utilities only
    --column-inserts Dump data as INSERT commands with column names
    --disable-dollar-quoting Disable dollar quoting, use SQL standard quoting
    --disable-triggers Disable triggers during data-only restore
    --inserts Dump data as INSERT commands, rather than COPY
    --no-security-labels Do not dump security label assignments
    --no-synchronized-snapshots Do not use synchronized snapshots in parallel jobs

    Connection Options:

    Option Description
    -d, --dbname=DBNAME Database to dump
    -h, --host=HOSTNAME Database server host or socket directory
    -p, --port=PORT Database server port number
    -U, --username=NAME Connect as specified database user
    -w, --no-password Never prompt for password
    -W, --password Force password prompt
    --role=ROLENAME Do SET ROLE before dump

    Examples

    How to Use These Examples

    The examples below show common ways to use the pg_dump command. Try them in your terminal to see the results. You can copy any example by clicking on the code block.

    # Basic Examples Basic
    pg_dump mydb > mydb.sql
    Backup a database to a SQL script file.
    pg_dump -U username -W -h hostname dbname > dbname.sql
    Backup a remote database with specified username and host.
    # Advanced Examples Advanced
    pg_dump -Fc mydb > mydb.dump Create a custom-format archive suitable for input into pg_restore. pg_dump -s mydb > mydb_schema.sql Dump only the schema, no data. pg_dump -a mydb > mydb_data.sql Dump only the data, no schema. pg_dump -t mytable mydb > mytable.sql Dump a specific table. pg_dump -n myschema mydb > myschema.sql Dump a specific schema. pg_dump -T mytable mydb > mydb_except_table.sql Dump all tables except the specified one. pg_dump -Fd mydb -f dumpdir Dump database into directory format. pg_dump -v -C -Fp mydb > mydb_with_create.sql Create a verbose dump with CREATE DATABASE statement using plain format.

    Try It Yourself

    Practice makes perfect! The best way to learn is by trying these examples on your own system with real files.

    Understanding Syntax

    Pay attention to the syntax coloring: commands, options, and file paths are highlighted differently.

    Notes

    The pg_dump command is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). Created by the PostgreSQL Global Development Group, pg_dump has been part of PostgreSQL since its early versions and has evolved to support various output formats and selective backup options. It's designed with both simplicity and flexibility in mind, making it suitable for various backup scenarios from simple development snapshots to complex production backup strategies. Key features of pg_dump include: 1. Consistency: pg_dump creates consistent backups by executing the entire dump in a single transaction, ensuring that the dump reflects a snapshot of the database at a single point in time. 2. Multiple Output Formats: The utility supports several output formats, including plain text SQL scripts, custom format (compressed and suitable for pg_restore), directory format (suitable for parallel restoration), and tar archive format. 3. Selective Backup: pg_dump allows users to back up specific tables, schemas, or exclude certain objects, providing flexibility for targeted backups. 4. Compression: When using the custom, directory, or tar formats, pg_dump can compress the output data, reducing storage requirements for backups. 5. Portability: Dumps can be created that are portable across PostgreSQL versions and platforms, facilitating database migrations and upgrades. 6. Schema-Only or Data-Only Options: Users can choose to dump only the database schema (structure) or only the data, useful for various maintenance and development tasks. 7. Parallel Operation: In newer versions, pg_dump supports parallel dumping of large databases, significantly reducing the time required for backup operations. Common use cases for pg_dump include: - Creating routine backups for disaster recovery - Migrating databases between servers or PostgreSQL versions - Creating development or testing copies of production databases - Generating schema scripts for version control or documentation - Extracting subsets of data for specific purposes - Creating lightweight backups of structural changes While pg_dump is excellent for logical backups, it's worth noting that for very large databases, it may be combined with other backup methods like PostgreSQL's continuous archiving (WAL archiving) and base backups for comprehensive backup strategies. The output of pg_dump is typically restored using the psql command (for plain SQL dumps) or the pg_restore utility (for custom, directory, or tar formats). The latter provides additional flexibility during restoration, including parallel processing and selective restoration of database objects. PostgreSQL also provides pg_dumpall, a related utility that can back up all databases in a PostgreSQL cluster, including global objects like roles and tablespaces that are not database-specific.

    Related Commands

    These commands are frequently used alongside pg_dump or serve similar purposes:

    pg_restore
    pg_dumpall
    psql
    createdb
    dropdb
    pgbench
    Explore All Commands

    Use Cases

    Learn By Doing

    The best way to learn Linux commands is by practicing. Try out these examples in your terminal to build muscle memory and understand how the pg_dump command works in different scenarios.

    See ExamplesTry Lab Exercises
    $ pg_dump
    View All Commands