Use pg_dump to create a logical backup of one PostgreSQL database, then restore it with psql (plain SQL format) or pg_restore (custom and directory archives). For most production workflows, custom format (-Fc) is the flexible default: it is compressed, can be inspected, restored selectively and restored in parallel. Choose directory format (-Fd) when you also need parallel dumping. Always decide the destination database and the effect of options such as --clean and --create before running a restore.
What pg_dump backs up—and what it does not
pg_dump creates a logical dump of one database: schema definitions, table data, indexes, constraints and other database objects that are included by the command options. It does not represent a complete PostgreSQL disaster-recovery plan by itself. Clusters may also require separate planning for roles, tablespaces, configuration, write-ahead-log (WAL) archiving, point-in-time recovery and copies stored away from the database server. The official overview explains the SQL-dump approach at PostgreSQL’s SQL Dump documentation.
As an Amazon Associate I earn from qualifying purchases.
A dump cannot restore information that was never included in it. For example, a database dump is not a substitute for a tested copy of cluster-level roles or for WAL needed to recover transactions that occurred after the dump was taken. Define a recovery-point objective (how much recent data you can lose) and a recovery-time objective (how quickly service must return), then choose additional backup methods when a logical dump alone cannot meet them.
Choose the dump format before you run the command
The format determines which restore tool you use and how much control you have later. The following comparison follows the capabilities documented by the PostgreSQL 18 pg_dump reference and pg_restore reference.
#1 Best Overall
- Easily store and access 2TB to content on the go with the Seagate Portable Drive, a USB external hard drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
| Format | Create example | Restore with | Readable SQL file? | Selective or reordered restore | Parallel dump | Parallel restore |
|---|---|---|---|---|---|---|
| Plain | pg_dump mydb > db.sql |
psql |
Yes | Limited; edit the SQL or produce a new dump with suitable options | No | No |
Custom (-Fc) |
pg_dump -Fc mydb > db.dump |
pg_restore |
No (archive) | Yes; list, filter and reorder archive entries | No | Yes |
Directory (-Fd) |
pg_dump -Fd mydb -j 5 -f dumpdir |
pg_restore |
No (directory archive) | Yes | Yes | Yes |
Custom format is compressed by default and is usually the most convenient general-purpose archive. Directory format stores the archive as multiple files and is the only format that supports a parallel dump. Plain SQL remains useful when you want a script that can be reviewed, edited or fed directly to psql.
Prepare for a reliable dump
- Install a PostgreSQL client version that is appropriate for the server you are dumping and confirm the binaries being used with
pg_dump --version,pg_restore --versionandpsql --version. - Use a role with permission to read every object you intend to include. A dump can fail or omit inaccessible objects when the role lacks the necessary privileges.
- Make sure the destination for the output has enough space. A custom archive is compressed, but temporary working space and the eventual restored database require additional capacity.
- Record the source host, database name, PostgreSQL versions, dump options, date and checksum alongside the dump. This metadata makes a later recovery reproducible.
- Decide where copies will live and how long they will be retained. The command references describe dump mechanics, not an off-site-copy or retention policy; those choices must match your infrastructure and recovery objectives.
PostgreSQL’s compatibility guidance is directional rather than universal: a newer pg_dump can dump older servers, and its output is expected to load into newer server versions than the client version. Test the exact source, client and destination versions you operate instead of treating every cross-version combination as guaranteed.
Create a plain SQL dump
Run the dump
pg_dump mydb > db.sqlThe command writes SQL statements to db.sql. It does not change the source database. Protect the file because it contains the database contents and may include sensitive information.
Restore into an already-created database
Create an empty destination first, then execute the script:
createdb -T template0 newdb
psql -X -d newdb -f db.sqltemplate0 gives you a clean database without custom additions inherited from template1. The -X option tells psql not to load a user’s startup file, preventing aliases or settings in ~/.psqlrc from changing the restore.
Plain SQL is easy to inspect with a text editor, but it does not provide the archive table of contents and parallel restore controls available with custom and directory formats.
Create a custom archive for flexible restores
Produce the archive
pg_dump -Fc mydb > db.dumpThe resulting archive is compressed by default. Restore it into a database that already exists:
pg_restore -d newdb db.dumpThe database named with -d is the connection target for the restore. It does not have to use the same name as the source database when you omit -C (also written --create).
Rank #2
- Easily store and access 5TB of content on the go with the Seagate portable drive, a USB external hard Drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Inspect the archive before executing it
pg_restore -l db.dumpThis lists archive entries such as schemas, tables, data and indexes. Save the list, edit it to remove or reorder entries, and pass the edited file with -L when you need a selective restore:
pg_restore -l db.dump > restore.list
# edit restore.list, then:
pg_restore -L restore.list -d newdb db.dumpA selective restore is not automatically dependency-safe. Omitting a type, schema or extension that another object needs can cause errors, and PostgreSQL cautions that some partial restores are not guaranteed to succeed in a clean database. Test a tailored list against a disposable destination before relying on it for an incident.
Create a directory archive and a parallel dump
Use directory format
pg_dump -Fd mydb -j 5 -f dumpdirDirectory format is required for parallel dumping. The -j 5 value starts five worker connections in addition to the leader connection; it is an example, not a universal recommendation. Each worker increases database and I/O load, so choose a value the server can sustain.
Free tools Windows power users keep installed
One-click scans. No signup required.
Parallel dumping relies on synchronized snapshots so all connections see a consistent view. PostgreSQL versions that do not provide the required synchronized-snapshot support cannot safely provide the same consistency for this mode. If that support is unavailable, use a nonparallel dump or upgrade the environment rather than assuming worker output is a single point-in-time copy.
Restore the directory archive
pg_restore -d newdb dumpdirYou can inspect a directory archive with the same pg_restore -l dumpdir command and use -L for a tested subset.
Restore under a different database name
To move an archive from mydb into a new name, create an empty target and restore without --create:
createdb -T template0 newdb
pg_restore -d newdb db.dumpThis is the safest pattern when the destination name is deliberate and you want to verify the target before loading data. Confirm that you are connected to the intended server and that the destination is empty or has been prepared for the objects being restored.
Recommended Free Tools
Drop and recreate the database recorded in the archive
Use --create (short form -C) only when you intend to recreate the archived database name:
Rank #3
- Easily store and access 1TB to content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop. Reformatting may be required for Mac
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
pg_restore -C -d postgres db.dumpHere, postgres is only the initial connection used to issue the drop and create commands. The data is then restored into the database name stored in the archive. It is not a way to rename the database. If you need a different name, omit -C and connect directly to the pre-created destination as shown above.
The --clean option issues DROP commands for objects that the restore will recreate:
pg_restore --clean --if-exists -d newdb db.dump--if-exists suppresses notices for objects that are not present, but it does not make --clean harmless. Dropping objects can destroy data that is not included in the archive. Confirm the host, database and intended loss scope, and take a separate safety copy when the target contains anything valuable.
Use parallel restore carefully
Custom and directory archives can be restored in parallel when writing directly to a database:
pg_restore -j 4 -d newdb db.dumpThe four jobs shown are an example. Effective parallelism depends on server CPU, client CPU, storage latency, network bandwidth, indexes and workload from other sessions. Too many jobs can create lock, I/O or CPU contention and finish more slowly than a smaller value. Start conservatively, observe the server, and adjust for your hardware.
Parallel jobs cannot be combined with --single-transaction. If all-or-nothing transaction semantics are more important than speed, use a serial restore and the transaction option where supported by your workflow. If speed is the priority, run parallel restore against an isolated destination and validate it before cutover.
Security: treat every archive as executable input
A restore executes SQL chosen by the source database superusers. PostgreSQL’s documentation states: “Restoring a dump causes the destination to execute arbitrary code of the source superusers’ choice.” Read the full warning in the official pg_restore documentation.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11- Do not restore an archive from an untrusted person or system directly into a production server.
- For a plain dump, read the SQL before running it. For custom or directory archives, emit SQL for review instead of executing immediately:
pg_restore --file=review.sql db.dumpReview ownership changes, extensions, functions, triggers, procedural code and commands that create or alter roles. Test the reviewed output in an isolated environment, use a least-privilege connection where practical, and restrict access to the dump files themselves.
Rank #4
- Easily store and access 4TB of content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Validate the recovery, not just the command
A command that exits successfully is not proof that the application can use the restored database. After each restore:
- Check the command’s exit status and capture its log.
- Connect with the same application role and run representative reads and writes in a test environment.
- Verify row counts or other known invariants for critical tables.
- Confirm extensions, sequences, constraints, indexes and ownership are present.
- Run application migrations or health checks only after confirming the restored schema matches the expected version.
- Document the elapsed restore time and any manual steps so the recovery procedure can be repeated under pressure.
Keep at least one restore test separate from the server that produced the dump. This catches corrupt files, missing credentials, incompatible extensions, insufficient disk space and assumptions about database names before an actual outage.
Troubleshoot common failures
“Database does not exist”
Without -C, pg_restore -d name expects name to exist. Create it from template0, check the host and port, and retry. With -C, connect to a maintenance database that already exists; the archive’s recorded name controls the recreated database.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteObjects already exist or ownership errors
Restore into a genuinely empty database, or intentionally use --clean after confirming its destructive effect. Ownership and privilege errors usually mean the destination lacks the roles referenced by the dump; create the required roles through your controlled administration process or adjust the dump and restore plan rather than granting broad permissions casually.
Selective restore fails on dependencies
Use pg_restore -l to inspect the list and include prerequisite schemas, types, extensions and tables before dependent objects. A partial archive is not guaranteed to load into a clean database, so iterate in a disposable target and retain the working list.
Parallel jobs are slower or fail under load
Reduce -j, check server CPU, locks, storage throughput and network saturation, and schedule the operation during a quieter period. Remember that dump workers are in addition to the leader connection.
Cross-version restore behaves unexpectedly
Check the exact client and server versions and test the combination. PostgreSQL’s compatibility statement supports a newer client dumping an older server and output loading into newer servers, but it is not a promise that arbitrary old-to-new or new-to-old combinations will work without adjustments.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →A practical decision checklist
- Choose plain SQL when human-readable, directly executable text is the priority.
- Choose custom format for compressed archives, archive inspection, selective restore and parallel restore.
- Choose directory format when parallel dumping is required, accepting a multi-file archive that must remain intact.
- Use
-Conly when the archived database name should be recreated; omit it to restore into a deliberately named target. - Use
--cleanonly after confirming exactly which target objects may be dropped. - Inspect untrusted archives and test every recovery procedure before depending on it.
For option details and version-specific behavior, consult the pg_dump documentation, the pg_restore documentation and PostgreSQL’s parallel restore feature description.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
