FREE TOOLS / PG_DUMP
pg_dump command generator
Click your way to a correct pg_dump command — the right format, compression, schema and table filters, and the restore-friendly flags people forget (--no-owner, --clean --if-exists). The command updates as you go, and we show the matching pg_restore line so the round trip actually works. Nothing leaves your browser.
pg_dump --format=c --no-owner --file=backup.dump \ postgres://user:password@host:5432/dbname
restore with
pg_restore -d "$TARGET_DATABASE_URL" --no-owner backup.dumpThe flags that actually matter for backups
- --format=custom — compressed and selectively restorable. The right default for a backup; plain .sql can't restore one table without replaying the whole file.
- --no-owner — so the dump restores into a cluster that doesn't have your source roles. Skipping this is the #1 cause of “role does not exist” restore failures.
- --clean --if-exists — drops existing objects first, without erroring when they aren't there. Makes a restore idempotent.
- SELECT on sequences — not a pg_dump flag, but the most common silent failure: a read-only role granted only on tables will fail dumping sequences. Grant SELECT ON ALL SEQUENCES too.
FAQ
Which pg_dump format should I use?
Custom (-Fc) is the default choice: it's compressed and lets pg_restore selectively restore individual tables. Use directory (-Fd) when you want parallel dumps (it's the only format that supports -j). Use plain (-Fp) only when you want a human-readable .sql file you replay with psql.
What does --no-owner do, and do I need it?
It omits commands that set object ownership to the source roles. Include it when you're restoring into a different server or cluster that doesn't have those exact roles — which is almost always the case for a backup. Without it, pg_restore throws role-does-not-exist errors.
What's the difference between --clean and --create?
--clean adds DROP statements so the restore replaces existing objects (pair it with --if-exists to avoid errors when they don't exist yet). --create adds a CREATE DATABASE so the restore builds a fresh database rather than loading into the current one.
How do I exclude large log or audit tables from the dump?
Use --exclude-table-data to keep a table's structure but skip its rows (good for logs you don't need to restore), or --exclude-table to drop it entirely. Both accept patterns and can be repeated.
Does pg_dump lock my tables or take my database down?
No. pg_dump takes a consistent MVCC snapshot and reads without blocking writes, so your application keeps running. You'll see read I/O similar to a large query for the duration. On very large or busy databases, dump off-peak or from a read replica.
Don't want to run this on a cron and hope?
A pg_dump command is step one. OffsiteDB runs it on a schedule, encrypts the result into a bucket you own, and restore-drills every snapshot on real Postgres so you know it actually comes back. See the backup guides for your provider, or start a free trial.