pg_dump and friends

pg_dump, pg_dumpall, and pg_restore together implement PostgreSQL's logical backup story. They produce a description of the database's contents that can be restored into a (possibly newer) cluster by re-running the catalog DDL plus the data. Source: src/bin/pg_dump/.

Directory layout

src/bin/pg_dump/
├── Makefile, meson.build
├── common.c              # shared between pg_dump and pg_dumpall
├── compress_io.c         # compression wrappers (gzip, lz4, zstd)
├── compress_*.c          # per-codec implementations
├── dumputils.c           # shared frontend helpers
├── filter.c              # --filter file parser
├── parallel.c            # parallel dump driver
├── pg_backup_archiver.c  # the archive abstraction (the heart of pg_dump)
├── pg_backup_archiver.h
├── pg_backup_custom.c    # custom-format archive
├── pg_backup_db.c        # libpq-backed connection helpers
├── pg_backup_directory.c # directory-format archive
├── pg_backup_null.c      # /dev/null archiver (used internally)
├── pg_backup_tar.c       # tar-format archive
├── pg_backup_utils.c
├── pg_dump.c             # the main dumper (large file)
├── pg_dump_sort.c        # topological dependency sort
├── pg_dumpall.c          # cluster-wide dumper
└── pg_restore.c          # custom-format restorer

What pg_dump does

A run of pg_dump:

1. Connects to a single database.
2. Acquires a snapshot (BEGIN; SET TRANSACTION SERIALIZABLE READ ONLY DEFERRABLE;) so the dump is consistent.
3. Catalog-walks the database: enumerates tables, indexes, views, types, functions, etc., producing a TocEntry per object.
4. Sorts the entries topologically so that creation order respects dependencies (pg_dump_sort.c reads pg_depend).
5. Emits each entry's CREATE statement.
6. For each table, dumps its data via COPY ... TO STDOUT (or INSERT statements with --inserts).

The dump is written into one of four archive formats:

Format	Flag	Description
Plain	-Fp	A single SQL script. Restored by piping into psql. Default.
Custom	-Fc	A binary archive readable by pg_restore. Allows selective restore, parallel restore, partial dependency reorder.
Directory	-Fd	A directory of one file per table plus a TOC. Only format that supports parallel dump (-j).
Tar	-Ft	A tar file. Mostly historical; lacks parallel support.

The custom and directory formats are the ones you want for large databases; both are restorable in parallel by pg_restore -j.

Archive abstraction

pg_backup_archiver.c defines the ArchiveHandle and TocEntry structures. Every format plugs in the same callbacks:

• WriteData — write bytes for a data section.
• WriteExtraToc — write format-specific TOC fields.
• StartData / EndData / StartBlob/EndBlob — bracket data sections.
• Clone / DeClone — duplicate state for parallel workers.

The dumper calls these via the abstract API; specifics live in pg_backup_custom.c, pg_backup_directory.c, etc.

Dependency sort

PostgreSQL's catalog records explicit dependencies in pg_depend and pg_shdepend. pg_dump reads them, builds a graph, and topologically sorts the TOC entries:

• Types before functions that use them.
• Tables before indexes.
• Materialized views before views that depend on them.
• Foreign keys after both referenced and referencing tables.
• ACL grants after the object they apply to.

Source: pg_dump_sort.c. Cycles in the dependency graph are broken by deferring some entries (e.g., circular FKs become ALTER TABLE ... ADD CONSTRAINT statements emitted after both tables exist).

Parallel dump

With -Fd -j N, the directory dumper runs N worker processes in parallel. Each opens its own libpq connection that imports the leader's snapshot via pg_export_snapshot() / SET TRANSACTION SNAPSHOT. The leader hands out tables; workers COPY them out.

sequenceDiagram
    participant Leader
    participant Workers as Worker[1..N]

    Leader->>Leader: BEGIN; pg_export_snapshot()
    Leader->>Workers: spawn N workers with snapshot id
    Workers->>Workers: SET TRANSACTION SNAPSHOT '...'
    loop until tables exhausted
        Leader->>Workers: assign table T
        Workers->>Workers: COPY T TO STDOUT
        Workers->>Leader: ack
    end
    Leader->>Leader: emit non-data TOC entries

Source: parallel.c.

Restoring

Plain format → psql

pg_dump -Fp mydb > mydb.sql
psql -f mydb.sql newdb

The script is just SQL; psql -f runs it.

Custom or directory format → pg_restore

pg_dump -Fc mydb > mydb.dump
pg_restore -d newdb mydb.dump

pg_dump -Fd -j 8 mydb -f mydb.dir
pg_restore -d newdb -j 8 mydb.dir

pg_restore is essentially a TOC-aware emitter: it reads the archive, optionally lists or filters its TOC (-l/-L), then emits SQL in dependency order — directly to the target database (-d) or to stdout (no -d). With -j N, it parallelizes dependency-independent restore steps.

pg_dumpall

pg_dumpall (pg_dumpall.c) handles cluster-wide objects: roles, tablespaces, and all databases. It calls pg_dump once per database and concatenates the output, prefixed with the global objects.

It only emits the plain format; for binary archives, run pg_dumpall --globals-only to capture roles/tablespaces and pg_dump -Fc per database separately.

Filter files

pg_dump --filter <file> (and pg_restore --filter) reads include/exclude rules:

include table public.users
include schema reporting
exclude table_data archive_*

Source: filter.c. Useful for partial dumps without long command lines.

Backup vs. backup

pg_dump is a logical backup — it can be restored into a different major version, can be partial, and survives across architectures. It is not suitable for very large databases (multi-TB) because the COPY of every table takes hours. For that, use pg_basebackup (a physical backup of the data directory + WAL); see pg_basebackup.

Important details

• Snapshot isolation. A long pg_dump holds an open transaction, which prevents VACUUM from reclaiming dead rows. On a busy primary this is a problem; consider running pg_dump against a streaming standby.
• --no-acl, --no-owner. Strip privilege/ownership statements; useful when restoring into a different role environment.
• --data-only / --schema-only. Skip the schema or skip the data, respectively.
• -t, -n, -T, -N. Include/exclude tables and schemas. The patterns are glob-like (*, ?, [abc]).
• Custom format flexibility. With -Fc, you can pg_restore -L list_of_objects.txt to control which objects are restored and in what order — handy when fixing a dependency surprise.
• Versions. Always use a pg_dump from the target (newer) version. New pg_dump knows about old catalogs and can adapt.

Testing

src/bin/pg_dump/t/ has TAP tests that round-trip every catalog object type through dump + restore + diff. They are some of the most useful tests in the project for catching forgotten DDL serialization.

Entry points for modification

• Adding support for a new catalog object: edit pg_dump.c to enumerate it, generate its CREATE statement (or use pg_get_* server-side functions where they exist), record dependencies. Always add a corresponding test in src/bin/pg_dump/t/.
• Tweaking sort order: pg_dump_sort.c. Be very careful — restore correctness depends on it.
• A new archive format would touch pg_backup_archiver.c plus a new pg_backup_<format>.c file.

For the wire protocol pg_dump uses, see libpq. For physical backups (the alternative), see Apps.