pg_restore: Restore a pg_dump Archive
pg_restore -d appdb appdb.dump loads a custom-format pg_dump archive into a target database.
pg_restore is the counterpart to pg_dump for the custom and directory formats. It can restore in parallel, selectively, and while cleaning existing objects, which matters when seeding a test database in CI.
What it does
pg_restore reads an archive produced by pg_dump -Fc or -Fd and replays it into a database. It only handles those non-plain formats; a plain SQL dump is loaded with psql instead. Options control parallelism, ownership, and whether to drop existing objects first.
Common usage
# restore into an existing database
PGPASSWORD=secret pg_restore -h db -U app -d appdb appdb.dump
# drop existing objects first, ignore ownership, 4 parallel jobs
pg_restore -h db -U app -d appdb --clean --no-owner -j 4 appdb.dump
# create the database as part of the restore
pg_restore -h db -U postgres --create -d postgres appdb.dumpOptions
| Flag | What it does |
|---|---|
| -d <db> | Target database to restore into |
| --clean | Drop objects before recreating them |
| --create | Create the database, then restore into it |
| --no-owner | Do not set object ownership (avoids missing-role errors) |
| -j <n> | Restore with n parallel workers (custom/directory only) |
| -t <table> | Restore only the named table |
| -1 / --single-transaction | Restore as one transaction |
In CI
Use --no-owner when the dump references roles that do not exist on the CI database, otherwise every object errors on a missing owner. Pair -j with directory or custom format to speed up large seeds. Add --clean when re-restoring into a database that already has objects, so the load is repeatable.
Common errors in CI
"pg_restore: error: input file appears to be a text format dump. Please use psql." means you handed it a -Fp plain dump; restore that with psql -f instead. "role \"app\" does not exist" means the archive sets ownership to a missing role; add --no-owner. "relation already exists" on a re-run means you need --clean.