How to back up a Postgres database: pg_dump, pg_restore and a test restore

How to back up a Postgres database in one command

To back up a Postgres database, run pg_dump against it in custom format:

pg_dump -Fc mydb > db.dump

That is the example in the official PostgreSQL documentation. The dump is a consistent snapshot from the moment the command started, and it does not block ordinary reads and writes. The custom format is compressed by default and must be restored with pg_restore. A plain pg_dump mydb > db.sql writes an SQL script instead, which you restore with psql. Postgres dump formats compares the four formats.

Prerequisites: versions, roles and what pg_dump leaves out

• Client version. pg_dump can dump from older servers but refuses a server newer than its own major version.
• Read access. It needs read access to every table, so a full backup almost always runs as a database superuser.
• Connection flags. -h host, -p port and -U user, or the PGHOST, PGPORT and PGUSER environment variables.
• Roles and tablespaces are not included. They are cluster-wide. Save them with pg_dumpall --globals-only > globals.sql.
• No plan or license is needed. These tools ship with PostgreSQL.

Postgres backup and restore, step by step

1. Save the globals. pg_dumpall --globals-only > globals.sql
2. Dump the database. pg_dump -Fc mydb > db.dump. For a large database, use the directory format with parallel workers: pg_dump -Fd mydb -j 5 -f dumpdir. Parallel dumps only work with the directory format, and pg_dump opens one more connection than the job count.
3. Copy both files off the server. A backup on the same disk as the database is not a backup. See Postgres backup to S3.
4. Restore the globals first. On the target, run psql -X -f globals.sql postgres. Roles that own objects must exist before the dump is restored, or ownership and permissions will not be recreated.
5. Create an empty database. createdb -T template0 newdb. The manual says to use template0 so the target is truly empty.
6. Restore. pg_restore -d newdb db.dump. Add -j with a job count to restore in parallel from a custom or directory archive.
7. Refresh statistics. Run ANALYZE on the restored database so the query planner has useful statistics.

To recreate the database under its original name instead, the documented form is pg_restore -C -d postgres db.dump, which needs the old database removed first with dropdb mydb. Warning: dropdb permanently deletes the existing database and all the data in it. Only run it after the same dump file has restored cleanly into a scratch database, as in the test restore below.

pg_restore flags worth knowing

For a plain SQL dump, the equivalent safety switch is psql -X --set ON_ERROR_STOP=on newdb < db.sql.

Worked example: a weekly test restore

A team backs up a database called shop nightly. Every Monday a second job proves the file works:

pg_restore -l db.dump confirms the archive is readable and lists its contents.

createdb -T template0 shop_restore_test

pg_restore -e -d shop_restore_test db.dump

With -e the job fails loudly on the first error, which is what you want in a test. They then compare row counts for a few key tables against production and drop the test database. Do this on a separate machine where you can; an isolated restore target explains why.

A mistake to avoid, and when to stop scripting

An easy mistake is dumping the database and forgetting the roles. The restore then throws ownership errors on a new server because the roles do not exist. The second is assuming a dump gives point-in-time recovery; it restores only to the moment it was taken.

For one or two databases, these native commands and cron are enough. If you have many databases, or nobody checks whether last night's job ran, a managed service such as SimpleBackups takes over scheduling and alerts. Its Basic plan is $0 for one backup job, as read on its pricing page on 3 October 2026. Either way, keep a written restore runbook.