The short version
For pg_dump and pg_restore, the one command to remember is pg_dump -Fc -f db.dump mydb followed by pg_restore -d newdb db.dump. The custom format (-Fc) is compressed, and pg_restore can list it, pick objects out of it and restore it in parallel with -j. A plain .sql dump goes through psql, never pg_restore, and a custom dump fed to psql does nothing. Roles are not in either one: take them with pg_dumpall --globals-only, or restore with --no-owner --no-privileges when the target has different users.
Everything below was run against a PostgreSQL 16.14 server in a local container, with pg_dump, pg_restore and psql 18.6 from Homebrew on an M1 Pro laptop. The sample database has a shop schema with three tables (50,000 customers, 400,000 orders, 300,000 JSON audit rows), a view, two indexes, a foreign key, an owner role and a read-only role. It is 140 MB on disk. In the commands, $DB is that database and target stands for whichever empty scratch database I restored into; the real names were longer.
Plain, custom or directory: which pg_dump format?
$ pg_dump -d $DB -f shop.sql
$ pg_dump -d $DB -Fc -f shop.dump
$ pg_dump -d $DB -Fd -j 4 -f shop.dir
$ du -sh shop.sql shop.dump shop.dir
69M shop.sql
9.6M shop.dump
9.6M shop.dir
$ ls -l shop.dir
-rw-r--r--@ 1 alex wheel 430379 Oct 25 19:08 3436.dat.gz
-rw-r--r--@ 1 alex wheel 5065956 Oct 25 19:08 3437.dat.gz
-rw-r--r--@ 1 alex wheel 4584554 Oct 25 19:08 3439.dat.gz
-rw-r--r--@ 1 alex wheel 7307 Oct 25 19:08 toc.dat
Two runs of each, wall-clock seconds from time:
| Format | Flag | Size | Dump | Restore | Restore with -j 4 |
|---|---|---|---|---|---|
| Plain SQL | (default) | 69 MB | 0.77, 0.82 | 2.30, 1.45 (psql) | not possible |
| Custom | -Fc |
9.6 MB | 1.23, 0.94 | 1.53, 1.39 | 0.98, 0.76 |
| Directory | -Fd -j 4 |
9.6 MB | 0.58, 0.60 | 1.05, 1.12 | |
| Tar | -Ft |
69 MB | 0.71 |
On a database this small the times are noise around a second; the size column is the part that holds. The plain and tar files are uncompressed, and the pg_dump reference says why: custom and directory are compressed with gzip by default, tar "does not support compression". The directory format is the only one pg_dump can write in parallel, and it refuses on the others:
$ pg_dump -d $DB -Fc -j 4 -f x.dump
pg_dump: error: parallel backup only supported by the directory format
Parallel restore is wider. The backup chapter says pg_restore -j works on any custom or directory archive, whether or not pg_dump -j made it, which my custom -j 4 row shows. Two things stop it:
$ pg_restore -j 4 -1 -d target shop.dump
pg_restore: error: cannot specify both --single-transaction and multiple jobs
$ cat shop.dump | pg_restore -j 4 -d target
pg_restore: error: parallel restore from standard input is not supported
Directory -j also opens one connection per job plus one, so check max_connections on a busy server first. My default is -Fc for anything I may restore selectively, and -Fd -j N when the dump itself takes too long.
How to restore: psql for .sql, pg_restore for the rest
The two tools do not accept each other's files. Each one says so and exits 1:
$ psql -d target -f shop.dump
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.
psql exit=1
$ pg_restore -d target shop.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.
pg_restore exit=1
The target database has to exist first; the backup chapter recommends creating it from template0 (createdb -T template0 newdb), or use -C so the dump creates it. Then:
psql -X -d newdb -f shop.sql
pg_restore -d newdb shop.dump
pg_restore -j 4 -d newdb shop.dir
The error every restore printed
Every restore in this article printed this line, and it deserves a section before the others:
$ psql -q -X -v ON_ERROR_STOP=1 -d target -f shop.sql
psql:shop.sql:13: ERROR: unrecognized configuration parameter "transaction_timeout"
exit=3
pg_dump 18 wrote SET transaction_timeout = 0; near the top of the dump, and that setting arrived in PostgreSQL 17. A 16 server does not know it. The dump came from a 16 server, and it still does not load cleanly into a 16 server, which is exactly what the pg_dump page warns about: output is "not guaranteed" to load into an older major version, "not even if the dump was taken from a server of that version". The custom and directory restores hit the same line.
The line is harmless, a session setting the old server ignores, so I left it. With ON_ERROR_STOP it stops a plain restore at line 13 with exit 3. Without it, psql printed the error, loaded all 400,000 orders and exited 0. Per the psql page, exit 3 is only for a script error with ON_ERROR_STOP set, so a shell script checking $? without that variable will not see restore errors at all. pg_restore is the opposite: by default it carries on and prints a count (errors ignored on restore: 1) and exits 1. Read the count, and when it is above the one line you expect, read the errors.
The clean fix is a pg_dump of the same major version as the target. Restoring into a different version is its own article: pg_dump: server version mismatch.
The plain file also opens with \restrict and ends with \unrestrict, followed by a random key. The psql reference describes it as a "restricted" mode for plain dumps in which no other meta-command runs.
pg_dump schema only, data only
$ pg_dump -d $DB -s -f schema.sql
$ pg_dump -d $DB -a -f data.sql
$ ls -l schema.sql data.sql
-rw-r--r--@ 1 alex wheel 72030000 Oct 25 19:14 data.sql
-rw-r--r--@ 1 alex wheel 4624 Oct 25 19:14 schema.sql
-s is 4.6 KB of DDL. -a has the COPY blocks and no CREATE TABLE at all, so it needs a schema already in place with matching columns.
pg_dump exclude table: -t, -T and --exclude-table-data
$ pg_dump -d $DB -t shop.customers -t shop.orders -f two_tables.sql
$ pg_dump -d $DB -T shop.audit_log -f no_audit.sql
$ pg_dump -d $DB --exclude-table-data=shop.audit_log -Fc -f no_audit_rows.dump
$ ls -l two_tables.sql no_audit.sql no_audit_rows.dump
-rw-r--r--@ 1 alex wheel 22527192 Oct 25 19:14 no_audit.sql
-rw-r--r--@ 1 alex wheel 5510237 Oct 25 19:14 no_audit_rows.dump
-rw-r--r--@ 1 alex wheel 22525994 Oct 25 19:14 two_tables.sql
$ grep -E '^(CREATE|COPY)' no_audit.sql
CREATE SCHEMA shop;
CREATE SEQUENCE shop.audit_log_id_seq
CREATE TABLE shop.customers (
CREATE TABLE shop.orders (
CREATE VIEW shop.revenue_by_country AS
COPY shop.customers (id, email, country, created_at) FROM stdin;
COPY shop.orders (id, customer_id, amount, status, ordered_at) FROM stdin;
CREATE INDEX orders_customer_idx ON shop.orders USING btree (customer_id);
CREATE INDEX orders_ordered_at_idx ON shop.orders USING btree (ordered_at);
$ grep -c 'CREATE SCHEMA' two_tables.sql
0
Three details from that output:
-T shop.audit_logdropped the table but kept itsaudit_log_id_seq, an orphan sequence in the target.-tdoes not dump the schema the table lives in.two_tables.sqlhas noCREATE SCHEMA shop, so into an empty database it fails on the firstCREATE TABLE.--exclude-table-datais the one I use most: the audit table is created, empty, with its key and sequence, and the 300,000 log rows stay behind. The reference describes it as the option for when "you need the definition of a particular table" but not its data.
All three take patterns, and can be repeated. -n / -N do the same for whole schemas.
pg_restore -l and -L: restore only some objects
A custom or directory archive has a table of contents. -l prints it:
$ pg_restore -l shop.dump > shop.list
$ cat shop.list
;
; Archive created at 2026-10-25 19:08:50 EDT
; dbname: pg_dump_and_pg_restore_the_commands_that_matter
; TOC Entries: 27
; Compression: gzip
...
; Dumped from database version: 16.14 (Debian 16.14-1.pgdg13+1)
; Dumped by pg_dump version: 18.6
...
6; 2615 19551 SCHEMA - shop pgdump_tip_owner
219; 1259 19576 TABLE shop audit_log pgdump_tip_owner
...
3439; 0 19576 TABLE DATA shop audit_log pgdump_tip_owner
3436; 0 19552 TABLE DATA shop customers pgdump_tip_owner
3437; 0 19561 TABLE DATA shop orders pgdump_tip_owner
...
3285; 1259 19573 INDEX shop orders_customer_idx pgdump_tip_owner
...
3291; 2606 19568 FK CONSTRAINT shop orders orders_customer_id_fkey pgdump_tip_owner
Edit the file, putting a ; at the start of any line to skip it (pg_restore also restores the rest in the order of the file), then pass it with -L. Here the audit rows are commented out after the dump was taken, which is the same result as --exclude-table-data decided later:
$ diff shop.list shop.edited.list
28c28
< 3439; 0 19576 TABLE DATA shop audit_log pgdump_tip_owner
---
> ;3439; 0 19576 TABLE DATA shop audit_log pgdump_tip_owner
$ pg_restore -L shop.edited.list -d target shop.dump
$ psql -d target -c "select (select count(*) from shop.audit_log) audit_rows, (select count(*) from shop.orders) orders"
audit_rows | orders
------------+--------
0 | 400000
pg_restore also has its own -t, and it is narrower than it looks. Into an empty database it failed on the missing schema (5 errors). With the schema created by hand, it restored the table and its rows but nothing else:
$ pg_restore -t orders -d target shop.dump
$ psql -d target -c '\d shop.orders'
Table "shop.orders"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
id | bigint | | not null |
customer_id | integer | | not null |
amount | numeric(12,2) | | not null |
status | text | | not null |
ordered_at | timestamp with time zone | | not null |
No primary key, no indexes, no foreign key. The reference says so: pg_restore's -t does not include subsidiary objects such as indexes, makes "no attempt" to restore what the table depends on, and takes no schema name or wildcard. For one table with its keys, the -l / -L route is the dependable one.
--clean --if-exists: restoring over an existing database
Restoring the same archive twice into the same database, with no flags:
$ pg_restore -d target shop.dump 2>&1 | grep -E '^pg_restore: (error|warning)' | sort | uniq -c
...
1 pg_restore: error: COPY failed for table "orders": ERROR: duplicate key value violates unique constraint "orders_pkey"
...
1 pg_restore: error: could not execute query: ERROR: relation "orders" already exists
...
1 pg_restore: error: could not execute query: ERROR: schema "shop" already exists
1 pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout"
1 pg_restore: warning: errors ignored on restore: 17
--clean drops each object before creating it. On an empty database that gave 15 errors, one schema "shop" does not exist after another, because there was nothing to drop. --clean --if-exists writes DROP ... IF EXISTS and works both ways:
$ pg_restore --clean --if-exists -d target shop.dump
pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
pg_restore: warning: errors ignored on restore: 1
$ psql -At -d target -c "select count(*), sum(amount) from shop.orders"
400000|99851325.81
The same two flags exist on pg_dump, for plain dumps that will be run with psql.
--no-owner and --no-privileges
The dump records ALTER TABLE ... OWNER TO pgdump_tip_owner and the GRANTs to the reader role. I restored it as an ordinary login, pgdump_tip_app, which owns the target database but is nobody else:
$ pg_restore -d pgdump_tip_app shop.dump 2>&1 | grep -E '^pg_restore: (error|warning)' | sort | uniq -c
6 pg_restore: error: could not execute query: ERROR: must be able to SET ROLE "pgdump_tip_owner"
1 pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout"
1 pg_restore: warning: errors ignored on restore: 7
$ pg_restore -O -x -d pgdump_tip_app shop.dump
...
pg_restore: warning: errors ignored on restore: 1
$ psql -d pgdump_tip_app -c '\dt shop.*'
List of tables
Schema | Name | Type | Owner
--------+-----------+-------+----------------
shop | audit_log | table | pgdump_tip_app
shop | customers | table | pgdump_tip_app
shop | orders | table | pgdump_tip_app
With -O the restoring user owns everything; with -x no grants are replayed, so you set up permissions for the new environment yourself. This is the usual pair for a managed target where the old roles do not exist and you do not log in as a superuser, which is the case in on-prem PostgreSQL to Amazon RDS.
pg_dumpall --globals-only for the roles
pg_dump dumps one database and no roles or tablespaces, and the backup chapter says the roles that own objects or hold grants must exist before the restore. pg_dumpall -g writes just those cluster-wide objects as SQL for psql:
$ pg_dumpall --globals-only -f globals.sql
$ ls -l globals.sql
-rw-r--r--@ 1 alex wheel 1381 Oct 25 19:15 globals.sql
$ grep -E 'pgdump_tip' globals.sql
CREATE ROLE pgdump_tip_app;
...
CREATE ROLE pgdump_tip_owner;
ALTER ROLE pgdump_tip_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096...';
CREATE ROLE pgdump_tip_reader;
ALTER ROLE pgdump_tip_reader WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
$ pg_dumpall --globals-only --no-role-passwords | grep pgdump_tip_owner
CREATE ROLE pgdump_tip_owner;
ALTER ROLE pgdump_tip_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
I cut the password hashes after the first characters. The file carries real SCRAM hashes, so treat it like a password file. --no-role-passwords leaves them out, and those roles then cannot log in until you set a password. Run the globals file with psql -X -f globals.sql postgres before the database restore. The backup chapter notes that restoring roles needs superuser access.
The commands, together
# source
pg_dumpall --globals-only -f globals.sql
pg_dump -Fc -f mydb.dump mydb # or -Fd -j 4 -f mydb.dir
pg_restore -l mydb.dump > mydb.list # see what is inside
# target
psql -X -f globals.sql postgres
createdb -T template0 mydb
pg_restore -j 4 -d mydb mydb.dump # or -L mydb.list, -O -x
psql -d mydb -c 'ANALYZE'
The last line is there because pg_dump 18 does not dump optimizer statistics unless you add --statistics, and the backup chapter recommends ANALYZE after a restore.
If your data arrives as a .sql file from somewhere other than pg_dump, such as a MySQL or SQLite export, the SQL to PostgreSQL converter turns it into PostgreSQL SQL that psql can load.