pg_restore

Restore an archive into an existing database

$ pg_restore [[-d|--dbname]] [db_name] [archive_file.dump]

$ pg_restore [[-U|--username]] [username] [[-d|--dbname]] [db_name] [archive_file.dump]

$ pg_restore [[-h|--host]] [host] [[-p|--port]] [port] [[-d|--dbname]] [db_name] [archive_file.dump]

$ pg_restore [[-l|--list]] [archive_file.dump]

$ pg_restore [[-c|--clean]] [[-d|--dbname]] [db_name] [archive_file.dump]

$ pg_restore [[-j|--jobs]] [2] [[-d|--dbname]] [db_name] [archive_file.dump]

pg_restore [connection-options] [options] [filename]

-d DBNAME, --dbname=DBNAME
    Connect to the database DBNAME on the target server. This specifies the database to which pg_restore should restore.

-F FORMAT, --format=FORMAT
    Specify the format of the archive file. Valid formats are c (custom), d (directory), t (tar), or p (plain, for compatibility only, usually requires psql).

-h HOSTNAME, --host=HOSTNAME
    Specify the host name of the machine on which the database server is running. If omitted, it defaults to a local Unix domain socket connection.

-p PORT, --port=PORT
    Specify the TCP port or local Unix domain socket file extension on which the database server is listening for connections.

-U USERNAME, --username=USERNAME
    Connect to the database as the user USERNAME.

-W, --password
    Force pg_restore to prompt for a password before connecting to the database.

-c, --clean
    Clean (drop) database objects before recreating them. This is useful for overwriting an existing database.

-C, --create
    Create the database before restoring into it. This will also issue a CONNECT command to the newly created database.

-j NUM, --jobs=NUM
    Run NUM parallel jobs to restore the database objects. This significantly speeds up the restoration process on multi-core systems, especially for large archives.

-L, --list
    List the contents of the archive file. The output can be redirected to a file and edited to select or reorder items for restoration.

-a, --data-only
    Restore only the data, not the schema (definitions) of objects.

-s, --schema-only
    Restore only the schema (definitions), not the data.

-t TABLE, --table=TABLE
    Restore only the definition and/or data of the specified table. Multiple -t options can be given.

-n SCHEMA, --schema=SCHEMA
    Restore only the definition and/or data of the specified schema. Multiple -n options can be given.

-O, --no-owner
    Do not set ownership of objects to the original owners. All objects will be owned by the user connecting to the database (specified by -U).

-x, --no-privileges, --no-acl
    Do not restore access privileges (ACLs). All objects will have default privileges.

-v, --verbose
    Enable verbose mode. This prints detailed information about the restoration process to standard output.

pg_restore is a powerful utility designed to restore a PostgreSQL database from an archive file created by pg_dump. Unlike simply replaying a plain-text SQL script, pg_restore can work with flexible archive formats (custom, directory, or tar format), offering significant advantages for large and complex restorations.

It provides granular control, allowing users to restore an entire database, specific tables, schemas, or even individual database objects like functions or indexes. This selective restoration capability is invaluable for development, testing, or disaster recovery scenarios where only parts of a database need to be recreated.

pg_restore connects to a running PostgreSQL server to perform the restore operations. It supports parallel restoration via the -j (--jobs) option, which can dramatically speed up the process for multi-core systems and large datasets. It's the go-to tool for restoring backups created with pg_dump's custom or directory formats, which are generally preferred for their flexibility and efficiency over plain-text dumps.

pg_restore requires a running PostgreSQL server to connect to and restore data. It cannot directly process plain-text SQL dump files; for those, use psql -f. Ensure the user performing the restore has sufficient privileges on the target database and objects. When using the --clean option, be aware that existing objects will be dropped, which is a destructive operation.

pg_restore is primarily used with the custom (-Fc), directory (-Fd), and tar (-Ft) archive formats generated by pg_dump. These formats contain more metadata than plain-text SQL scripts, allowing for selective restoration and parallel processing. The directory format is especially useful for large backups, as it generates multiple files for different object types, making it easier to manage and transfer.

If your pg_dump output is a plain-text SQL script (the default), you should use the psql command-line tool to restore it. For example: psql -d mydatabase -f mydump.sql. pg_restore is designed for the more flexible, non-plain-text archive formats.

pg_restore has been an integral part of the PostgreSQL utility suite since its early versions, evolving alongside the database to support new features, object types, and performance optimizations. Its development has focused on providing robust, flexible, and efficient restoration capabilities for the various backup formats produced by pg_dump.

pg_dump(1), psql(1), pg_basebackup(1)