pg_restore: Usage, Options & Common CI Errors
pg_restore reconstructs a Postgres database from a custom, directory, or tar archive.
pg_restore is the other half of pg_dump for archive formats. The number-one mistake is pointing it at a plain-SQL dump, which it cannot read - that is psql's job.
What it does
pg_restore reads an archive produced by pg_dump in custom (-Fc), directory (-Fd), or tar (-Ft) format and recreates the database objects and data, optionally in parallel with -j.
Common usage
PGPASSWORD=secret pg_restore -h localhost -U postgres -d app app.dump
pg_restore -h localhost -U postgres -C -d postgres app.dump # create db
pg_restore -h localhost -U postgres -d app --clean app.dump
pg_restore -h db -U postgres -d app -j4 app.dump # 4 parallel jobsOptions
| Flag | What it does |
|---|---|
| -d <db> | Target database to restore into |
| -C | Create the database first (connect to another db) |
| --clean | Drop objects before recreating them |
| -j <N> | Restore with N parallel jobs |
| --no-owner | Do not set object ownership |
| -l / -L | List / select archive contents |
Common errors in CI
pg_restore: error: input file does not appear to be a valid archive - you handed it a plain-SQL dump (use psql -f instead) or a truncated/wrong file. "pg_restore: error: could not execute query ... role "x" does not exist" - restore with --no-owner or pre-create the role. Errors during data load do not stop the restore by default; pass --exit-on-error to fail the CI step, and -1 to wrap it in a single transaction.