pg_restore
Restore a PostgreSQL database from an archive
TLDR
Restore an archive into an existing database
Same as above, customize username
Same as above, customize host and port
List database objects included in the archive
Clean database objects before creating them
Use multiple jobs to do the restoring
SYNOPSIS
pg_restore [connection-options] [options] [filename]
PARAMETERS
-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).
DESCRIPTION
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.
CAVEATS
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.
EXIT STATUS
pg_restore returns 0 on success, and nonzero if a problem is encountered.
ENVIRONMENT VARIABLES
pg_restore utilizes the environment variables supported by libpq, such as PGHOST, PGPORT, PGUSER, and PGPASSWORD, for database connection parameters.
HISTORY
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.