Logical backups: pg_dump, pg_restore, scope and limits · PostgreSQL

A logical backup captures the contents of a database as statements that rebuild it, rather than the bytes on disk. That difference gives it capabilities a physical backup does not have, and limits a physical backup does not have either.

Formats

Format	Flag	Restore with	Selective	Parallel	Compressed	Integrity
Plain	`-Fp`	`psql`	No	No	External only	None
Custom	`-Fc`	`pg_restore`	Yes	Yes	Built in	Yes
Directory	`-Fd`	`pg_restore`	Yes	Dump and restore	Built in	Yes
Tar	`-Ft`	`pg_restore`	Yes	No	External only	Yes

Read-only / Safethe same database in two formats— Captured on 18.6. Custom format compresses by default; plain does not.

$ pg_dump -U postgres -F c -f /tmp/d.dump postgres && pg_dump -U postgres -F p -f /tmp/d.sql postgres && ls -la /tmp/d.dump /tmp/d.sql

-rw-r--r-- 1 postgres postgres  751359 /tmp/d.dump
-rw-r--r-- 1 postgres postgres 2156673 /tmp/d.sql

Read-only / Safewhat a custom-format dump knows about itself— Captured on 18.6. pg_restore -l reads the table of contents without restoring anything.

$ pg_restore -l /tmp/d.dump

;
; Archive created at 2026-08-27 21:26:07 UTC
;     dbname: postgres
;     TOC Entries: 7
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Dumped from database version: 18.6 (Debian 18.6-1.pgdg13+2)
;     Dumped by pg_dump version: 18.6 (Debian 18.6-1.pgdg13+2)
;

Plain format has no integrity check at all

Read-only / Safea custom-format dump with 200 bytes overwritten at offset 500— Real error from 18.6. The corruption was detected before any restore was attempted.

$ dd if=/dev/zero of=/tmp/bad.dump bs=1 seek=500 count=200 conv=notrunc && pg_restore -l /tmp/bad.dump

pg_restore: error: could not read from input file: end of file

A plain SQL dump is a text file of statements. It begins:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';

There is no table of contents, no checksum, and no structure to validate. Corruption in the middle is discovered when psql reaches it — part-way through a restore, having already applied everything before it, leaving a database in an unknown state.

Use plain format for a schema you want to read or diff. Use custom or directory format for anything you intend to restore.

What `pg_dump` does not capture

From lesson XIII-01, measured: zero role or tablespace definitions. The complete list of what a pg_dump of one database omits:

Roles and their passwords. pg_dumpall --globals-only.
Tablespace definitions. Same.
Other databases. One dump per database.
postgresql.conf and pg_hba.conf. Files, not catalogue.
WAL, and therefore any point in time. A dump is one instant.
Physical layout. Indexes are rebuilt, not copied.
pg_stat counters and statistics. The restored database needs ANALYZE.

That last one is easy to overlook and expensive: a freshly restored database has no planner statistics, which lesson VIII-07 measured as worth 1,003 buffers against 4 on one query. pg_restore does not run ANALYZE for you.

# after every logical restore
vacuumdb --analyze-only --jobs=8 --dbname=restored

Restoring

# whole database, parallel, into a fresh database
createdb restored
pg_restore -d restored --jobs=8 /tmp/d.dump

# schema first, inspect, then data
pg_restore -d restored --schema-only /tmp/d.dump
pg_restore -d restored --data-only  --jobs=8 /tmp/d.dump

# one table
pg_restore -d restored --table=orders /tmp/d.dump

# generate SQL without applying it, to read what would happen
pg_restore --file=would_do.sql /tmp/d.dump

The flags that matter, and the ones that hurt

--jobs=N parallelises data loading and index building. It is the single largest determinant of restore time on a large dump, and it works only with custom or directory format. Match it roughly to CPU count.

--no-owner and --no-acl skip ownership and grants. Correct when restoring into an environment with different roles — a developer laptop, a test system — and wrong for a disaster recovery restore, where the grants are part of what you are recovering.

--clean drops objects before recreating them. Combined with --if-exists it makes a restore idempotent. Without --if-exists it errors on anything that is not there.

--single-transaction wraps the whole restore in one transaction, so a failure leaves nothing behind. It is safe and it cannot be combined with --jobs, so it trades restore speed for atomicity. On a large database that trade is usually the wrong way round.

--exit-on-error — worth knowing that pg_restore continues past errors by default, reporting them at the end. A restore that emits four hundred errors and exits non-zero is easy to miss in a log, and the database it produced is incomplete. Set this flag, or check the exit status and the error count deliberately.

Where logical backups are the right tool

Cross-version migration. A dump from 15 restores into 18. Physical backups cannot cross major versions at all.

Cross-platform migration. Different architecture, different operating system, different page layout. A dump is portable; a physical backup is not.

Selective recovery. Restoring one table from a 4 TB cluster without restoring the other 3.99 TB.

Schema extraction. --schema-only produces a readable, diffable definition.

Small databases, where a dump-and-restore is quick enough that the extra capability of PITR is not worth its complexity.

Where they are the wrong tool

Anything large. A dump reads every row through the SQL layer and a restore rebuilds every index. For a 4 TB database this is hours to days; a physical restore is a file copy.

Any RPO shorter than the dump interval. A dump is a single instant. Nightly dumps mean a 24-hour RPO, and no amount of care changes that.

A running system’s consistency across databases. pg_dumpall dumps each database in its own transaction, so two databases are not captured at the same instant.

A dump takes a snapshot and holds it for its whole duration

pg_dump runs in a REPEATABLE READ transaction so that its output is internally consistent. That snapshot is held for the entire dump.

Everything in lesson VII-05 follows. On a 500 GB database taking six hours to dump:

Vacuum cannot remove any version made obsolete during those six hours, cluster-wide.
Tables under churn accumulate dead rows for six hours and do not return to their previous size afterwards.
age(backend_xmin) for the dump session climbs the whole time.

This is a real and routine cost of nightly logical backups on a large, busy cluster, and it is frequently attributed to something else. The measurement in lesson VII-05 — a 1.7 MB table becoming 19 MB from one held snapshot — is the same mechanism at a smaller scale.

Two mitigations:

Dump from a replica. The snapshot is held on the standby. hot_standby_feedback reintroduces the problem on the primary, so it must be off — at the cost of query cancellations on the standby, which Part XIV covers.

Use physical backups for large clusters and keep logical dumps for the cases in the list above.

Why directory format is the one to reach for, and the parallel dump nobody uses

Custom format is the common recommendation and directory format is better for large databases, for a reason that is easy to miss: --jobs works on the dump as well as the restore.

pg_dump -Fd -j 8 -f /backup/mydb mydb        # parallel DUMP
pg_restore -d restored -j 8 /backup/mydb     # parallel RESTORE

Custom format writes a single file, and a single file cannot be written by eight processes, so pg_dump -Fc -j 8 is not accepted. Directory format writes one file per table, so it can.

On a database with several large tables this can be the difference between a six-hour dump and a one-hour one, and it is the same command otherwise.

Three details worth knowing about parallel dumps.

They open multiple connections and need consistent snapshots across all of them. pg_dump uses exported snapshots to achieve this, so every worker sees the same instant. It requires max_worker_processes and connection headroom, and it fails cleanly if it cannot get them.

They take an ACCESS SHARE lock per table, per worker. On a cluster with a pending ALTER TABLE, from lesson IX-05, those locks queue behind it like anything else.

The output is a directory, not a file. Whatever ships it to the backup destination must handle that — a script written for a single .dump file will silently copy nothing useful.

Directory format also allows one further thing custom format cannot: a single table’s data file can be inspected or restored individually with ordinary tools, which is occasionally what you need at two in the morning.

What to take from this

Custom or directory format for anything you intend to restore. Plain format has no integrity check, and corruption is found mid-restore.
Measured: 751 kB custom against 2,157 kB plain, and a corrupted custom dump rejected before restore.
pg_dump omits roles, tablespaces, configuration files and statistics. Take pg_dumpall --globals-only alongside, and ANALYZE afterwards.
pg_restore continues past errors by default. Use --exit-on-error or check deliberately.
Directory format supports --jobs on the dump as well as the restore.
A dump holds a snapshot for its whole duration, with every consequence from lesson VII-05.

Cross-course references

Linux for Production Sysadmins — Part XLVIII (Backup tools) covers the pipe-and-exit-status trap that makes a logical dump appear to have succeeded, and Part XLIX (Restore) covers proving the output is usable.
Git, CI/CD & GitOps — Part XXXV (Secrets in Git) covers the globals dump, which contains role passwords and is the file most often committed by accident.