pg_dump is the standard PostgreSQL command-line utility that creates a logical backup of a single database, exporting its schema and data as SQL statements or a structured archive that can be restored with psql or pg_restore. It reads the database through a consistent snapshot, so the dump reflects one moment in time even while applications keep writing.
Among backup tools for PostgreSQL databases, pg_dump is the counterpart of mysqldump in MySQL. It is ideal for migrations, version upgrades, selective restores and portable copies, but it is not a substitute for physical backups with continuous archiving, because it cannot provide point-in-time recovery.
How pg_dump produces a consistent backup
pg_dump connects to the server as an ordinary client and opens a transaction at the repeatable read isolation level. Under PostgreSQL’s multiversion concurrency control, every query in that transaction sees the same snapshot, so tables dumped minutes apart remain mutually consistent, including foreign key relationships.
At the start, pg_dump takes an ACCESS SHARE lock on every table it will dump. This lock does not block reads or writes, but it blocks schema changes such as ALTER TABLE or DROP TABLE until the dump finishes. It then reads the system catalogs to generate object definitions and copies table contents with the COPY protocol.
The output describes objects, not storage. A dump contains CREATE statements, table data, indexes, constraints, functions and permissions, while indexes are rebuilt from definitions during restore. Cluster-wide objects such as roles and tablespaces are not included; they are exported with pg_dumpall using the --globals-only option.
Output formats and when to use them
pg_dump supports four output formats, chosen with -F. The format determines whether restore can be selective and parallel, so it should be chosen before the first backup runs.
| Format | Option | Restore with | Main advantage |
|---|---|---|---|
| Plain SQL | -Fp (default) | psql | Readable and editable text |
| Custom | -Fc | pg_restore | Compressed, selective and reorderable restore |
| Directory | -Fd | pg_restore | Parallel dump and restore with -j |
| Tar | -Ft | pg_restore | Single archive file, no compression by default |
For most production use the custom or directory format is the better choice. The custom format produces one compressed file with a table of contents, and pg_restore can list it with -l, edit the list and restore only chosen objects with -L. The directory format writes one file per table and is the only format that supports parallel dumping.
Key options and parameters
pg_dump has many options, but a small set covers most operational needs. The table groups the most useful ones by purpose.
| Option | Effect | Typical use |
|---|---|---|
-j N | Dumps N tables in parallel | Large databases in directory format |
-Z level or method | Sets compression level or algorithm | Balancing size and CPU time |
-n and -N | Includes or excludes schemas | Dumping one application schema |
-t and -T | Includes or excludes tables | Skipping large log tables |
-s and -a | Schema only or data only | Building empty test databases |
--no-owner | Omits ownership commands | Restoring under a different role |
--serializable-deferrable | Waits for a fully serializable snapshot | Dumps used for reporting |
Recent PostgreSQL versions accept gzip, lz4 and zstd compression methods. Parallel jobs open one extra connection per worker, so max_connections and server load must allow for them, and each worker adds its own snapshot synchronization.
pg_dump compared with other backup methods
pg_dump is one of several backup approaches for PostgreSQL, and a robust strategy usually combines a logical and a physical method.
| Method | Type | Strength | Limitation |
|---|---|---|---|
| pg_dump | Logical backup | Portable, selective, version-independent | Slow restore for large databases |
| pg_dumpall | Logical backup | Whole cluster including roles | Plain SQL only, no parallelism |
| pg_basebackup | Physical backup | Fast full copy of the cluster | Same major version and platform |
| Continuous WAL archiving | Physical backup | Point-in-time recovery | More setup and storage |
| Storage snapshots | Physical backup | Near-instant capture | Must be crash-consistent across volumes |
Because a logical dump is replayed as SQL, restore time includes rebuilding every index and validating constraints. For a database of several hundred gigabytes, this can take hours, while a physical restore copies files directly. That difference in recovery time objective is the main reason pg_dump alone rarely protects large production systems.
Restoring and common mistakes
A dump is only useful if restores are tested regularly. Typical restore commands use pg_restore -d target -j 4 --no-owner dump.file for archive formats, or psql -f dump.sql for plain SQL. The options --clean --if-exists drop existing objects first, and --create recreates the database itself.
- Forgetting global objects: roles and their passwords must be dumped separately with pg_dumpall.
- Using an old pg_dump against a newer server: pg_dump refuses or produces incomplete output; use the newer client, which can dump older servers.
- Running long dumps during migrations: the ACCESS SHARE locks make concurrent schema changes wait.
- Choosing plain format for large databases, which rules out parallel and selective restore.
- Holding old snapshots for hours, which delays vacuum cleanup and can cause table bloat on busy systems.
In plain words
pg_dump makes a complete written recipe of a PostgreSQL database. The recipe explains how to recreate every table. It also lists all the data to put back in. Because it is a recipe and not a photocopy of the files, it can be used on a different server or a newer version.
It captures one consistent moment, even while people keep using the database. Think of freezing a single frame of a video. The downside is speed. Rebuilding from a recipe takes longer than restoring a photocopy, so very large databases also need faster backup methods.
A real-world example
A software company needs to move a 120 GB PostgreSQL database from an old server running version 12 to a new cluster running a current major version. The team installs the newest client tools and runs pg_dump in directory format with eight parallel jobs, excluding a large audit table that is archived separately. Roles are exported with pg_dumpall in globals-only mode.
On the new server, the team restores the globals first and then runs pg_restore with eight jobs. A rehearsal the week before measured the full process at about 90 minutes, which fits the planned maintenance window. On migration night the restore completes on schedule, application tests pass, and the old server is kept read-only for two weeks as a fallback.