Guide

pg_dump formats: custom, plain, directory or tar?

The flag you pick on backup night decides what you are allowed to do at restore time. Choose it once, deliberately.

The comparison

Two things follow from that table, and they are the reason this page exists:

• Plain is the default and the weakest. Run pg_dump with no -F flag and you get uncompressed SQL that can only be replayed whole, single-threaded, in file order.
• Only the directory format can dump in parallel. --jobs at dump time is supported with -Fd and nothing else.

Plain — -Fp

A text file of SQL statements. Readable, greppable, diffable, and restorable by anything that speaks SQL. That is the whole case for it.

pg_dump -Fp -d "$DATABASE_URL" | gzip > db.sql.gz

# restore — ON_ERROR_STOP is not optional
gunzip -c db.sql.gz | psql -v ON_ERROR_STOP=1 --single-transaction -d mydb

Without -v ON_ERROR_STOP=1, psql prints errors and carries on to the end, exiting 0. That is how a partially restored database gets declared a success.

Note that pg_dumpall — the one that also dumps roles and other cluster-wide objects — emits plain SQL only. There is no -Fc for it.

Custom — -Fc

The sane default for a single database. One compressed file, and the archive keeps a table of contents, which is what unlocks everything else:

pg_dump -Fc -d "$DATABASE_URL" -f backup.dump

# restore, 4 workers
pg_restore -j 4 --no-owner -d mydb backup.dump

Selective restore works off that table of contents. List it, comment out what you do not want with a leading ;, and restore the edited list:

pg_restore -l backup.dump > toc.txt
# edit toc.txt ...
pg_restore -L toc.txt -d mydb backup.dump

# or pull one table straight out
pg_restore -t orders -d mydb backup.dump

Compression is on by default; -Z 0 turns it off if you are writing to a filesystem or object store that compresses for you.

Directory — -Fd

A directory with one compressed file per table plus a TOC file. Everything the custom format does, plus parallel dump — which for a large database is the difference between a 40-minute and a 10-minute backup window:

pg_dump -Fd -j 4 -d "$DATABASE_URL" -f backup_dir/
pg_restore -j 4 --no-owner -d mydb backup_dir/

The cost is that a backup is now a tree, not a file, so you tar it before shipping it offsite. And -j at dump time opens one connection per worker plus a leader connection, all sharing a synchronized snapshot. That does not survive a transaction pooler — through PgBouncer in transaction mode, or Supabase's pooler on port 6543, parallel dump fails. Use a direct or session-mode connection, or drop back to -Fc. (More on why the transaction pooler breaks pg_dump.)

Tar — -Ft

A tar archive with the same contents as the directory format, but uncompressed, and pg_restore -j does not accept it. There is essentially no reason to choose it over -Fc today; it exists for tooling that needs to pull members out of the archive itself.

What to pick

• Under ~50 GB, one database: -Fc. One file, compressed, parallel restore, selective restore.
• Large database, backup window matters: -Fd -j N over a direct connection, then tar the directory.
• You need to read or edit the SQL (schema review, migration to another engine, stripping a table by hand): -Fp.
• Roles, and other cluster-wide objects: pg_dumpall --globals-only, alongside your per-database dump. Skipping this is what produces "role does not exist" at restore time.

The limit all four share

pg_dump takes a consistent logical snapshot of the moment it starts. It is not point-in-time recovery: your worst-case data loss is the interval between dumps, and no format changes that. If you need an RPO measured in minutes, you need WAL archiving — pgBackRest or Barman — with the dump as a second, independent copy.

And whichever format you choose, the file is a hypothesis until it has been restored. The format only decides how fast and how selectively you can run the drill.

Related fixes: How to actually test a Postgres backup pg_restore: role "..." does not exist pg_restore: unsupported version in file header pg_dump hangs — and takes your application with it