A backup you have never restored is not a backup — it is a hope. In Houston that hope gets tested by energy trading tables that change every few minutes and dispatch databases where a day of lost orders is a day of lost revenue. The logical backup path is a handful of commands, and you can prove it works in ten minutes.

This tutorial covers pg_dump and pg_restore end to end: choosing a format, restoring into a clean database, verifying the restore, restoring selectively, and the three errors that show up most often. Every command and number below comes from a real run against PostgreSQL 17.10; the flags are unchanged in PostgreSQL 18.

Step 1: Pick the dump format before you type the command

Four dump formats exist; three matter.

# 1. Plain SQL text - readable, easy to grep, restore with psql
pg_dump -U postgres shop > shop.sql

# 2. Custom format - compressed archive, selective restore
pg_dump -U postgres -Fc -f shop.dump shop

# 3. Directory format - the only format that can dump in parallel
pg_dump -U postgres -Fd -j 4 -f shop.dir shop

On a demo database with 500 customers, 5,000 orders, one view and four indexes, the three commands produced 289 KB (plain), 44 KB (custom) and 44 KB of directory files. Compression is not a rounding error once a table has real payload columns in it.

Step 2: Restore into a clean database, then prove it worked

Create the target from template0, not template1: a dump is relative to template0, so anything added to template1 can collide on restore.

createdb -U postgres -T template0 shop_restore
pg_restore -U postgres -d shop_restore -j 4 shop.dump

# verify
psql -U postgres -d shop_restore -c "
  SELECT (SELECT count(*) FROM customers)          AS customers,
         (SELECT count(*) FROM orders)             AS orders,
         (SELECT sum(total_cents) FROM orders)     AS cents"

It returned 500 | 5000 | 226732500 on the restored copy — identical to the source. A row count and a sum is the cheapest integrity check there is, and it catches the failure mode that matters: a restore that “succeeded” with missing rows.

Note: -j works only with custom and directory archives, and never with --single-transaction.

Step 3: Restore one table, not the whole database

Before restoring selectively, look inside the archive. The custom format keeps a table of contents you can print, edit, reorder, or comment out:

pg_restore -l shop.dump > shop.list

# ...then pull out a single table into a scratch database
createdb -U postgres -T template0 orders_only
pg_restore -U postgres -d orders_only -t orders shop.dump

The listing shows every object with an internal ID, table, and owner: two tables, a sequence each, and the open_orders view in the demo archive. The last command restored just the orders table — 5,000 rows — into an empty database. Use -n for a schema, -t for one table, and --section=pre-data for schema without data.

Step 4: Parallel restore is the single biggest win

The -j flag is not marketing. On a benchmark of 12 tables at 250,000 rows each (306 MB on disk), on a 12-core host:

The trade-off is in the table. A plain dump is fast to write and slow to restore; a single-threaded custom dump writes slowly because one process does all the compression, but stores three times less data. Directory format with -j gets both — four workers compress in parallel, four load in parallel — cutting restore wall clock by roughly 3x here. All four restores produced identical checksums.

The caveat: -j parallelizes across tables and index builds. If 90% of your database is one giant events table, do not expect 4x — you are waiting on one COPY stream. Match -j to CPU cores, then test.

Step 5: Roles and tablespaces live outside the database

A database dump contains the database, not the cluster-level objects: roles, role passwords, and tablespaces. Restore onto a fresh server without those and you get a pile of ownership errors — or objects quietly reassigned to the wrong owner.

Dump the globals once per cluster with pg_dumpall -U postgres --globals-only > cluster_globals.sql, alongside your per-database dumps. Without flags, pg_dumpall dumps the whole cluster in one file, at the cost of parallel dump and selective restore.

Step 6: The three errors you will actually hit

1. ERROR: relation "t1" already exists — you restored a plain dump into a database that already has objects. Restore into a fresh database (createdb -T template0), or use pg_restore --clean --if-exists, which drops and recreates what it is about to restore. That path returned exit code 0 with a complete table in testing; the other returned a stream of “already exists” errors.

2. A restore that reports success but is missing data. By default both psql and pg_restore keep going after an error. In scripts, always fail loudly:

psql -X --set ON_ERROR_STOP=on -f shop.sql          # exits 3 on the first SQL error
pg_restore -e -d shop_restore shop.dump             # --exit-on-error

3. Tables present, zero rows. You restored a schema-only dump. Check the archive before you blame the server: if pg_restore -l shows no TABLE DATA entries, the dump was taken with -s.

When pg_dump is the wrong tool

Logical dumps are consistent as of the moment they start, do not block writers, and restore cleanly into a newer major version or different architecture. What they cannot do is point-in-time recovery: a dump has no WAL in it, so you cannot replay it to 09:41:07 to undo a bad UPDATE.

If your recovery point objective is measured in seconds, not hours — trading positions, clinical records — you need a physical base backup plus continuous WAL archiving: pg_basebackup -U postgres -D /var/lib/postgresql/17/main_copy -X fetch -c fast -P. That copies the whole cluster, including the WAL needed to open it, and it is the foundation for point-in-time recovery. Plan on both: logical dumps for portability and object-level recovery, physical backups and WAL archiving for the recovery window.

The five-minute checklist

• Default to -Fc; move to -Fd -j N when the database outgrows a single-threaded dump.
• Dump cluster globals (pg_dumpall --globals-only) on the same schedule as the databases.
• Restore into a throwaway database on a schedule, and compare row counts and sums — not just exit codes.
• Use ON_ERROR_STOP=on or pg_restore -e in every scripted restore.
• Keep dumps on a different host than the server, and re-run the restore drill after every major-version upgrade.

Sources: PostgreSQL documentation on SQL dumps, pg_dump, pg_restore, pg_dumpall, pg_basebackup and continuous archiving and PITR.