PostgreSQL Backup and Restore with pg_dump and pg_restore

A working backup is the difference between a bad afternoon and a bad week. PostgreSQL provides pg_dump and pg_restore for logical backups. These client utilities work with local or remote databases and produce portable archives that you can load into a compatible PostgreSQL server.

This guide explains how to use pg_dump for single databases, pg_dumpall for cluster-wide backups, and pg_restore to bring a database back from a dump file. It covers the common output formats, partial backups, and the practical mistakes that turn a backup into an unrestorable file.

Quick Reference #

For a printable quick reference, see the PostgreSQL cheatsheet .

Logical vs Physical Backups #

Before reaching for pg_dump, it helps to know what it is and what it is not.

A logical backup exports the database as SQL statements or as a portable archive that can be replayed against an empty database. Plain-text dumps are human-readable, and archive formats support selective restores. Logical dumps are also the normal choice when moving a database to a newer PostgreSQL major version.

A physical backup copies the data files on disk, usually with pg_basebackup or a tool such as pgBackRest. Physical backups are faster to restore for large clusters and can be combined with WAL archiving for point-in-time recovery, but they are tied more closely to the PostgreSQL server version and storage layout.

pg_dump and pg_restore are useful for logical exports, migrations, and smaller recovery jobs. They do not replace a physical backup and WAL retention plan when your recovery requirements include restoring the entire cluster to a specific point in time.

pg_dump Syntax #

The general syntax for pg_dump is:

pg_dump [OPTIONS] DBNAME

Common options:

• -h, --host - Database host
• -p, --port - Database port
• -U, --username - PostgreSQL user
• -F, --format - Output format: p (plain SQL), c (custom), d (directory), or t (tar)
• -f, --file - Output file or directory path
• -j, --jobs - Parallel workers (directory format only)
• -t, --table - Dump only matching tables
• -n, --schema - Dump only matching schemas
• --data-only - Skip schema and dump only data
• --schema-only - Skip data and dump only schema

pg_dump connects as a regular client. The connecting role needs enough privileges to read every selected object. Running routine backups with a dedicated role is safer than allowing a script to connect as the postgres superuser.

Plain SQL Dump #

The simplest backup is a plain SQL file. It is human-readable, can be opened in any editor, and is restored with psql instead of pg_restore.

Dump a single database to a SQL file:

sudo -u postgres pg_dump mydb > mydb.sql

The output is a sequence of CREATE, COPY, and ALTER statements. Restore it into a database created from template0 so that local additions to template1 do not cause duplicate-object errors:

sudo -u postgres createdb -T template0 mydb_restored
sudo -u postgres psql -X --set ON_ERROR_STOP=on -d mydb_restored -f mydb.sql

The -X option prevents settings in .psqlrc from changing the restore, while ON_ERROR_STOP makes psql stop at the first SQL error. Without it, psql continues and can leave a partially restored database.

Plain SQL is convenient for small databases, code review, and migrations between PostgreSQL versions. It cannot be restored in parallel and does not support the flexible object selection available in archive formats.

Custom Format #

The custom format is a practical default for logical backups. It is a compressed archive that supports selective and parallel restore, plus inspection with pg_restore --list.

Dump a database in custom format:

sudo -u postgres pg_dump -Fc -f mydb.dump mydb

-Fc selects the custom format, and -f writes the output to a file. The resulting mydb.dump is a binary archive, not SQL text, so you restore it with pg_restore:

sudo -u postgres createdb -T template0 mydb_restored
sudo -u postgres pg_restore --exit-on-error -d mydb_restored mydb.dump

--exit-on-error prevents pg_restore from continuing after a failed SQL command. The custom format is useful when you want one file per database and the option to restore selectively later.

Directory Format with Parallel Workers #

For larger databases, the directory format can be written and restored in parallel. Instead of one file, it creates a directory containing a table of contents and separate data files.

Dump in directory format with four parallel workers:

sudo -u postgres pg_dump -Fd -j 4 -f mydb_backup mydb

-Fd selects the directory format and -j 4 uses four workers. A parallel dump opens one leader connection plus one connection for each worker, so this command uses five database connections.

Restore the same backup in parallel:

sudo -u postgres createdb -T template0 mydb_restored
sudo -u postgres pg_restore --exit-on-error -d mydb_restored -j 4 mydb_backup

Parallel jobs increase load on the database server and storage. Start with a worker count near the number of available CPU cores, then measure the effect on your system.

Remote Backups #

pg_dump does not need to run on the database host. You can back up a remote database over the network as long as the connecting role has access:

pg_dump -h db.example.com -p 5432 -U backup_user -Fc -f mydb.dump mydb

You will be prompted for the password unless one is available from a PostgreSQL password file or another libpq authentication source. For automation, create ~/.pgpass and restrict its permissions:

touch ~/.pgpass
chmod 600 ~/.pgpass

Add the connection entry to the file:

db.example.com:5432:mydb:backup_user:replace_with_password

A dedicated backup role with only the required read access is safer than reusing an application role. On supported PostgreSQL versions, membership in the predefined pg_read_all_data role can provide read access to tables, views, and sequences, but row-level security policies may require additional planning.

Dumping Specific Tables or Schemas #

You do not always need a full database. pg_dump accepts patterns to include or exclude specific objects.

Dump only the orders table:

sudo -u postgres pg_dump -Fc -t orders -f orders.dump mydb

Dump every table in the public schema:

sudo -u postgres pg_dump -Fc -n public -f public.dump mydb

Exclude a table that you do not need to back up, such as a large cache table:

sudo -u postgres pg_dump -Fc -T sessions -f mydb.dump mydb

The same flags accept psql-style patterns when quoted, which lets you match a group of tables (-t 'logs_*'). Partial dumps do not automatically include every object on which the selected table or schema depends, so test them against a clean database before relying on them.

Schema-Only and Data-Only Dumps #

Sometimes you want just the structure of a database, or just the data without the DDL.

Create a schema-only backup:

sudo -u postgres pg_dump --schema-only -Fc -f schema.dump mydb

Create a data-only backup:

sudo -u postgres pg_dump --data-only -Fc -f data.dump mydb

Data-only restores can fail when the existing schema has triggers or foreign-key constraints that reject the incoming rows. The safest workflow is to restore the schema and data into an empty database. pg_restore --disable-triggers is available for data-only restores, but it requires superuser privileges and must be used carefully.

Cluster-Wide Backups with pg_dumpall #

pg_dump backs up one database at a time. For roles, tablespaces, and other global objects, use pg_dumpall.

Create a cluster-wide plain SQL backup:

sudo -u postgres pg_dumpall > cluster.sql

Restore it into an empty cluster:

sudo -u postgres psql -X --set ON_ERROR_STOP=on -f cluster.sql postgres

pg_dumpall takes a separate snapshot of each database, so changes spanning multiple databases are not captured at one synchronized point in time. A common pattern is to combine pg_dumpall --globals-only for roles and tablespaces with per-database pg_dump runs. This provides one file with global objects and one archive per database that you can restore selectively.

sudo -u postgres pg_dumpall --globals-only > globals.sql
for db in mydb analytics reports; do
  sudo -u postgres pg_dump -Fc -f "$db.dump" "$db"
done

pg_restore Syntax #

The general syntax for pg_restore is:

pg_restore [OPTIONS] [FILE]

Common options:

• -d, --dbname - Target database name
• -j, --jobs - Parallel workers (custom or directory format)
• -c, --clean - Drop existing objects before recreating them
• -C, --create - Create the database named in the archive
• -e, --exit-on-error - Stop after the first SQL error
• -t, --table - Restore only named tables
• -n, --schema - Restore only named schemas
• --data-only - Restore data and skip DDL
• --schema-only - Restore DDL and skip data
• --list - Print the archive table of contents

Restoring a Database #

The standard restore creates an empty target database and replays the archive into it:

sudo -u postgres createdb -T template0 mydb_restored
sudo -u postgres pg_restore --exit-on-error -d mydb_restored mydb.dump

For a large dump, parallelize the restore:

sudo -u postgres pg_restore --exit-on-error -d mydb_restored -j 4 mydb.dump

To create the database recorded in the archive, connect to an existing maintenance database such as postgres and pass -C:

sudo -u postgres pg_restore --exit-on-error -C -d postgres mydb.dump

The database passed with -d is used only for the initial CREATE DATABASE command. The remaining objects are restored into the database name stored in the archive. Parallel jobs can be combined with -C; the incompatible option is --single-transaction.

Selective Restore #

One reason to use the custom or directory format is selective restore. Start by writing the archive table of contents to a text file:

sudo -u postgres pg_restore --list mydb.dump > toc.txt

Edit toc.txt, remove unwanted lines or comment them out with a semicolon, then pass the list back to pg_restore:

sudo -u postgres pg_restore --exit-on-error -L toc.txt -d mydb_restored mydb.dump

This is the most controlled way to restore a table or a few schemas from a full database dump. A selected object can still depend on types, functions, extensions, or other objects that are not in the edited list.

Compression and Storage #

Custom and directory archives use compression by default. You can adjust the compression level with -Z:

sudo -u postgres pg_dump -Fc -Z 9 -f mydb.dump mydb

Higher compression can reduce file size at the cost of additional CPU time. For a plain SQL dump, pipe the output through a compressor:

sudo -u postgres pg_dump mydb | gzip > mydb.sql.gz

For automated backups, write to a dedicated directory with restrictive permissions, add rotation, and copy the result off-host. Create the directory and edit the postgres user’s crontab :

sudo install -d -m 700 -o postgres -g postgres /var/backups/postgresql
sudo -u postgres crontab -e

Add the following daily backup entry:

0 2 * * * /usr/bin/pg_dump -Fc -f /var/backups/postgresql/mydb-$(date +\%F).dump mydb

The percent sign is escaped because an unescaped % has a special meaning in crontab commands. Use a separate rotation policy and keep another copy outside the database host.

Verifying a Backup with a Test Restore #

Listing an archive confirms that pg_restore can read its table of contents, but only a test restore proves that the objects and data can be loaded. Create a temporary database, restore the archive, and update the optimizer statistics:

sudo -u postgres createdb -T template0 mydb_verify
sudo -u postgres pg_restore --exit-on-error -d mydb_verify mydb.dump
sudo -u postgres psql -X -d mydb_verify -c "ANALYZE;"

Check row counts, critical queries, and required extensions. After verification, remove the temporary database:

sudo -u postgres dropdb mydb_verify

Schedule restore tests regularly and record how long they take. Recovery time matters as much as whether the dump command completed.

Troubleshooting #

pg_dump: error: connection to server failed
The host, port, socket path, or authentication settings are wrong, or PostgreSQL is not listening on the requested address. Check the service with sudo systemctl status postgresql, confirm listen_addresses, and review the matching rule in pg_hba.conf.

pg_dump: error: permission denied for table ...
The connecting role cannot read one or more selected objects. Grant the required USAGE and SELECT privileges, or grant the trusted backup role membership in pg_read_all_data. Databases that use row-level security need additional review because that predefined role does not bypass RLS policies.

pg_restore: error: could not execute query ... role does not exist
The dump references roles that do not exist on the target cluster. Restore the globals first, recreate the missing roles, or use --no-owner --no-privileges when preserving original ownership and grants is not required.

pg_restore: error: relation ... already exists
The target database already contains objects from the dump. Restoring into a fresh database is the safest solution. If replacing the existing objects is intentional, use --clean --if-exists after verifying the target.

Restore is very slow on a large dump
Use a custom or directory archive with -j parallel workers. Increase the job count gradually while watching CPU, disk throughput, available connections, and memory use. Too many jobs can make the restore slower.

Restored database is missing extensions
The extension software must be installed on the target server before PostgreSQL can run the CREATE EXTENSION statements from the dump. Install the matching extension package, recreate the empty target database, and run the restore again.

FAQ #

Is pg_dump safe to run on a live database?
Yes. pg_dump creates a consistent export while normal reads and writes continue. It holds ACCESS SHARE locks on dumped tables, which can conflict with schema changes that need exclusive locks. Large dumps also create load and can keep old row versions available longer, so monitor them and schedule demanding jobs appropriately.

Should I use pg_dump or pg_basebackup for backups?
Use pg_dump for logical backups of individual databases and for cross-version migrations. Use pg_basebackup as part of a physical backup, replication, or point-in-time recovery plan for the whole cluster. Production systems commonly need both logical exports and physical recovery coverage.

Can I restore a dump from a newer PostgreSQL version into an older one?
Do not rely on restoring into an older major version. PostgreSQL expects pg_dump output to load into newer server versions, but backward compatibility is not guaranteed. The pg_dump client must be the same major version as the source server or newer, and it cannot dump a server newer than itself. For an upgrade, use the target version’s client tools when possible.

How do I back up only the database structure, not the data?
Use pg_dump --schema-only. This is useful for reviewing schema changes and creating empty databases for migration tests.

Where does pg_dump store the dump?
It writes to the path passed with -f, or to standard output when no file is specified. There is no default backup directory. For automated jobs, create a directory owned by the backup account and restrict access to it.

Conclusion #

Use a custom-format pg_dump with pg_dumpall --globals-only when you need portable logical backups, then store the files off-host and prove them with scheduled restore tests. For setup and access control, continue with the PostgreSQL on Ubuntu 26.04 guide and PostgreSQL user management .

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page