pg_restore - restore a PostgreSQL database from an archive file created by pg_dump

Options you will use

-d, --dbname=NAME

connect to database name and restore into it (without -d it prints SQL).

-l, --list

print summarized TOC of the archive.

-L, --use-list=FILENAME

use table of contents from this file for selecting/ordering output.

-C, --create

create the target database (connect with -d postgres).

-c, --clean

clean (drop) database objects before recreating.

--if-exists

use IF EXISTS when dropping objects.

-e, --exit-on-error

exit on error, default is to continue.

-j, --jobs=NUM

use this many parallel jobs to restore.

-1, --single-transaction

restore as a single transaction.

-O, --no-owner

skip restoration of object ownership.

-x, --no-privileges

skip restoration of access privileges (grant/revoke).

-n, --schema=NAME

restore only objects in this schema.

-t, --table=NAME

restore named relation (table, view, etc.).

-a, --data-only

restore only the data, no schema.

-s, --schema-only

restore only the schema, no data.

-f, --file=FILENAME

output file name (- for stdout).

-h, --host=HOSTNAME

database server host or socket directory (default: the Unix socket in /var/run/postgresql). A value starting with / is a socket directory; anything else is TCP.

-p, --port=PORT

database server port (default 5432).

-U, --username=USERNAME

database user name (default: your OS user name).

-w, --no-password

never prompt for a password (fail instead: what scripts want).

-W, --password

force a password prompt (normally automatic).

Examples

$ pg_restore -l orders.dump | head

what is in the archive, and when it was made

$ createdb scratch && pg_restore -d scratch orders.dump

a full restore into a new database

$ pg_restore -C -d postgres orders.dump

recreate the database named in the dump

$ pg_restore -d scratch -t customers orders.dump

just one table

Gotchas

• Never restore over the production database: restore into a scratch database and copy what you need.
• "role ... does not exist": restore the globals (pg_dumpall -g) first, or use --no-owner --no-privileges for a scratch copy.
• "input file appears to be a text format dump. Please use psql." - a plain dump: psql -f file.

Taught in

• 25.43 Backups: pg_dump, pg_restore and testing restores