- pg_dump exports a single database consistently while it is in use, without blocking readers or writers; roles and tablespaces need
pg_dumpall --globals-only. - Plain format (
-Fp, the default) restores withpsql; custom (-Fc), directory (-Fd) and tar (-Ft) archives restore withpg_restore. - Custom and directory archives are compressed by default and support parallel restore (
pg_restore -j); only the directory format supports parallel dumps (pg_dump -j). - pg_dump refuses to dump a newer major version than its own. Use client tools of the same or a newer major version than the servers involved.
- The PostgreSQL 18 documentation says pg_dump is generally not the right choice for regular backups of production databases, except in simple cases; it complements physical backups.
What pg_dump does and what it leaves out
pg_dump is a PostgreSQL client application for exporting a database. It produces a consistent export even while the database is being used, and it does not block other users. Because it is an ordinary client, you can run it from any machine that can connect to the server, which is why it is the usual tool for copying a database between servers, versions and machine architectures. Its output can generally be loaded into newer PostgreSQL versions, unlike file-level backups.
Two limits shape how you use it. First, pg_dump dumps only one database; cluster-wide objects such as roles and tablespaces need pg_dumpall. Second, the PostgreSQL 18 manual now says that, except in simple cases, pg_dump is generally not the right choice for regular backups of production databases, and points to the backup chapter, which also covers file-system backups and continuous archiving. Treat pg_dump as the tool for logical copies, migrations and smaller databases.
Security note from the manual: restoring a dump executes code chosen by the source database's superusers. If you do not trust them, inspect the SQL first; for archive formats, pg_restore --file writes the SQL out for review.
pg_dump formats: plain, custom, directory and tar
The -F (--format) option picks the output. For most backups the custom format is the practical default: one compressed file that pg_restore can restore selectively, reorder and load in parallel. Use the directory format when you want a parallel dump of a large database.
| Format | Flag | Restore with | Compression | Parallel |
|---|---|---|---|---|
| Plain SQL script (default) | -Fp | psql | None by default; -Z compresses the whole file | No |
| Custom archive | -Fc | pg_restore | Yes, by default | Restore only (pg_restore -j) |
| Directory archive | -Fd with -f dir | pg_restore | Yes, gzip by default | Dump (pg_dump -j) and restore |
| Tar archive | -Ft | pg_restore | Not supported | No |
Compression options
-Z (--compress) takes a level or a method with optional detail: gzip, lz4, zstd or none, for example -Z zstd:9 or -Z zstd:long. For custom and directory archives it compresses each table's data and defaults to gzip at a moderate level; for plain output a non-zero level compresses the whole file.
pg_dump examples, including a remote host
Connection options are -h (host), -p (port), -U (user) and -d (database name or a full connection string). Defaults come from PGHOST, PGPORT, PGUSER and PGDATABASE. The connecting role must be able to read every table it dumps, which in practice often means a superuser for a full dump; -n and -t let a less privileged role dump the parts it can read.
# plain SQL script
pg_dump -U postgres mydb > mydb.sql
# custom-format archive (compressed), written with -f
pg_dump -U postgres -Fc -f mydb.dump mydb
# directory format, 5 parallel jobs (opens 6 connections)
pg_dump -U postgres -Fd -j 5 -f mydb_dir mydb
# remote host, schema only
pg_dump -h db.example.com -p 5432 -U app -s -f mydb_schema.sql mydb
# one table, or a pattern, excluding another
pg_dump -U postgres -t 'sales.order*' -T sales.order_log -Fc -f orders.dump mydb
# whole data, but skip the rows of a large log table
pg_dump -U postgres --exclude-table-data=audit_log -Fc -f mydb_nolog.dump mydbAvoiding password prompts: the password file
For scripts, store credentials in a password file instead of on the command line. Each line has the form hostname:port:database:username:password. On Linux and macOS the file is ~/.pgpass and must not be readable by group or others (chmod 0600 ~/.pgpass), or it is ignored. On Windows it is %APPDATA%\postgresql\pgpass.conf. PGPASSFILE points to a different file, and -w (--no-password) makes pg_dump fail instead of prompting.
db.example.com:5432:mydb:app:<your-password>pg_restore examples
pg_restore reads custom, directory and tar archives. With -d it restores straight into a database; without it, it writes the equivalent SQL script. Create the target from template0 so it starts empty, and make sure the roles that own objects exist first (or restore with --no-owner).
# restore into a new, empty database
createdb -U postgres -T template0 newdb
pg_restore -U postgres -d newdb mydb.dump
# recreate the original database name (connect to any existing database, e.g. postgres)
pg_restore -U postgres -C -d postgres mydb.dump
# overwrite objects in an existing database
pg_restore -U postgres --clean --if-exists -d mydb mydb.dump
# parallel restore with 4 jobs (custom or directory archives only)
pg_restore -U postgres -j 4 -d newdb mydb.dump
# restore selected items: list the archive, edit the list, restore from it
pg_restore -l mydb.dump > mydb.list
pg_restore -U postgres -L mydb.list -d newdb mydb.dump
# plain SQL dump: restore with psql, stop on the first error
psql -X --set ON_ERROR_STOP=on -U postgres -d newdb -f mydb.sql| Option | Effect |
|---|---|
-j N | Loads data and builds indexes and constraints with up to N sessions. Not for pipes or standard input, and not with --single-transaction. |
-1 / --single-transaction | All or nothing; implies --exit-on-error. May exhaust lock table space on very large databases; --transaction-size is the middle ground. |
-c with --if-exists | Drops objects before recreating them, without "does not exist" errors. |
-C | Creates the database named in the archive and restores into it. |
-O / --no-owner | Skips ownership commands, so objects belong to the restoring user. |
-l and -L | List the archive contents, then restore only (and in the order of) the items in an edited list. |
pg_dumpall: roles, tablespaces and whole clusters
pg_dumpall writes every database in a cluster into one SQL script, together with the global objects pg_dump skips: roles, tablespaces and privilege grants on configuration parameters. It calls pg_dump once per database, so each database is internally consistent but the snapshots of different databases are not synchronised. It connects once per database, so a password file avoids repeated prompts, and both producing and restoring a complete dump normally need superuser rights.
A common pattern is pg_dumpall --globals-only for roles and tablespaces plus a custom-format pg_dump per database, which keeps selective and parallel restore. The PostgreSQL manual notes that the globals dump is necessary for a full cluster backup when you dump databases individually.
pg_dumpall -U postgres --globals-only -f globals.sql
pg_dumpall -U postgres -f cluster.sql
psql -X -U postgres -f cluster.sql postgrespg_dump with Docker, Azure PostgreSQL and Amazon RDS
Version rule first. pg_dump can dump servers back to PostgreSQL 9.2 but refuses servers newer than its own major version, and its output is only guaranteed to load into the same or a newer major version. Microsoft's Azure guide states the same rule for pg_dump, pg_restore, psql and pg_dumpall. Upgrade your local client tools before dumping a newer server, and add --quote-all-identifiers when source and target versions differ.
Docker. The postgres image includes the client tools, so run them inside the container with docker exec. Writing the archive inside the container and copying it out with docker cp avoids redirection problems with binary output on Windows. The PostgreSQL in Docker guide shows the full commands.
pg_dump and Azure Database for PostgreSQL. Microsoft documents pg_dump with psql for smaller databases and pg_dump with pg_restore across multiple cores for larger ones. On flexible server, roles cannot be dumped with their passwords because users have no access to pg_authid, so dump roles with pg_dumpall -r --no-role-passwords and set passwords again afterwards.
Amazon RDS for PostgreSQL. AWS recommends pg_dump -Fc and pg_restore -j for imports, and notes that pg_dumpall cannot be used for importing because it needs superuser permissions that RDS does not grant. Restore with --no-owner if the source owners do not exist on the target.
pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql
pg_dump -h mydemoserver.postgres.database.azure.com -U myuser -Fc -f testdb.dump testdbScheduling pg_dump and fixing common errors
cron (Linux, macOS). Use a password file so the job does not prompt. In a crontab, an unescaped % becomes a newline, so write \% when you add a date to the file name. Task Scheduler (Windows). Put the pg_dump command in a .cmd file, keep the password in %APPDATA%\postgresql\pgpass.conf for the user the task runs as, and register it with schtasks /create.
15 1 * * * pg_dump -h localhost -U backup -w -Fc -f /var/backups/pg/mydb-$(date +\%F).dump mydbschtasks /create /tn "PostgreSQL backup" /tr C:\scripts\pg-backup.cmd /sc daily /st 01:15| Problem | Cause | Fix |
|---|---|---|
| pg_dump stops with a server version mismatch | The server is a newer major version than pg_dump | Install client tools of the server's major version or newer. |
psql cannot read a .dump file | Custom, directory and tar archives are not SQL scripts | Restore them with pg_restore. |
| Errors that roles do not exist during restore | Owners and grantees from the source are missing on the target | Restore pg_dumpall --globals-only first, or use --no-owner (and --no-privileges). |
| Duplicate object errors on restore | Target database was created from a customised template1 | Create the target with createdb -T template0. |
| Parallel dump fails partway | Another session requested an exclusive lock on a table being dumped | Re-run outside busy periods; pg_dump aborts rather than deadlock. |
| Plain restore leaves a half-loaded database | psql continues after errors by default | Set the psql variable ON_ERROR_STOP (as in the example above) or use -1, and restore into a fresh database. |
| Slow queries after restoring | Optimiser statistics are not dumped by default | Run ANALYZE after the restore. |
Frequently asked questions
What is the difference between pg_dump and pg_dumpall?
pg_dump exports one database in any of four formats. pg_dumpall exports every database in the cluster plus roles and tablespaces, as one plain SQL script restored with psql. Use pg_dumpall --globals-only alongside per-database pg_dump archives to get both.
Which pg_dump format should I use?
Custom (-Fc) for most backups: one compressed file with selective and parallel restore. Directory (-Fd) when you need a parallel dump. Plain SQL when you want to read or edit the script, or load it into another product.
How do I run pg_dump against a remote host?
Pass the connection options: pg_dump -h db.example.com -p 5432 -U app -Fc -f mydb.dump mydb. The client must be the same major version as the server or newer, and the server must accept the connection in its authentication settings.
Can pg_restore restore a plain .sql file?
No. pg_restore reads only the archive formats. A plain SQL dump is restored with psql -X -d newdb -f mydb.sql; -X skips your psqlrc file, as the manual recommends.
Does pg_dump lock the database?
It takes shared locks that do not block normal reads or writes, so other users carry on working. Operations that need an exclusive lock, such as most forms of ALTER TABLE, wait until the dump finishes.
Sources
- PostgreSQL 18 documentation: pg_dump
- PostgreSQL 18 documentation: pg_restore
- PostgreSQL 18 documentation: pg_dumpall
- PostgreSQL 18 documentation: SQL Dump
- PostgreSQL 18 documentation: The Password File
- PostgreSQL versioning policy
- postgres Docker Official Image documentation
- Microsoft Learn: Migrate using dump and restore (Azure Database for PostgreSQL)
- Amazon RDS User Guide: Importing data into PostgreSQL on Amazon RDS
- Microsoft Learn: schtasks create
- Ubuntu manpage: crontab(5)
Checked 8 October 2026.
How we research guides: our editorial method. We link only to official downloads and never host installers.