← All notes

Answer: Use the custom (-Fc) or directory (-Fd) format and restore with pg_restore, which can run in parallel. Use pg_dumpall --globals-only for roles and tablespaces, which pg_dump does not include.

pg_dump -Fc -d shop -f shop.dump
pg_dumpall --globals-only > globals.sql

createdb shop_restored
pg_restore -j 4 -d shop_restored shop.dump

Formats

| Format | Restore with | |---|---| | plain SQL (default) | psql | | custom -Fc | pg_restore | | directory -Fd | pg_restore, parallel dump with -j | | tar -Ft | pg_restore |

Useful options

• --data-only or --schema-only.
• -t table to dump specific tables.
• If no database is given, PGDATABASE or the user name is used.

Claims

• id: C1 claim: Non-text dumps should be restored using pg_restore. source: S1 evidence: "Non-text file dumps should be restored using the pg_restore utility."
• id: C2 claim: Cluster-wide data can be dumped with pg_dumpall --globals-only. source: S1 evidence: "Cluster-wide data can be dumped alone using the pg_dumpall --globals-only option."
• id: C3 claim: pg_restore -j restores a dump in parallel. source: S1 evidence: "You can use pg_restore -j to restore a dump in parallel."
• id: C4 claim: pg_dump only dumps a single database. source: S2 evidence: "pg_dump only dumps a single database."
• id: C5 claim: Examine pg_dump output for warnings on standard error. source: S2 evidence: "one should examine the output for any warnings (printed on standard error)"

Sources

• [S1] PostgreSQL: Documentation: 18: 25.1. SQL Dump. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/backup-dump.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: pg_dump. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/app-pgdump.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_13e838r3,
  author = {{iinventedlight}},
  title = {{Backing up PostgreSQL with pg\_dump}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-pg-dump/},
  note = {Permanent ID iil:note:13e838r3. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-pg-dump.md.