Back up and restore PostgreSQL with pg_dump, pg_restore and pg_dumpall

A logical backup is a file of SQL commands (or an archive of them) that recreates your tables and data. pg_dump makes one for a single database while it is in use: it takes a consistent snapshot and does not block other readers or writers. It works across major versions and CPU architectures, which also makes it the simplest way to move a database to a new server.

Two limits matter. pg_dump does not include roles or tablespaces, because those belong to the whole cluster; use pg_dumpall for them. And a dump captures one moment only: you cannot restore to a time between two dumps. For that, see physical backups with WAL archiving.

Choose an output format

• Plain (-Fp, the default): an SQL script. Readable, restore with psql.
• Custom (-Fc): one compressed archive file. Restore with pg_restore, which can pick single tables and run in parallel. The usual choice.
• Directory (-Fd): one file per table plus a table of contents. The only format that supports parallel dumps with -j.
• Tar (-Ft): a tar archive, not compressed.

Make a backup

1. Run the dump as a role that can read everything you need, for example postgres.
2. Dump the global objects once per cluster.
3. Dump each database in custom or directory format.
4. Check that the archive is readable, and copy it off the server.

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals.sql
sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/appdb.dump appdb
sudo -u postgres pg_dump -Fd -j 4 -f /var/backups/postgresql/appdb.dir appdb
pg_restore --list /var/backups/postgresql/appdb.dump | head

Useful options: -n schema or -t table limit the dump (quote patterns so the shell does not expand them), -O/--no-owner leaves out ownership commands, and -Z/--compress picks the compression method or level (for example zstd in recent versions). A parallel dump opens jobs + 1 connections, so make sure max_connections allows that.

Restore into a new database

Create the roles first (from the globals file), then an empty database, then restore. Creating from template0 makes sure the database starts truly empty:

sudo -u postgres psql -X -f globals.sql postgres
sudo -u postgres createdb -O appuser -T template0 appdb_restored
sudo -u postgres pg_restore -d appdb_restored -j 4 appdb.dump

-j runs the slowest steps (loading data, creating indexes and constraints) in parallel. It works with the custom and directory formats and only when restoring directly into a database. -1/--single-transaction makes the restore all-or-nothing, but it cannot be combined with -j.

To let pg_restore create the database itself, connect to a different database with -C; the data goes into the database name stored in the archive:

sudo -u postgres pg_restore -C -d postgres appdb.dump

Restore over an existing database

Warning: --clean drops every object in the archive from the target database before recreating it, so data written since the dump is lost. Check which database you are connected to and take a fresh dump of the target first.

sudo -u postgres pg_dump -Fc -f appdb-before-restore.dump appdb
sudo -u postgres pg_restore --clean --if-exists -d appdb appdb.dump

Restore only some objects

List the archive’s table of contents, comment out lines you do not want with a leading ;, and restore using the edited list:

pg_restore -l appdb.dump > appdb.list
# edit appdb.list
pg_restore -L appdb.list -d appdb_restored appdb.dump

Plain-format dumps and pg_dumpall

Plain SQL dumps and full pg_dumpall output are restored with psql. Use -X so your personal ~/.psqlrc does not interfere, and ON_ERROR_STOP if you want the restore to stop at the first error. A full pg_dumpall restore usually reports that the bootstrap superuser role already exists; that error is harmless.

pg_dumpall -U postgres -f cluster.sql
psql -X -U postgres -f cluster.sql postgres

psql -X -v ON_ERROR_STOP=1 -d appdb_restored -f appdb.sql

Treat restores as part of the backup

A backup you have never restored is a guess. Restore regularly into a scratch database, compare row counts of important tables, and time how long the restore takes. Only restore dumps from sources you trust: a dump can contain arbitrary SQL chosen by the source’s superusers.

Official documentation

• pg_dump
• pg_restore
• pg_dumpall