iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use pg_dump to create a logical backup of one PostgreSQL database, then restore a plain SQL dump with psql or an archive with pg_restore. For most operational backups, the custom format (-Fc) is the flexible default because it supports archive inspection, selective restore and parallel restore. Choose directory format (-Fd) when you also need a parallel dump.
The destination and options matter as much as the dump itself. Restoring without --create loads into the database named with -d; restoring with --create uses the database name recorded in the archive. Treat --clean, --create, parallel jobs and archives from other people as potentially destructive or unsafe operations.
What pg_dump backs up—and what it does not
pg_dump creates a logical dump of one PostgreSQL database. A logical dump contains SQL-level objects and data that can be recreated on another PostgreSQL instance. It is different from a physical backup, continuous write-ahead-log (WAL) archiving, a storage snapshot or a complete cluster backup. PostgreSQL documents logical dumps as one part of a broader backup strategy, not a replacement for every recovery method (PostgreSQL SQL Dump documentation).
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsA dump cannot restore information that was never included in it. For example, a database dump is not automatically a backup of other databases in the same cluster, cluster-wide configuration, server certificates, external files, or a retention history. Decide separately where dumps are stored, how they are copied off the server, how long they are retained and how often a restore is tested.
#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.
Choose the dump format before you run the command
The output format determines the restore tool and the recovery options available later.
| Format | Create command | Restore command | Readable SQL file? | Selective or reordered restore | Parallel dump | Parallel restore |
|---|---|---|---|---|---|---|
| Plain SQL | pg_dump dbname > db.sql |
psql -X -d targetdb -f db.sql |
Yes | Limited; edit the script or regenerate it | No | No |
| Custom archive | pg_dump -Fc dbname > db.dump |
pg_restore -d targetdb db.dump |
No | Yes; list, filter and reorder archive entries | No | Yes |
| Directory archive | pg_dump -Fd dbname -j 5 -f dumpdir |
pg_restore -d targetdb dumpdir |
No; multiple archive files | Yes | Yes | Yes |
Plain output is a SQL script and is restored by executing it with psql. Custom format is compressed by default and stores an archive that pg_restore can inspect and restore selectively. Directory format is also an archive, but it is the format required for parallel dumps. Both custom and directory archives support parallel restore when restoring directly to a database (pg_dump documentation; pg_restore documentation).
Plan the destination and access requirements
Install client utilities whose major version is appropriate for the servers you operate, and verify that the account used for dumping can read every object and table that must be backed up. The restore account needs enough privileges to create or own the objects being restored. Extensions, roles and other cluster-level prerequisites may need to be prepared separately, depending on what the dump contains and how the source was configured.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Write down the intended destination before restoring:
- Existing, pre-created database: use
pg_restore -d destination archiveorpsql -d destination -f script. This lets the destination name differ from the source. - Database name recorded in the archive: use
pg_restore -C -d maintenance_database archive. The maintenance database is used only for the initial drop/create operation; the data is restored into the archived name. - Destructive replacement: add
--cleanonly when dropping objects that will be restored is intentional. Confirm the host, port, database and data-loss scope first.
Create and restore a plain SQL dump
Create the script
- Run the dump from a client that can connect to the source server:
pg_dump mydb > db.sql - Record the source server, PostgreSQL version, dump time and the exact command or environment used. Store the file where your backup controls can copy and protect it.
- Check that the output file exists and has a plausible size before treating the operation as complete. A successful command does not by itself prove that a future restore has been tested.
Restore into a fresh database
Create or designate the destination, then execute the script:
psql -X -d newdb -f db.sqlThe -X option prevents psql from loading a user’s startup file, reducing the chance that aliases or local settings alter an unattended restore. Plain scripts are executed in order, so an error can leave a partially restored database; inspect the command output and application logs rather than assuming that the first error is harmless.
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.
Plain SQL is useful when you need a human-readable artifact, want to review or edit statements, or must use tools that understand SQL text. It is less convenient for large selective restores and has no built-in parallel restore mode.
Create a custom archive for flexible recovery
Create the archive
pg_dump -Fc mydb > db.dumpCustom format is compressed by default. It is not intended to be read as a SQL script; use pg_restore to inspect or load it.
Restore into an already-created database
pg_restore -d newdb db.dumpThis command restores the archive into newdb, regardless of the source database’s name. Creating the destination explicitly is the clearest way to perform a clone or a migration under a new name.
Inspect entries before executing them
List the archive’s table of contents without restoring it:
pg_restore -l db.dumpFor a selective or reordered restore, copy the list to a file, edit or comment entries as appropriate, and pass it with -L:
Free tools Windows power users keep installed
One-click scans. No signup required.
pg_restore -l db.dump > restore.list
pg_restore -L restore.list -d newdb db.dumpSelection is powerful but not automatically dependency-safe. Omitting a type, schema, extension or table that another object requires can make a partial restore fail, and PostgreSQL notes that some partial restores are not guaranteed to succeed in a clean database. Validate the resulting database and application behavior instead of assuming that a filtered list is self-contained.
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.
Create a directory dump when the dump itself must be parallel
Directory format is the only pg_dump format that supports parallel dumping:
pg_dump -Fd mydb -j 5 -f dumpdirThe -j 5 value starts five worker connections in addition to the leader connection. More workers can increase throughput, but they also increase server, storage and network load. Start with a conservative value that your source system can sustain, then observe CPU, I/O, memory and connection pressure.
Parallel dumping relies on synchronized snapshots so workers see a consistent view of the database. The source server must support the snapshot behavior required by the client; if it cannot, use a nonparallel dump or an environment that provides the necessary PostgreSQL support. A directory dump is a directory tree, so copy and verify the entire directory rather than selecting individual files.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteRestore an archive under a different name
The official pattern for a fresh destination with a new name is:
createdb -T template0 newdb
pg_restore -d newdb db.dumpUsing template0 helps ensure that newdb starts empty instead of inheriting objects added to template1. The restore then targets newdb directly and does not use the source database name.
Drop and recreate the archived database deliberately
To recreate the database name stored in the archive, connect first to a maintenance database such as postgres:
Rank #4
- High capacity in a small enclosure – The small, lightweight design offers up to 6TB* capacity, making WD Elements portable hard drives the ideal companion for consumers on the go.
- Plug-and-play expandability
- Vast capacities up to 6TB[1] to store your photos, videos, music, important documents and more
- SuperSpeed USB 3.2 Gen 1 (5Gbps)
pg_restore -C -d postgres db.dumpWith -C (the short form of --create), -d postgres is used only to issue the initial drop and create commands. The actual objects and data are restored into the database name recorded in db.dump. If that name is not the name you want, do not use -C; create the desired destination and restore to it with -d.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Add --clean only when you intend to drop objects before recreating them:
pg_restore --clean --if-exists -C -d postgres db.dump--clean emits DROP commands for objects that will be restored. It can remove data or definitions that are not present in the incoming archive, and it does not make an accidental target safe. Verify connection parameters in a separate terminal and, for production work, use an approved change and recovery procedure.
Use parallel restore without overwhelming the server
Custom and directory archives can be restored with multiple jobs when loading directly into a database:
pg_restore -j 4 -d newdb db.dumpThe job count is not a universal performance setting. It depends on server CPU, client CPU, storage latency, network bandwidth, locks and the size and shape of the archive. Too many jobs can create contention and make the restore slower. Watch resource usage and increase the count only when the system has headroom.
Recommended Free Tools
Parallel jobs cannot be combined with --single-transaction. Choose between a potentially faster multi-job restore and a single-transaction requirement; do not attempt to specify both. A nonparallel restore is the option when one transaction covering the operation is more important than throughput.
Best Value
- Plug-and-play expandability
- SuperSpeed USB 3.2 Gen 1 (5Gbps)
Review archive trust and restore safety
Restoring a dump causes the destination to execute arbitrary code chosen by the source superusers, as the PostgreSQL pg_restore documentation warns (official warning). Treat an archive from an untrusted person, system or download as executable input.
- Inspect a custom or directory archive with
pg_restore --listbefore connecting it to a sensitive database. - For non-plain formats, generate SQL for review rather than executing immediately:
pg_restore --file=review.sql db.dump. Open and inspect the resulting file in a controlled environment. - Restore unfamiliar archives in an isolated test instance with no unnecessary network access or powerful credentials.
- Use a least-privileged role where possible, and confirm that extensions, ownership changes and procedural objects are acceptable.
- Never combine an unverified archive with
--cleanor--createon a production target.
Understand consistency, completeness and failure modes
A dump is logically consistent, not a point-in-time cluster backup
pg_dump captures a database-level logical view. It does not include changes that occur after the dump’s snapshot, and it does not provide continuous recovery to an arbitrary timestamp. If your recovery objective requires point-in-time restoration or protection for several databases as one unit, design a physical backup and WAL strategy in addition to logical dumps.
Partial restores can fail because of dependencies
Schema, table or object filters may omit a type, function, extension or ownership prerequisite. Read the archive list, preserve dependent objects, and test the exact filtered command against an empty test database before using it for an incident.
Errors can leave a usable-looking but incomplete database
Capture standard output and error, check the process exit status, and run post-restore checks such as expected schemas, row counts for critical tables, extension availability and application smoke tests. A database that accepts connections is not proof that every object and row restored successfully.
Version compatibility guidance
PostgreSQL’s documentation states that pg_dump can dump older PostgreSQL servers and that its output is expected to load into newer server versions than the client version. This is directional guidance, not a promise that every combination of server versions, extensions and features will work. Prefer a client from the newer target environment when practical, test cross-version migrations, and verify extension versions and ownership requirements separately (pg_dump compatibility notes).
Quick Recap
A repeatable backup-and-restore checklist
- Define the recovery objective: one database, a whole cluster, point-in-time recovery or a one-time migration.
- Choose plain, custom or directory format based on readability, selective restore and parallelism needs.
- Run the dump with a role that can read all required objects, and record source details and the command.
- Verify the output artifact and copy it according to your storage, off-site and retention policy.
- Inspect archives before restoring, especially when their origin is not fully trusted.
- Prepare an empty destination, or explicitly choose
--createand understand which name will be used. - Use
--cleanonly after confirming the target and the intended drop scope. - Choose a conservative
-jvalue for parallel operations; never pair it with--single-transaction. - Check exit status, logs, object counts and application behavior after the restore.
- Schedule restore tests so that the backup is demonstrated to be recoverable, not merely present.
Which command pattern should you use?
| Need | Recommended pattern | Reason |
|---|---|---|
| Readable script for review | pg_dump mydb > db.sql followed by psql -X -d targetdb -f db.sql |
Produces ordinary SQL and uses the standard SQL client for restore. |
| Flexible archive and selective restore | pg_dump -Fc mydb > db.dump followed by pg_restore -d targetdb db.dump |
Supports archive listing, filtering and reordering. |
| Parallel dump | pg_dump -Fd mydb -j N -f dumpdir |
Directory output is required for parallel dump. |
| Parallel restore | pg_restore -j N -d targetdb db.dump |
Custom and directory archives support multi-job restore. |
| Restore under a different database name | createdb -T template0 targetdb, then pg_restore -d targetdb db.dump |
Destination naming is explicit and does not depend on the archive’s name. |
| Recreate the archived name | pg_restore -C -d postgres db.dump |
-d is the maintenance connection; the archive’s database name is recreated. |
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.
