pg_dump on a Large Database: Fast Dumps and Restores Without Hurting Production

Running pg_dump on a large database is easy to start and hard to finish well. The default command works, but it uses one CPU core, holds a snapshot and table locks for hours, and produces a file that restores even more slowly than it was dumped. With the right format, parallel jobs and a few server settings, the same dump and restore can fit into a planned window. And sometimes the right answer is not to use pg_dump at all.

First: is pg_dump the right tool?

pg_dump makes a logical copy: SQL definitions plus table data, read through a normal connection. Restoring it means loading every row again, then building every index and validating every foreign key. On a database of several terabytes that can take many hours, and the time grows with the data and the number of indexes.

That makes pg_dump a poor main backup for a large production database. For disaster recovery you want physical backups with WAL archiving, which restore at disk speed and let you recover to a point in time. We covered that in PostgreSQL point-in-time recovery. pg_dump is still the right tool for:

• moving a database to a new major version, a new platform or a managed service where physical copies are not possible;
• copying one database, schema or set of tables rather than the whole cluster;
• long-term archives that must be readable by future PostgreSQL versions;
• an extra, independent safety copy before risky changes.

Use the directory format and parallel jobs

The single most important switch is the format. Plain SQL and the custom format are written by one process. The directory format (-Fd) is the only one where pg_dump can write with several processes at once, so it is the only one that supports -j:

# directory format, 8 parallel workers (opens 9 connections), zstd (PostgreSQL 16+)
pg_dump -h db1 -d shop -Fd -j 8 -Z zstd:3 \
        --lock-wait-timeout=30s \
        -f /backup/shop.dump

# roles and tablespaces are not in pg_dump output
pg_dumpall -h db1 --globals-only -f /backup/globals.sql

A few things to know about parallel dumps:

• pg_dump opens njobs + 1 connections. Check max_connections and any connection pooler limits before using a high value.
• Parallelism is per table: each worker dumps whole tables. If one table holds most of the data, one worker spends hours on it while the others sit idle. Partitioned tables help here, because each partition is a separate table.
• All workers see the same data through a synchronized snapshot, so the dump is consistent even though it is made by several connections.
• The directory and custom formats are compressed with gzip by default. Since PostgreSQL 16 you can choose lz4 or zstd with -Z, for example -Z zstd:3. These usually compress faster than gzip, which matters when compression, not the disk, limits the dump.

Write the dump to a different disk or host than the database data. Reading and writing on the same volume is a common reason for a slow dump and for production latency during it.

What a long dump does to production

The documentation says pg_dump does not block readers or writers, and that is true. But a dump that runs for hours on a busy database has side effects you need to plan for.

Table locks. pg_dump takes an ACCESS SHARE lock on every table it dumps and holds it until the end. Normal queries are not affected, but anything that needs an exclusive lock waits: ALTER TABLE, DROP, TRUNCATE, many migrations. Worse, a waiting ALTER TABLE sits in the lock queue in front of ordinary queries on that table, so a deployment during the dump can stall the application. Do not schedule schema migrations during a dump. With a parallel dump the reverse also happens: if someone requests an exclusive lock on a table that has not been dumped yet, the worker's NOWAIT lock request fails and pg_dump aborts the whole dump.

Lock table size. All these locks are taken in one transaction. max_locks_per_transaction defaults to 64 per server process, and the shared lock table is sized from it. A database with tens of thousands of tables and partitions can make pg_dump fail with "out of shared memory". The setting can only be changed with a restart, so check it before the night of the migration.

Vacuum. The dump runs in a single long transaction with one snapshot. As long as it is open, vacuum cannot remove rows that were deleted or updated after the snapshot was taken. On tables with heavy updates, an eight-hour dump means eight hours of bloat that is only cleaned up after the dump ends.

Use --lock-wait-timeout so that pg_dump fails quickly instead of waiting forever if it cannot get its initial locks, for example behind a long migration.

Dumping from a standby

Taking the dump from a streaming replica moves the read load off the primary. Parallel dumps are supported on standbys from PostgreSQL 10. The catch is replay conflicts: the standby has to apply changes from the primary, and when they clash with the dump's snapshot, PostgreSQL cancels the dump's queries after max_standby_streaming_delay. We explained the mechanism in canceling statement due to conflict with recovery.

There are two usual ways out. You can raise max_standby_streaming_delay on a dedicated replica for the duration, accepting that it falls behind while the dump runs. Or you can enable hot_standby_feedback, which prevents the conflicts but moves the vacuum problem back to the primary. For a dedicated dump replica, the first is usually the better choice.

Fast restore with pg_restore

Restore is where most of the time goes, and pg_restore is where parallelism pays off most. With -j it runs data loading, index creation and constraint creation in several sessions at once:

psql -h db2 -d postgres -f /backup/globals.sql

createdb -h db2 shop
pg_restore -h db2 -d shop -j 8 --exit-on-error /backup/shop.dump

# pg_dump does not include optimizer statistics by default
vacuumdb -h db2 -d shop --analyze-in-stages --jobs=8

• -j works with the custom and directory formats, and the input must be a regular file or directory, not a pipe. Plain SQL dumps loaded with psql cannot be restored in parallel.
• -j cannot be combined with --single-transaction. Since PostgreSQL 17, --transaction-size lets you commit every few thousand objects instead, which the documentation recommends for large databases because a single transaction may exhaust the lock table.
• By default pg_restore continues after errors and prints the error count at the end. On a large restore, --exit-on-error is usually better than finding out after six hours that the first table failed.

The documentation on bulk loading lists server settings worth changing for the restore:

• Increase maintenance_work_mem, which speeds up CREATE INDEX and adding foreign keys.
• Increase max_wal_size to reduce checkpoints during the load.
• If the target is a fresh server, consider wal_level = minimal, archive_mode = off and max_wal_senders = 0 for the restore. This needs a restart and makes earlier base backups unusable for archive recovery, so set the values back afterwards, take a new base backup and only then build the standbys.

Statistics: the step people forget

A freshly restored database has no planner statistics until it is analyzed. In PostgreSQL 18, pg_dump can include statistics with --statistics, but the default is --no-statistics, and older versions never include them. Until ANALYZE runs, the planner works with guesses, and large joins can get very bad plans. Autovacuum will analyze tables eventually, but on a large database that can take a long time after you have already switched traffic. Run vacuumdb --analyze-in-stages right after the restore: it produces rough statistics for all tables quickly, then refines them.

Restoring only what you need

With the custom and directory formats you can list the archive contents, edit the list and restore a subset in your chosen order:

pg_restore -l /backup/shop.dump > shop.list
# edit shop.list: delete lines or comment them out with ';'
pg_restore -h db2 -d shop -j 8 -L shop.list /backup/shop.dump

The --section option splits a restore into pre-data (table definitions), data and post-data (indexes, constraints, triggers). On large migrations this lets you load data first and build indexes as a separate, monitored step. When you only need a table's structure, not its contents, --exclude-table-data at dump time skips the data of logs, queues and audit tables, which can make up a large part of the size.

Large databases: planning the window

• Measure, do not guess. Dump and restore a full-size copy once and time each phase: data dump, data load, index build. Index and constraint creation can take as long as loading the data, or longer.
• Disk. You need space for the dump itself, and on the target for the data plus every index plus WAL generated during the load.
• Network. Dumping over the network from a remote client adds transfer time. Run pg_dump close to the source and pg_restore close to the target.
• Downtime. pg_dump captures one moment. Writes made after the snapshot are not in the dump, so for a migration you either stop writes during the whole dump and restore, or use logical replication to keep the new database in sync and switch over in minutes.
• One huge table. If one table dominates, it sets the minimum time no matter how many jobs you use. Consider partitioning it in advance, or moving it separately.

Checklist for pg_dump on a large database

1. Decide whether pg_dump is the right tool or physical backup or logical replication fits better.
2. Use -Fd -j N, check max_connections, and choose lz4 or zstd on PostgreSQL 16+.
3. Check max_locks_per_transaction against the number of tables and partitions.
4. Do not run schema migrations during the dump; set --lock-wait-timeout.
5. If dumping from a standby, handle replay conflicts first.
6. Dump globals with pg_dumpall --globals-only.
7. Restore with pg_restore -j, larger maintenance_work_mem and max_wal_size, and --exit-on-error.
8. Run vacuumdb --analyze-in-stages before sending traffic to the restored database.
9. Time the whole process on a full-size copy before the real window.