pg_dump and pg_restore: a practical backup guide

Two commands cover almost every case. pg_dump --format=custom --file=app.dump app writes one compressed, consistent archive of one database while it keeps serving traffic. pg_restore --dbname=app_new --jobs=4 app.dump puts it back, in parallel, into an empty database. Everything else in this guide is the detail around those two lines: what the archive does not contain, which version of the tools to use, how to restore one table without touching the rest, and why the first thing to do after any restore is ANALYZE.

The reason to learn this rather than lean entirely on your host's backup button is portability. A logical dump is a description of your data that any PostgreSQL of the same or a newer major version can read, on any machine, forever. It is the backup that survives changing hosts, changing versions and deleting the server by accident.

What a dump is, and what it is not#

pg_dump connects as an ordinary client and reads your data out through SQL. To get a consistent picture it opens a single transaction at the REPEATABLE READ isolation level and takes its snapshot there, so every table in the archive reflects the same instant even if the dump takes an hour. It does not block readers or writers.

It does take an ACCESS SHARE lock on every table it reads. That is the weakest lock there is and it conflicts with exactly one thing: ACCESS EXCLUSIVE, which is what DROP TABLE, most forms of ALTER TABLE, TRUNCATE and VACUUM FULL take. So a dump and a migration running at the same time will deadlock each other's queue, and because lock requests are ordered, the migration waiting behind the dump blocks every query that arrives after it. Use --lock-wait-timeout=10s so the dump gives up rather than joining a pile-up, and do not schedule dumps and migrations in the same window. Migrations without downtime has the other half of that problem.

The second cost of a long dump is the held snapshot. While it runs, vacuum cannot remove any row version newer than it, so a dump that takes four hours on a busy table leaves four hours of dead rows behind it. That is normal and it cleans up afterwards, but it is why a dump of a large, heavily updated database is best taken when the database is quiet. Postgres vacuum and bloat explains what those dead rows cost.

What a dump is not is a point-in-time recovery system. It captures one instant. If you need "restore to 14:32, just before the bad UPDATE", that is pg_basebackup plus archived write-ahead logs, which is a different and much heavier setup. For most applications, a nightly dump and an hour of possible loss is the right trade, and pretending otherwise is how people end up with neither.

The four output formats#

Use custom format unless you have a reason not to. It is a single file, it is compressed, it can be restored selectively, and pg_restore can rebuild it with several workers.

Use directory format when the database is large enough that the dump itself is the bottleneck, because --jobs only works for dumping in that format. It produces a folder with one file per table plus a table of contents, which also means you can see which table is enormous just by listing the folder.

Use plain format when a human needs to read or edit the SQL, or when you are moving a small database somewhere that only has psql. It is not selective and not compressed, so a plain dump of anything sizeable is a bad default. Tar format exists for historical reasons and offers nothing the other two do not.

On PostgreSQL 16 and later, --compress accepts a method as well as a level, so --compress=zstd:3 on a directory dump is meaningfully faster than gzip at a similar size. On earlier versions -Z 0 to -Z 9 selects a zlib level, and the default for archive formats is already a moderate one.

Taking a dump#

$ export PGHOST=db.example.net PGPORT=5432 PGUSER=appuser$ pg_dump --format=custom --no-owner --no-privileges \      --file=app-$(date +%F).dump app

Connection settings come from flags (-h, -p, -U, -d) or the PG* environment variables. Never put the password on the command line, where it lands in shell history and in ps output. Use ~/.pgpass instead, one line per server, mode 0600:

db.example.net:5432:*:appuser:the-generated-password

The flags worth knowing:

• --no-owner (-O) - leaves out the ALTER ... OWNER TO statements. Essential when the role names differ on the target, and harmless when they do not.
• --no-privileges (-x) - leaves out GRANT and REVOKE. Use it when you are restoring into a database whose permissions you manage separately.
• --schema-only and --data-only - one without the other. A schema-only dump is a useful thing to keep in version control.
• -t, -T, -n, -N - include or exclude tables and schemas by name or pattern. Note that -t orders does not pull in the tables orders references, so a dump built this way usually will not restore into an empty database on its own.
• --exclude-table-data='audit_log*' - keeps the table definition, drops the rows. This is how you get a 200 MB dump out of a 40 GB database for a staging copy.
• --jobs=4 (-j) - directory format only, one connection per worker. Do not set it higher than the number of cores you are willing to give the database.
• --verbose - prints each object as it goes, which turns a silent hour into something you can watch.

For the cluster as a whole there is pg_dumpall, which iterates over every database. Its plain-SQL-only output makes it a poor choice for large data, but it is the only way to get the things that live outside a database:

$ pg_dumpall --globals-only --file=globals.sql

Roles, globals and what a dump leaves out#

This is where restores fail, so read the list. pg_dump of a single database does not include:

• Roles and their passwords. Those are cluster-wide. pg_dumpall --globals-only captures them, password hashes included, and you restore it with psql -f globals.sql before the database dump. See PostgreSQL roles and permissions for what those roles should look like in the first place.
• Tablespaces. Also cluster-wide, also in the globals file. The directories they point at have to exist on the target first.
• The `CREATE DATABASE` statement, unless you pass -C. Normally you create the empty database yourself with the right owner, encoding and collation, then restore into it.
• Server configuration. postgresql.conf and pg_hba.conf are files on disk, not database objects. Copy them separately.
• Extension code. The dump contains CREATE EXTENSION postgis;, not PostGIS. If the package is not installed on the target machine, the restore stops there.
• Planner statistics. Every restored table arrives with no statistics at all, which is why a freshly restored database can be dramatically slower than the one it came from until you run ANALYZE. Some very recent versions can carry statistics across; running ANALYZE afterwards costs little and removes the doubt. Reading a Postgres query plan explains what those statistics are doing.

Large objects are included by default when you dump a whole database, and excluded when you restrict the dump with -t or -n unless you also pass -b. Sequence values are included, as setval calls, so identity columns continue where they left off rather than colliding on the first insert.

Restoring, and the flags that matter#

$ createdb -T template0 app_restore$ pg_restore --dbname=app_restore --jobs=4 --no-owner \      --verbose app-2026-09-21.dump

-T template0 matters more than it looks: it gives you a database with nothing in it, so objects the dump creates cannot collide with objects inherited from template1.

• --clean --if-exists - drops each object before recreating it. Use both together or --clean will fail noisily on the first object that is not there.
• --single-transaction (-1) - all or nothing. If anything fails, the database is left exactly as it was. It cannot be combined with --jobs, so you are choosing between speed and atomicity.
• --jobs=N - restores table data and builds indexes concurrently. On a restore of any size this is the difference between twenty minutes and two hours. Custom and directory formats only.
• --no-owner --role=appuser - restores everything as one role regardless of who owned it originally.
• --section=pre-data|data|post-data - schema, rows, then indexes, constraints and triggers. Useful when you want to load data and defer the index build.
• -t, -n, -L - selective restore, covered below.

A plain-format dump is not restored with pg_restore at all. It is SQL, so:

$ psql --dbname=app_restore --set ON_ERROR_STOP=1 --file=app.sql

Without ON_ERROR_STOP=1, psql prints errors, carries on, and finishes with an exit status of zero and a half-built database. That default has ruined more restores than any other single thing.

To restore one table out of an archive, list the contents first, keep the lines you want, and feed the list back:

$ pg_restore --list app.dump > toc.list$ grep -E 'TABLE DATA public (orders|order_items)' toc.list > wanted.list$ pg_restore --dbname=app_restore --data-only --use-list=wanted.list app.dump

That is more reliable than -t orders, because the list file lets you keep the sequence, the indexes and the constraints that belong to the table as well.

Version rules when moving between servers#

Three rules cover it:

1. Dump with the newest `pg_dump` you have. pg_dump from version 17 can dump a version 13 server perfectly well. The reverse is refused: an old pg_dump against a newer server aborts with a version mismatch, because it cannot know what the newer catalogue contains.
2. Restore into the same major version or a newer one. Going backwards, from 17 to 16, is not supported. It sometimes appears to work and then fails on some syntax the older server has never heard of.
3. `pg_restore` must be at least as new as the `pg_dump` that wrote the archive. An older pg_restore rejects a newer archive with unsupported version ... in file header.

The practical version of all three: when upgrading or migrating, run the new server's pg_dump and pg_restore binaries against the old server. Minor versions (17.2 to 17.6) never matter here.

Making a large restore finish faster#

A restore is a very long series of inserts followed by a very long series of index builds, and the default configuration is tuned for a server answering queries, not for that. On the target, for the duration only:

ALTER SYSTEM SET maintenance_work_mem = '1GB';ALTER SYSTEM SET max_wal_size = '8GB';ALTER SYSTEM SET synchronous_commit = 'off';SELECT pg_reload_conf();

maintenance_work_mem is what index builds and sorts use, and it is the single biggest lever. max_wal_size stops the restore triggering a checkpoint every few seconds. synchronous_commit = off means a crash mid-restore could lose recent commits, which does not matter because you would start the restore again anyway. Put them back afterwards, and run ANALYZE on the whole database:

$ vacuumdb --analyze-only --jobs=4 --dbname=app_restore

Size the target realistically too. A restore needs room for the data, the indexes, and the write-ahead log generated while building them, which can mean two to three times the dump's uncompressed size. On a 20 GB plan that is the constraint you will hit first, and a database server that fills its disk stops accepting writes.

Scheduling dumps and proving one restores#

A dump nobody has restored is a hypothesis. The routine that turns it into a backup:

1. Dump nightly, at an hour when the application is quiet, with the date in the filename.
2. Copy it off the machine it came from. A dump sitting on the same disk as the database protects you from DROP TABLE and from nothing else.
3. Keep seven daily, four weekly, and whatever monthly retention your obligations require. Prune automatically, because manual pruning ends with a full disk.
4. Once a quarter, restore the most recent one into a scratch database, count rows in three tables you care about, point a copy of the application at it, and drop it.

On RE:NODE the Schedules tab takes a cron expression and runs ordered tasks with delays between them - a backup, a power action, a console command - so the nightly half of that is configuration rather than a script you have to keep alive. Backup slots come with every database plan, backups are taken on demand or on a schedule, stored off the machine they protect, restorable with a button, downloadable, and lockable so rotation cannot delete the one you care about. The two things worth remembering: deleting a server deletes its backups, locked ones included, and a downloaded pg_dump archive is the only copy that is portable to another host. Keep both. Backups that actually restore and testing a restore before you need it make the case at more length, and the panel guides show where the buttons are.

Errors you will actually see#

`pg_dump: error: aborting because of server version mismatch` - your pg_dump is older than the server. Install matching client tools; do not force it.

`pg_restore: error: input file appears to be a text format dump. Please use psql.` - you made a plain dump and reached for the wrong tool. psql -f it.

`role "someone" does not exist` - you restored a dump containing ownership or grants into a cluster without those roles. Restore the globals first, or re-dump with --no-owner --no-privileges.

`permission denied for schema public` - from PostgreSQL 15 onward, the public schema no longer lets every role create objects in it. Grant it explicitly to the restoring role, or make that role the database owner.

`ERROR: relation "orders" already exists` - restoring over a database that is not empty. Use a fresh database, or --clean --if-exists.

`could not execute query: ERROR: extension "pg_trgm" is not available` - the extension's files are not installed on the target machine. Install the package, then restart the restore.

A restore that succeeds and leaves the application broken - almost always missing sequences or missing grants, from a -t dump that took the table and nothing attached to it.

FAQ#

Does pg_dump lock my tables or take the database offline?

No. It takes an ACCESS SHARE lock on each table, which readers and writers ignore. Only statements needing an ACCESS EXCLUSIVE lock, such as ALTER TABLE or TRUNCATE, have to wait. The database serves traffic normally throughout.

Can I restore a dump into a different PostgreSQL version?

Into the same major version or a newer one, yes, and that is the standard way to upgrade. Into an older major version, no. Use the newer server's pg_dump to read the older one when you are migrating forward.

What is the difference between pg_dump and pg_dumpall?

pg_dump handles one database and can write compressed, selectively restorable archives. pg_dumpall walks every database in the cluster and can only write plain SQL. In practice you use pg_dumpall --globals-only for roles and tablespaces, and pg_dump for each database.

How long should a dump take?

Roughly as long as reading the whole database off disk, plus compression. A few gigabytes on NVMe is a minute or two. If it is taking far longer, the usual causes are a contended CPU, plain format piped through a slow compressor, or a table full of large objects.

Should I use pg_dump or my host's backup button?

Both. The platform backup is fast to take and fast to restore in place, and it is gone if the server is deleted. A pg_dump archive you have downloaded is slower but portable, version-tolerant and yours. They fail in different ways, which is the whole argument for having two.

My restored database is much slower than the original. Why?

Because statistics are not part of the dump, so the planner is guessing about every table until you run ANALYZE. Run vacuumdb --analyze-only across the database as the last step of every restore and the difference usually disappears.