How to restore a PostgreSQL dump with pg_restore or psql

Find out which format you have

Choosing a format is covered in the pg_dump guide. To tell them apart, run file in the directory holding the dump:

file shop.sql shop.sql.gz shop.dump shop.tar shop_dir

shop.sql: ASCII text
shop.sql.gz: gzip compressed data, from Unix, original size modulo 2^32 2161974
shop.dump: PostgreSQL custom database dump - v1.15-0
shop.tar: POSIX tar archive
shop_dir: directory

v1.15-0 is the archive format, not the PostgreSQL version: pg_dump 16 wrote 1.15, pg_dump 17 and 18 wrote 1.16. The exact versions are in a plain dump's header:

gunzip -c shop.sql.gz | head -n 20 | grep Dumped

-- Dumped from database version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

pg_restore -l shop.dump | head -12 shows the same for an archive. A plain dump made with pg_dump -C has its own CREATE DATABASE and \connect lines, so psql fills that database whatever -d says; point -d at postgres.

Prepare the target

• Restore the roles first: a dump names owners and grantees but does not create them. See backing up roles.
• Create an empty database from template0. Dumps are relative to it, the docs explain, so anything added to template1 would clash.
• Make the right role its owner (-O). Since PostgreSQL 15, other ordinary users cannot create objects in a new database's public schema.
• Install every extension the dump uses. It runs CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; without a version, so the server creates its default version from files that must already be installed.

sudo -u postgres createdb -T template0 -O app_owner shop

Restore a plain SQL dump with psql

sudo -u postgres psql -X -v ON_ERROR_STOP=1 --single-transaction -d shop -f /var/backups/postgresql/shop.sql

• -X skips your ~/.psqlrc, as the pg_dump docs recommend, so personal settings cannot change the restore.
• -v ON_ERROR_STOP=1 stops at the first error and exits with status 3. Without it psql prints the error and carries on.
• --single-transaction (-1) runs the file as one transaction: an error rolls everything back instead of leaving half a database, even when that discards hours of work.
• -d is the database to load into; -f is the file.

Output such as CREATE TABLE, COPY 40000 and small setval results is normal. For a gzipped dump, psql reads the pipe as if given -f -, so --single-transaction still applies:

gzip -t shop.sql.gz && gunzip -c shop.sql.gz | sudo -u postgres psql -X -v ON_ERROR_STOP=1 --single-transaction -d shop

A plain dump fixes ownership when it is made: with the roles missing, psql stops at the first ALTER ... OWNER TO with role "app_owner" does not exist. Create the roles, or dump again with pg_dump --no-owner --no-privileges.

Restore a custom, directory or tar archive with pg_restore

sudo -u postgres pg_restore -d shop -j 4 --exit-on-error /var/backups/postgresql/shop.dump

• -d is the database to restore into.
• -j 4 loads data and builds indexes and constraints in four sessions, each its own connection. Custom and directory archives only, read from disk rather than a pipe, and not with --single-transaction. The docs suggest starting at the server's CPU core count.
• --exit-on-error (-e) stops at the first error. By default pg_restore carries on, ends with errors ignored on restore: N and exits 1.
• --single-transaction (-1) is the all-or-nothing alternative to -j.

For a directory archive, pass the directory. --create (-C) also creates the database, with its archived name, owner, settings and grants; -d then only names the database to connect to first:

sudo -u postgres pg_restore -C --exit-on-error -d postgres /var/backups/postgresql/shop.dump

To overwrite a database in place, use --clean --if-exists instead (pg_dump guide). --clean --create drops the whole database and fails with database "shop" is being accessed by other users while anything is connected; without -e it then restored into the old one, as above.

Restore without superuser rights

On a managed database, or where logging in as postgres is not allowed, the restoring user has no rights in a new database. Our restorer, restoring into one owned by postgres, got permission denied for database shop, permission denied to create extension "citext" and this:

pg_restore: error: could not execute query: ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.customers (
                     ^

Make the restoring user the database owner: it then owns public (via pg_database_owner) and may create schemas and trusted extensions. The restore then ran clean:

sudo -u postgres createdb -T template0 -O restorer shop

pg_restore -h localhost -U restorer --no-owner --no-privileges -d shop shop.dump

• --no-owner (-O) skips ALTER ... OWNER TO, so restorer owns everything.
• --no-privileges (-x) skips GRANT and REVOKE; grant access yourself afterwards.
• --role=app_owner keeps the original owners: pg_restore logs in as one user, then runs SET ROLE app_owner. Our bob (a member of app_owner with INHERIT FALSE), restoring into a database owned by app_owner, hit the same permission errors without it and restored cleanly with it.

pg_restore -h localhost -U bob --role=app_owner -d shop shop.dump

Untrusted extensions such as postgres_fdw fail with permission denied to create extension "postgres_fdw" (HINT: Must be superuser to create this extension.). Have a superuser or your provider create it in the empty database first; the restore then only trips on COMMENT ON EXTENSION, which --no-comments skips. Provider limits are in managed database backups.

Restore part of an archive

-l prints the archive's table of contents, one line per object:

pg_restore -l shop.dump | grep 'public orders'

220; 1259 16504 TABLE public orders app_owner
219; 1259 16503 SEQUENCE public orders_id_seq app_owner
3511; 0 16504 TABLE DATA public orders app_owner
3527; 0 0 SEQUENCE SET public orders_id_seq app_owner
3362; 2606 16509 CONSTRAINT public orders orders_pkey app_owner
3360; 1259 16515 INDEX public orders_customer_idx app_owner
3363; 2606 16510 FK CONSTRAINT public orders orders_customer_id_fkey app_owner

Save the whole list, comment out lines with a leading ;, and restore with -L, which runs the remaining items in the file's order. This leaves out the rows of orders, a common way to skip a huge log table:

pg_restore -l shop.dump > shop.list

sed -i '/TABLE DATA public orders /s/^/;/' shop.list

sudo -u postgres pg_restore -L shop.list -d shop_nodata shop.dump

In the empty shop_nodata, orders came back with no rows but with its primary key and index. The shortcuts leave more out:

• -n reporting restores the objects in that schema but not the schema itself. pg_restore 16 failed with schema "reporting" does not exist until we ran CREATE SCHEMA reporting first, and the SQL that pg_restore 17 and 18 generate has no CREATE SCHEMA either.
• -t customers restores one table and its rows, but not its indexes or constraints, nor anything it depends on: ours failed with type "public.citext" does not exist. -t takes no wildcards or schema names; add -n for the schema.

Version rules

On Debian and Ubuntu, /usr/bin/pg_restore runs the local cluster's version but /usr/bin/psql the newest installed one: on our server, 16 and 18. Run another version by full path, such as /usr/lib/postgresql/18/bin/pg_restore.

Refresh statistics and count the rows

A dump carries no planner statistics by default, so the first queries may pick poor plans. --analyze-in-stages produces rough statistics first, then full ones:

sudo -u postgres vacuumdb --analyze-in-stages -d shop

vacuumdb: processing database "shop": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "shop": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "shop": Generating default (full) optimizer statistics

Then check that every row arrived. This query counts the rows in each table:

SELECT n.nspname || '.' || c.relname AS table_name,
       (xpath('/row/n/text()',
              query_to_xml(format('SELECT count(*) AS n FROM %I.%I', n.nspname, c.relname), false, true, '')))[1]::text::bigint AS row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY 1;

Run it on the source when you take the dump and keep the result next to the file. After the restore, run it on the restored database and compare:

sudo -u postgres psql -X -At -F ' ' -f rowcounts.sql -d shop > shop-rowcounts.txt

sudo -u postgres psql -X -At -F ' ' -f rowcounts.sql -d shop | diff shop-rowcounts.txt - && echo "row counts match"

On the truncated restore above, where psql had exited 0, the diff caught the gap:

3c3
< reporting.daily_totals 29
---
> reporting.daily_totals 20

Counts prove the rows arrived, not that the application works; run its checks too, and make it a routine with scheduled test restores.

Common errors

Frequently asked questions

How do I restore a .sql file in PostgreSQL?

With psql, into an existing empty database: psql -X -v ON_ERROR_STOP=1 --single-transaction -d mydb -f file.sql. pg_restore cannot read plain SQL.

How do I restore a .dump file?

If it starts with PGDMP it is a custom-format archive: pg_restore -d mydb file.dump into an empty database, or pg_restore -C -e -d postgres file.dump to create it too.

Can I restore a dump into a database with a different name?

Yes. Without -C, pg_restore and psql load into whatever database -d names. Only pg_restore -C, or a plain dump made with pg_dump -C, uses the original name.

Why does pg_restore exit with status 1 when the data looks fine?

It ignored errors and printed how many. Ownership and comment errors are often harmless, but read each one and compare row counts before trusting the result.