skip to content

How do pg_dump -j and pg_restore -j parallelise a PostgreSQL dump and restore, and what limits each one?

level: middleimportance: should knowfreq 50%

answer

  1. different work on each side
  2. one table per worker
  3. leader plus N connections
  4. shared snapshot, ACCESS SHARE locks
  5. no stdin, no tar, no single transaction

basics

~20 s

pg_dump -j dumps several tables at once, needs the directory format and opens N+1 connections sharing one snapshot. pg_restore -j loads data and builds indexes and constraints concurrently from a custom or directory archive, never with --single-transaction.

solid answer

~50 s

`pg_dump -j N` works only with the directory format (`-Fd`). A leader connection exports its snapshot, and N workers import it, so the dump stays consistent across N+1 connections. Each worker dumps one table at a time, so a single huge table still goes through one worker. The leader takes `ACCESS SHARE` locks on every table up front. If someone then queues an `ACCESS EXCLUSIVE` lock, a worker's `NOWAIT` lock attempt fails and the dump aborts. `pg_restore -j N` creates the pre-data schema first, then runs data loads, index builds and constraint creation concurrently. It accepts custom or directory archives, whether or not they were dumped with `-j`, but not tar, not standard input, and not together with `--single-transaction`. On PostgreSQL 17 and 18, `--transaction-size` gives batched commits as a middle ground. A good starting value for `-j` is the server's CPU core count.

code

bash · 12 lines
bash
# Dump with 6 workers (7 connections): directory format only
pg_dump -h prod-pg -Fd -j 6 --lock-wait-timeout=30s \
  -f /backup/cust_harbor.dir cust_harbor

# Restore with 8 jobs into an empty database
createdb -h staging-pg -T template0 cust_harbor
pg_restore -h staging-pg -d cust_harbor -j 8 --exit-on-error \
  /backup/cust_harbor.dir

# Rejected: one transaction cannot span several connections
pg_restore -d cust_harbor -j 8 --single-transaction cust_harbor.dump
# pg_restore: error: cannot specify both --single-transaction and multiple jobs

go deeper

for a junior

Know that pg_dump -j needs the directory format and that pg_restore -j works on custom or directory archives.

for a middle

Explain the shared snapshot, the N+1 connections, table-level granularity, and why stdin, tar and --single-transaction rule out a parallel restore.

for a senior

Diagnose a parallel dump aborted by queued DDL, find the one giant table that bounds the run, and choose --transaction-size over -1 on object-heavy databases.

for a principal

Weigh dump and restore parallelism against source load and target hardware, and know when restore time, not dump time, is the number that matters.

## Two different kinds of parallelism The same `-j` letter means different work on each side of a PostgreSQL logical copy: - **`pg_dump -j N`** reads several tables at the same time, so the dump finishes sooner; - **`pg_restore -j N`** runs the expensive restore steps (data loads, index builds, constraint creation) at the same time, so the restore finishes sooner. Either can be used without the other. A custom-format archive made by a serial `pg_dump` can still be restored with `pg_restore -j 8`. ## pg_dump -j: what it needs and what it costs - **Format.** Only the directory format can be written by several processes at once. Any other `-F` fails with `parallel backup only supported by the directory format`. - **Connections.** One leader plus N workers, so N + 1 connections. Leave room under `max_connections`. - **Granularity.** The unit of work is one table's data. Many tables of similar size parallelise well. One table that is most of the database still goes through a single worker and bounds the run time. Partitions count as separate tables. - **Consistency.** The leader calls `pg_export_snapshot()`, and each worker runs `SET TRANSACTION SNAPSHOT` with it, so every connection sees the same data. This needs synchronized snapshots, which primaries have had since 9.2 and standbys since 10. - **Locks.** At the start, the leader takes `ACCESS SHARE` on every table it will dump. A later `ALTER TABLE`, `TRUNCATE` or `DROP` then queues for `ACCESS EXCLUSIVE` behind it. A worker that reaches that table asks for its own `ACCESS SHARE ... NOWAIT`, is refused because of the queued request, and `pg_dump` aborts with `could not obtain lock on relation`. It aborts rather than deadlocking. Keep DDL out of the dump window. `--lock-wait-timeout` bounds only the leader's initial lock acquisition. - **Load.** N workers mean N concurrent full-table reads on the source. That is easier to absorb on a standby than on a busy primary. ## pg_restore -j: phases and restrictions A parallel restore runs in phases: 1. The **pre-data** items (schemas, types, functions, empty tables) are created over one connection, because almost everything else depends on them. 2. Workers then take the remaining items as their dependencies are satisfied: `TABLE DATA`, `INDEX`, constraint and `FK CONSTRAINT` entries, and so on. Jobs that depend on the largest tables, such as their index builds and foreign-key checks, are scheduled first, so the restore does not end with one job grinding through a big table alone. 3. Anything left over, normally nothing, is finished serially at the end. The restrictions, each of which produces a specific error: | Input or option | Parallel restore? | Why | |---|---|---| | custom archive in a regular file | yes | pg_restore can seek to each table's data | | directory archive | yes | one file per table | | tar archive | no | `parallel restore is not supported with this archive file format` | | plain SQL script | no | only `psql` reads it, serially | | custom archive on standard input | no | `parallel restore from standard input is not supported` | | `--single-transaction` | no | `cannot specify both --single-transaction and multiple jobs` | Each job is a separate connection. The docs suggest starting at the server's CPU core count. Higher values can still help, and too high a value thrashes. ## Transaction batching: --transaction-size By default, `pg_restore` commits every SQL command separately. `-1` puts everything in one transaction, which is cheapest in commit overhead. But that transaction holds a lock on every restored object until the end and can exhaust the lock table on a database with many objects. `--transaction-size=N`, added in PostgreSQL 17, commits after every N objects. It gets close to the single-transaction speed while capping lock-table use, and it implies `--exit-on-error`. ## Where the time really goes - On restore, **index builds and foreign-key validation** usually dominate, not the data load. That is why parallelising post-data items pays off. Sizing `maintenance_work_mem` for those builds belongs to performance tuning. - The **largest table plus its indexes** is the critical path on both sides. - Parallel jobs move the bottleneck to disk and network, so measure, don't just raise `-j`.

  • Why can a parallel pg_dump abort halfway with a lock error?
    The leader holds `ACCESS SHARE` on every table from the start. If another session then requests `ACCESS EXCLUSIVE`, for example through `ALTER TABLE` or `TRUNCATE`, that request queues, and every new lock request on the table queues behind it. A worker asks for its lock with `NOWAIT`, so it is refused, and `pg_dump` aborts instead of deadlocking. The cure is to keep DDL out of the dump window.
  • When is pg_restore --transaction-size better than --single-transaction?
    On databases with many objects. `--single-transaction` holds a lock on every restored object until commit and can exhaust the server's lock table. `--transaction-size`, available since PostgreSQL 17, commits every N objects. A few thousand per batch gives nearly the same speed with bounded lock use. Unlike `-1`, it does not make the restore all-or-nothing, although it also implies `--exit-on-error`.

  • pg_dump -j also works with the custom format.
  • Each parallel pg_dump worker takes its own snapshot, so the dump is inconsistent.
  • pg_dump -j 8 splits one huge table across eight workers.
  • pg_restore -j can read a custom archive streamed through a pipe.
  • Combine pg_restore -j with --single-transaction for a fast all-or-nothing restore.

Assumes PostgreSQL 18; pg_restore --transaction-size exists since 17, so on 16 and earlier the only choices are per-command commits or --single-transaction.