pg_dump creates a consistent logical backup of one PostgreSQL database while it stays online. pg_restore loads it back — selectively if you like. Together they’re the everyday backup tools for databases up to tens of gigabytes.

All examples run as the postgres system user (sudo -u postgres …) or with -h 127.0.0.1 -U <role> and a password.

Choose a format

Format Flag Restore with Notes
Plain SQL -Fp (default) psql Human-readable; restore all-or-nothing
Custom -Fc pg_restore Compressed; selective and parallel restore
Directory -Fd pg_restore One file per table; parallel dump with -j
Tar -Ft pg_restore Rarely needed

For backups, custom format is the best default.

Back up a database

sudo -u postgres pg_dump -Fc -d shop -f /var/backups/pg/shop-$(date +%F).dump

Plain SQL (e.g. to read or edit it):

sudo -u postgres pg_dump -d shop | gzip > shop-$(date +%F).sql.gz

Large database, many cores — parallel directory dump:

sudo -u postgres pg_dump -Fd -j 4 -d shop -f /var/backups/pg/shop-$(date +%F)

pg_dump takes a snapshot at the start, so the backup is consistent even while the application keeps writing.

Useful options

pg_dump -Fc -d shop -t orders -t customers -f part.dump   # only these tables
pg_dump -Fc -d shop -T audit_log -f shop.dump             # exclude a table
pg_dump -Fc -d shop --exclude-table-data='sessions' -f shop.dump   # keep schema, skip data
pg_dump -d shop --schema-only -f schema.sql
pg_dump -Fc -d shop -n reporting -f reporting.dump        # one schema

Roles and global objects: pg_dumpall

pg_dump doesn’t include roles (users) or tablespaces — they’re cluster-wide. Back them up separately:

sudo -u postgres pg_dumpall --globals-only > /var/backups/pg/globals-$(date +%F).sql

pg_dumpall without options dumps the entire cluster as plain SQL — convenient for small servers, but you lose the selective/parallel restore of custom format.

Restore

Into a new, empty database

sudo -u postgres createdb -O shop_app shop_restored
sudo -u postgres pg_restore -d shop_restored --no-owner --role=shop_app -j 4 /var/backups/pg/shop-2026-11-04.dump
  • -j 4 restores in parallel (custom and directory formats).
  • --no-owner --role=shop_app makes the target role own everything — useful when role names differ on the new server.

Replace an existing database

sudo -u postgres pg_restore -d shop --clean --if-exists -j 4 shop.dump

--clean drops objects before recreating them. Safer: restore into a new database, verify, then swap names during a short maintenance window.

Plain SQL dumps

gunzip -c shop.sql.gz | sudo -u postgres psql -d shop_restored

Add -v ON_ERROR_STOP=1 to stop at the first error instead of continuing past it.

Restore globals first

On a new server, recreate roles before restoring databases:

sudo -u postgres psql -f globals.sql

Pick pieces from a dump

pg_restore -l shop.dump > toc.txt          # list contents
# edit toc.txt: comment out lines with ';'
pg_restore -L toc.txt -d shop_restored shop.dump
pg_restore -d shop_restored -t orders shop.dump   # a single table

Version compatibility

Use a pg_dump at least as new as the server you’re dumping; pg_restore can read dumps from older versions. Dumping with a newer pg_dump and restoring into a newer server is also the standard way to upgrade major versions — see upgrading PostgreSQL major versions.

Automate nightly backups

/usr/local/bin/pg-backup.sh:

#!/bin/bash
set -euo pipefail
DEST=/var/backups/pg
mkdir -p "$DEST"
pg_dumpall --globals-only > "$DEST/globals-$(date +%F).sql"
for db in $(psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
  pg_dump -Fc -d "$db" -f "$DEST/$db-$(date +%F).dump"
done
find "$DEST" -type f -mtime +14 -delete
sudo chown postgres: /usr/local/bin/pg-backup.sh && sudo chmod 700 /usr/local/bin/pg-backup.sh
sudo mkdir -p /var/backups/pg && sudo chown postgres: /var/backups/pg
echo '15 2 * * * postgres /usr/local/bin/pg-backup.sh' | sudo tee /etc/cron.d/pg-backup

Then copy /var/backups/pg off the server — with restic, Borg or rsync. See automated VPS backups.

Test restores regularly

sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test /var/backups/pg/shop-$(date +%F).dump
sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM orders;"
sudo -u postgres dropdb restore_test

When pg_dump isn’t enough

  • Point-in-time recovery (restore to 14:32 yesterday) and very large databases need physical backups with WAL archiving: pgBackRest, Barman or WAL-G. See PostgreSQL backups with pgBackRest.
  • Logical dumps are also slower to restore than physical backups for big data sets.

Troubleshooting

  • server version mismatch — your pg_dump is older than the server; install the matching client version.
  • permission denied for table … — the dumping role can’t read everything; run as postgres or grant pg_read_all_data.
  • role "x" does not exist during restore — restore globals first, or use --no-owner --no-privileges.

Related: PostgreSQL users and databases, remote connections. Keep dumps on a second machine — a Storage VPS works well as a backup target.

  • PostgreSQL
  • Backups
  • Databases