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
    Specifies the database to connect to and restore into.

-f filename
    Specifies the output file for a script format archive. If not specified, standard output is used.

-h hostname
    Specifies the host name of the machine on which the server is running.

-j number
    Runs the most time-consuming parts of pg_restore, those that load data, create indexes, or create constraints, in parallel by using multiple concurrent jobs. Requires a custom archive format.

-l
    List the contents of the archive.

-p port
    Specifies the port number the server is listening on.

-U username
    Connect as the specified username.

-v
    Specifies verbose mode.

-c
    Clean (drop) database objects before recreating them.

-C
    Create the database before restoring into it.

-t table
    Restore only the named table(s).

-n schema
    Restore only objects in the named schema(s).

pg_restore is a utility for restoring PostgreSQL databases from an archive created by pg_dump.

It reads the archive and rebuilds the database in a consistent state. The archive can be in one of several formats: plain text SQL script, tar archive, or a custom format designed for pg_restore. pg_restore can either restore the entire database or selectively restore specific parts, such as tables, schemas, or even data.

The utility supports parallel restoration to significantly speed up the process, especially for large databases. It connects to the PostgreSQL server as a normal client, using libpq. Thus, it can be used remotely, provided that appropriate network configurations are in place. Before running pg_restore, ensure you have the necessary permissions on the target database and that the database exists, unless the -c or -C option is specified to create the database.

Restoring to a database server that is a different major version from the one used to create the dump can cause problems. pg_restore attempts to resolve these problems, but complete success is not guaranteed.

When restoring a database that contains extensions, make sure the extensions are installed in the target database before running pg_restore. This often requires running CREATE EXTENSION before restoring the data.

Parallel restores require a custom archive format and cannot be used with plain-text SQL archives.

pg_restore returns 0 on success, and nonzero if a problem is encountered.

pg_restore utilizes the environment variables supported by libpq, such as PGHOST, PGPORT, PGUSER, and PGPASSWORD, for database connection parameters.

pg_restore has been a core utility within the PostgreSQL ecosystem since its early days. Its development has closely followed the evolution of pg_dump, ensuring compatibility and support for new features in each PostgreSQL release. Over time, features like parallel restoration and selective object restoration have been added to enhance its performance and flexibility. It's designed to be a reliable and versatile tool for managing PostgreSQL database backups and restores.

pg_dump(1), psql(1)