PostgreSQL ships with two backup utilities, and the difference between them is the source of a surprising number of painful 2 AM discoveries. pg_dump backs up a single database. pg_dumpall backs up an entire Postgres cluster, including the roles, permissions, and settings that pg_dump leaves behind. Teams that only run pg_dump discover during a server rebuild that none of their users or grants exist. Teams that only run pg_dumpall discover during an incident that they cannot restore one table without replaying a giant SQL file through psql.
This guide covers what each tool actually captures, the flags that matter in production, restore mechanics with pg_restore versus psql, and the combined strategy that production teams should run. The recipes assume Postgres 13 or newer, though nearly everything applies back to much older versions.
What pg_dump does
pg_dump exports a single database into a consistent, logical backup. Two properties make it the daily driver for production backups.
First, it is consistent without blocking. pg_dump runs inside a transaction using Postgres's MVCC snapshot machinery, so you get an exact picture of the database at the moment the dump began, while reads and writes continue normally. You do not need a maintenance window to run it, which is what makes hourly backup schedules practical.
Second, with the right format, it is selectively restorable. This is the property most teams underuse, and it hinges on the format flag.
Output formats: always prefer custom format
pg_dump supports four formats via -F:
- Plain (
-Fp, the default): a giant SQL script. Human-readable, restorable with psql, but all-or-nothing and uncompressed unless you pipe through gzip. - Custom (
-Fc): a compressed binary archive restorable with pg_restore. Supports selective restore of individual tables, restore reordering, and parallel restore. This should be your production default. - Directory (
-Fd): like custom format but one file per table, which unlocks parallel dumping with-j. The right choice for large databases. - Tar (
-Ft): mostly historical; custom format does everything it does, better.
A production-ready dump of one database looks like this:
pg_dump -Fc -h db.internal -U backup_user \
--no-password myapp_production \
> myapp-$(date +%Y%m%d-%H00).dumpFor a database large enough that single-threaded dumping is slow, switch to directory format and parallel jobs:
pg_dump -Fd -j 4 -h db.internal -U backup_user \
-f /backups/myapp-$(date +%Y%m%d-%H00) myapp_productionFlags worth knowing
--schema and --table narrow the dump to specific schemas or tables, useful for huge tables that deserve their own schedule. --exclude-table-data keeps a table's structure but skips its rows, perfect for log or session tables that bloat dumps with data nobody needs to restore. --serializable-deferrable guarantees a serializable-consistent snapshot for workloads with strict consistency needs. And -Z adjusts compression level in custom format if you want to trade CPU for size.
What pg_dumpall does
pg_dumpall walks every database in the cluster and dumps them all, plus the global objects that exist outside any single database: roles (users and groups), role passwords, role memberships, tablespaces, and cluster-wide settings like per-role configuration. None of those are included in pg_dump output, because they do not belong to a database.
The catch: pg_dumpall only emits plain SQL. No custom format, no compression built in, no pg_restore, no selective restore, no parallelism. For a cluster of any real size, a full pg_dumpall is slow to produce and slower to restore. That makes it a poor choice as your primary backup mechanism, but indispensable for one specific job:
pg_dumpall --globals-only -h db.internal -U postgres \
> globals-$(date +%Y%m%d).sql--globals-only captures just the roles, memberships, and tablespace definitions in a small, fast file. This is the missing piece that makes pg_dump backups actually rebuildable: without it, restoring to a fresh server fails with a wall of "role does not exist" errors, and you reconstruct your permission model from memory during an outage.
When to use which
The decision comes down to a few production scenarios.
Hourly or daily backups of an application database: pg_dump in custom format. Fast, compressed, non-blocking, selectively restorable. This is the backbone of any Postgres backup strategy, and the granularity is what lets you treat backups as checkpoints you can roll back to when a bad migration ships.
Roles, grants, and cluster settings: pg_dumpall --globals-only on a daily schedule. It is tiny and changes rarely, but the day you rebuild a server it saves hours.
Migrating an entire small cluster: full pg_dumpall can be acceptable. For a dev box with a handful of small databases, one file restored with one psql command is convenient. Beyond that scale, per-database pg_dump plus globals wins on every axis.
Managed Postgres (RDS, Supabase, Neon, PlanetScale-style platforms): you typically cannot run pg_dumpall fully because superuser access is restricted, and provider snapshots cover infrastructure failure. But provider snapshots are full-instance and often daily; granular pg_dump backups remain your tool for restoring a single table to a recent point without rolling back the world. Note that some platforms restrict role inspection in ways that affect --globals-only too; capture what your provider permits.
Restoring: pg_restore vs psql
The format you dumped in dictates the restore tool. Plain SQL restores through psql:
psql -h db.internal -U postgres -d myapp_production \
-f myapp-20260604.sqlCustom and directory formats restore through pg_restore, which is where the selective options live:
# Restore the whole database, 4 parallel jobs
pg_restore -h db.internal -U postgres -d myapp_production \
-j 4 --clean --if-exists myapp-20260604-1400.dump
# Restore ONE table into a scratch database for inspection
createdb myapp_scratch
pg_restore -d myapp_scratch -t orders myapp-20260604-1400.dumpThat second recipe is the single most useful pattern in incident response: restore the damaged table into a scratch database, inspect it, then copy the verified rows across. You never have to take production down to investigate. --clean --if-exists drops and recreates objects on a full restore; -l lists archive contents and -L replays a filtered list when you need surgical control over exactly what gets restored, and in what order.
Rebuilding a server from scratch, order matters: restore globals first, then each database:
psql -U postgres -f globals-20260604.sql
createdb myapp_production
pg_restore -U postgres -d myapp_production -j 4 myapp-20260604.dumpPerformance tuning for large production dumps
Once a database crosses a few tens of gigabytes, dump performance starts to matter, both for how long the backup runs and how much load it places on production. A few levers go a long way.
Parallelize with directory format. -Fd -j N dumps N tables concurrently, and on a multi-core server with fast storage, four to eight jobs routinely cut dump time by 60 to 80 percent. The same flag works on restore with pg_restore, where it matters even more: index rebuilding is CPU-bound and parallelizes beautifully.
Dump from a replica when you have one. Pointing pg_dump at a streaming replica moves the read load off your primary entirely. The replica's data is seconds behind at most, a fine trade for zero production impact.
Tune compression to your bottleneck. Custom format compresses at level 6 by default. If the dump host has CPU to spare and the network to storage is slow, raise it; if CPU is the constraint, drop to -Z 3, which is usually 2x faster for modestly larger files.
Exclude what you do not need. The fastest gigabyte to dump is the one you skip. Session tables, queue tables, and append-only log tables are prime candidates for --exclude-table-data, with their schemas still captured for a clean restore.
Production pitfalls that bite real teams
Version skew. Always dump with a pg_dump at least as new as the server you are dumping, and never restore to a Postgres major version older than the dump's source assumptions. After every major version upgrade, run your backup job manually once and confirm it still succeeds; the upgrade is the classic moment backup scripts start failing silently.
Globals nobody captured. Worth repeating, because it is the most common gap we see: if your backup strategy is pg_dump only, your roles and grants are not backed up. Schedule the globals dump.
Dumps that never leave the server. A backup stored on the database server shares its fate: disk failure, region outage, or an attacker takes both at once. Ship every dump to off-site object storage, S3, Spaces, Backblaze, or your own Nextcloud, as part of the same job that creates it.
Backups nobody verifies. A dump file's existence proves nothing. Verify size against expectations after every run, alert on failure through a channel humans actually read, and test-restore quarterly. The full pipeline (scheduling, shipping, rotation, verification, alerting) is exactly what we walk through in our guide to hourly database snapshots.
Frequently asked questions
Does pg_dump lock my database?
No. pg_dump takes only the access-share locks that any reader takes, so normal reads and writes proceed unimpeded. The one thing it blocks, and is blocked by, is DDL: an ALTER TABLE during a dump waits for the dump's lock on that table, and vice versa. Schedule schema migrations away from backup windows.
Can I use pg_dump for very large databases?
Logical dumps remain practical further than most people expect, into the hundreds of gigabytes with directory format and parallel jobs. Past that, restore time becomes the real constraint, and you graduate to physical backups (pg_basebackup, WAL archiving) for full-cluster recovery while keeping pg_dump for granular table-level restores. The two are complements, not competitors.
pg_dump vs pg_basebackup: which one do I want?
They answer different questions. pg_basebackup copies the entire cluster at the file level and, combined with WAL archiving, supports point-in-time recovery of the whole instance; it is the foundation of replication and full-cluster disaster recovery. pg_dump produces a logical, portable, selectively restorable copy of one database. You cannot restore one table from a basebackup without standing up a whole instance, and you cannot stand up a streaming replica from a pg_dump. Production setups of any seriousness eventually run both, but if you are choosing where to start, granular pg_dump checkpoints cover the incidents teams actually hit most often: one bad change to one part of the data.
Can pg_dump back up a single table?
Yes: pg_dump -Fc -t orders myapp_production dumps just the orders table, and -t accepts patterns like -t 'analytics_*' for groups of tables. The same selectivity exists at restore time with pg_restore -t, which is usually the more useful direction: dump everything on schedule, restore precisely what the incident requires.
Does pg_dump include indexes?
It includes index definitions, not index data; indexes are rebuilt from scratch during restore. This makes dumps smaller and is also why restores benefit so much from parallelism and from temporarily raising maintenance_work_mem during a large restore.
What about compression beyond the default?
Postgres 16 and newer support zstd and lz4 compression in custom and directory formats, for example --compress=zstd:9. zstd typically produces files 25 to 40 percent smaller than the default zlib at comparable speed, which adds up quickly across hourly retention tiers. On older versions, piping plain format through zstd achieves a similar result at the cost of losing pg_restore's selective machinery, so on pre-16 servers most teams stay with custom format and accept the default compression.
How often should I run pg_dump in production?
As often as your tolerance for data loss dictates. For an active application, hourly is the practical standard: frequent enough that a bad deploy costs minutes of data rather than a day, cheap enough that storage cost stays trivial under a tiered retention policy.
The short version
Use pg_dump with custom format (or directory format with parallel jobs for big databases) as your primary, frequent backup: it is consistent, non-blocking, compressed, and lets you restore a single table when one bad change, increasingly an AI-drafted one, hurts one part of production. Use pg_dumpall with --globals-only on a daily schedule so roles and permissions survive a rebuild. Restore with pg_restore into scratch databases for surgical recovery, ship everything off-site, and rehearse the restore before you need it.
If you want the whole pipeline without owning the scripts, Ottomatik's automated PostgreSQL backups run pg_dump on your schedule, hourly through monthly, ship to your own storage, rotate retention tiers automatically, and alert your team in Slack when anything fails. The first backup takes about three minutes to set up, which is meaningfully less time than reading this article took.
