pg_restore: restore a PostgreSQL dump and check it worked

To restore a custom-format dump into an empty database on a server where the original roles do not exist, which covers most restores to a new machine or a managed host:

pg_restore --dbname="$TARGET_URL" \
  --no-owner --no-privileges --exit-on-error \
  app.dump

$TARGET_URL is a connection string such as postgresql://user@host:5432/app for a database that already exists and is empty. Keep the password in ~/.pgpass rather than in the string, so it stays out of shell history and the process list. If the command exits 0, every statement in the archive ran. That still does not prove you have your data back, which is what the last section is about.

Everything on this page was run against PostgreSQL 16 and 17 servers with pg_restore 16.15 and 17.10. The error messages below are copied from those runs, not paraphrased.

Custom, directory, or plain SQL: pg_restore or psql?

pg_restore only reads archives: what pg_dump --format=custom, --format=directory, or --format=tar produce. A plain dump, the default when you run pg_dump with no format and usually named .sql, is a SQL script. Hand one to pg_restore and it refuses with input file appears to be a text format dump. Please use psql. Restore it with psql instead:

psql --dbname="$TARGET_URL" --set ON_ERROR_STOP=1 \
  --single-transaction --file=app.sql

ON_ERROR_STOP is the psql equivalent of --exit-on-error; without it, psql prints each error and carries on. Plain dumps cannot be restored in parallel or selectively, and they are uncompressed, so for backups you plan to restore, custom format is the better default. Not sure which you have? pg_restore --list fileprints the archive’s table of contents, or the text-format error above.

One more direction is useful: pg_restore can turn an archive into a SQL script without touching any database. That is how you read what a dump contains, or edit it before running it.

pg_restore --file=app.sql --no-owner app.dump

Restore from a dump into a new database, or over an existing one

The safe default is a new, empty database. Create it and restore into it:

createdb --maintenance-db="$ADMIN_URL" app_restored
pg_restore --dbname="$TARGET_URL" --no-owner --no-privileges \
  --exit-on-error app.dump

Here $ADMIN_URL points at an existing database on the same server (often postgres) and $TARGET_URL at the new app_restored. Alternatively, --create makes pg_restore do both steps: it connects to the database given in --dbname, issues CREATE DATABASE with the original name, and restores into that. With --create you do not choose the new database’s name, and running it twice fails because the database exists.

Restoring over a database that already has these objects needs --clean --if-exists: drop each object in the archive, then recreate it. Two things to know before you use it on anything that matters. It only drops what is in the archive, so tables created after the dump survive, next to restored tables they may no longer fit. And the drops run immediately, so if the restore fails halfway, the old data is already gone.

--single-transaction fixes the second problem: the whole restore commits or none of it does. In our test, a restore that failed on a missing role left zero tables behind instead of a half-built schema. The price is that it cannot be combined with --jobs (pg_restore refuses: cannot specify both --single-transaction and multiple jobs), and a large restore holds one long transaction open.

Restore one table, one schema, or a custom list

# one table (add --schema for tables outside public)
pg_restore --dbname="$TARGET_URL" --no-owner --table=orders app.dump

# one schema
pg_restore --dbname="$TARGET_URL" --no-owner --schema=billing app.dump

# anything else: edit the table of contents and feed it back
pg_restore --list app.dump > contents.txt
# comment out lines with a leading ; then:
pg_restore --dbname="$TARGET_URL" --no-owner --use-list=contents.txt app.dump

Two behaviours catch people here. First, a --table name that matches nothing is not an error. pg_restore restores nothing and exits 0. The “no matching tables were found” message people search for comes from pg_dump, not pg_restore. Check names against --list first. Second, --table restores the table and its data, not the schema it lives in: --schema=billing --table=invoices into a database without that schema fails with schema "billing" does not exist. Create the schema first. It also leaves out the table’s indexes and constraints, primary key included: in our test, --table=ordersbrought back the rows with no index and no primary key. When you need those, use the list file below and keep the table’s CONSTRAINT and INDEX lines.

The list-file route is the precise one. Each line of contents.txtis one object: a table, its data, an index, a constraint, a grant. Removing an index line gives you the table without that index. Rolling back a single table from last night’s dump into a scratch database, then copying the rows you need across, is the usual way to undo a bad DELETE without touching anything else.

Speed: --jobs and what it cannot do

pg_restore --dbname="$TARGET_URL" --no-owner --no-privileges \
  --jobs=4 app.dump

With --jobs, pg_restore loads tables and builds indexes over several connections at once. Start with the number of CPU cores on the database server. It needs a custom or directory archive read from a file (not a pipe), it cannot be combined with --single-transaction, and each job is a separate connection, so a connection pooler or a low connection limit on a managed plan can become the bottleneck. Directory-format dumps can also be written in parallel with pg_dump --jobs, which is why large databases tend to use them.

pg_restore errors and how to fix them

Without --exit-on-error, most of these do not stop the restore. pg_restore logs them and ends with warning: errors ignored on restore: 5 and exit status 1. Scroll up to the first error. The later ones are often consequences of it.

For the transaction_timeout case, when the matching older pg_restore cannot read the archive (it fails with the unsupported-version error instead), turn the archive into SQL, drop the one line, and run it with psql. This worked restoring a PostgreSQL 17 dump into a 16 server. The filter removes only the first line that is exactly that statement, which pg_restore writes at the top, so table data that happens to contain the same text is left alone:

set -o pipefail   # a pg_restore failure fails the whole pipeline
pg_restore --no-owner --no-privileges --file=- app.dump \
  | awk '!done && $0 == "SET transaction_timeout = 0;" { done = 1; next } { print }' \
  | psql --dbname="$TARGET_URL" --set ON_ERROR_STOP=1 --quiet

The pg_dump documentation is explicit that output is not guaranteed to load into an older major version, so check the result as carefully as the last section describes.

--no-owner and --no-privileges: when they are safe

They are the standard fix for missing roles, and on a single-user app database they are usually fine. What they change: with --no-owner, every object belongs to the user who ran the restore. If your application connects as a different role, it may not be able to read its own tables until you grant access or change owners.

--no-privileges is the one to think about. It skips every GRANT and every REVOKE. PostgreSQL gives EXECUTE on new functions to PUBLIC by default, so a function you had locked down with REVOKE … FROM PUBLIC is callable by every role again after the restore. Row-level security policies are not privileges and do come back. If the database will serve real traffic, recreate the roles first and restore without these two flags, or reapply your grants from a script you keep in version control.

Restoring to Supabase, Neon, or RDS

Managed Postgres does not give you a true superuser, so expect to need --no-owner --no-privileges, and expect extensions the platform does not offer to fail. Per platform:

Supabase. Restore into a new project, never with --cleanagainst one that has the platform’s own schemas, and connect through the Session pooler. The details, including Auth users and Storage files that a database dump does not bring back, are in restoring a Supabase backup.

Neon. Use the direct connection string, the host without -pooler. Neon’s docs say pooled connections are not supported for pg_dump and pg_restore and will cause errors.

AWS RDS and Aurora. The master user has rds_superuser, which is not a superuser, so ownership changes to roles you do not have fail without --no-owner. Connect with sslmode=verify-full and the RDS CA bundle. If what you have is an RDS snapshot rather than a pg_dump file, that is a different restore path, done in the AWS console.

Check that the restore actually worked

Exit status 0 with --exit-on-error means every statement ran. It does not mean the archive had everything you needed in it. Four checks, cheapest first:

1. Every table in the archive exists. Compare the TABLE DATA lines of pg_restore --list with information_schema.tables on the target.
2. Row counts look right. Exact counts per table, not the statistics-based estimates, which are missing or off on a freshly restored database until ANALYZE runs. An empty table that should not be empty is the classic sign of a dump taken with the wrong flags.
3. Spot-check content. The newest rows in your busiest tables, compared with the source.
4. Run the app against it. Your test suite or a smoke test pointed at the restored database catches missing extensions, functions, and grants that the first three do not.

This query gives exact row counts for every table in one pass:

psql --dbname="$TARGET_URL" --no-align --tuples-only --command "
  select table_schema || '.' || table_name,
         (xpath('/row/n/text()', query_to_xml(
            format('select count(*) as n from %I.%I', table_schema, table_name),
            false, true, '')))[1]::text
  from information_schema.tables
  where table_type = 'BASE TABLE'
    and table_schema not in ('pg_catalog', 'information_schema')
  order by 1"

Doing this once, after an incident, tells you whether that restore worked. Doing it on a schedule, against last night’s backup in a throwaway database, tells you whether your backups work at all, before you need one. The pg_dump to S3 guide has a script that does exactly that: it downloads the newest dump, restores it into a temporary container, and runs checks 1 and 2.

FAQ