Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / man1 / pg_restore.1

'\" t
.\"     Title: pg_restore
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2025
.\"    Manual: PostgreSQL 18.0 Documentation
.\"    Source: PostgreSQL 18.0
.\"  Language: English
.\"
.TH "PG_RESTORE" "1" "2025" "PostgreSQL 18.0" "PostgreSQL 18.0 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
pg_restore \- restore a PostgreSQL database from an archive file created by pg_dump
.SH "SYNOPSIS"
.HP \w'\fBpg_restore\fR\ 'u
\fBpg_restore\fR [\fIconnection\-option\fR...] [\fIoption\fR...] [\fIfilename\fR]
.SH "DESCRIPTION"
.PP
pg_restore
is a utility for restoring a
PostgreSQL
database from an archive created by
\fBpg_dump\fR(1)
in one of the non\-plain\-text formats\&. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved\&. The archive files also allow
pg_restore
to be selective about what is restored, or even to reorder the items prior to being restored\&. The archive files are designed to be portable across architectures\&.
.PP
pg_restore
can operate in two modes\&. If a database name is specified,
pg_restore
connects to that database and restores archive contents directly into the database\&. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output\&. This script output is equivalent to the plain text output format of
pg_dump\&. Some of the options controlling the output are therefore analogous to
pg_dump
options\&.
.PP
Obviously,
pg_restore
cannot restore information that is not present in the archive file\&. For instance, if the archive was made using the
\(lqdump data as \fBINSERT\fR commands\(rq
option,
pg_restore
will not be able to load the data using
\fBCOPY\fR
statements\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBWarning\fR
.ps -1
.br
.PP
Restoring a dump causes the destination to execute arbitrary code of the source superusers\*(Aq choice\&. Partial dumps and partial restores do not limit that\&. If the source superusers are not trusted, the dumped SQL statements must be inspected before restoring\&. Non\-plain\-text dumps can be inspected by using
pg_restore\*(Aqs
\fB\-\-file\fR
option\&. Note that the client running the dump and restore need not trust the source or destination superusers\&.
.sp .5v
.RE
.SH "OPTIONS"
.PP
pg_restore
accepts the following command line arguments\&.
.PP
\fIfilename\fR
.RS 4
Specifies the location of the archive file (or directory, for a directory\-format archive) to be restored\&. If not specified, the standard input is used\&.
.RE
.PP
\fB\-a\fR
.br
\fB\-\-data\-only\fR
.RS 4
Restore only the data, not the schema (data definitions) or statistics\&. Table data, large objects, and sequence values are restored, if present in the archive\&.
.sp
This option is similar to, but for historical reasons not identical to, specifying
\fB\-\-section=data\fR\&.
.RE
.PP
\fB\-c\fR
.br
\fB\-\-clean\fR
.RS 4
Before restoring database objects, issue commands to
\fBDROP\fR
all the objects that will be restored\&. This option is useful for overwriting an existing database\&. If any of the objects do not exist in the destination database, ignorable error messages will be reported, unless
\fB\-\-if\-exists\fR
is also specified\&.
.RE
.PP
\fB\-C\fR
.br
\fB\-\-create\fR
.RS 4
Create the database before restoring into it\&. If
\fB\-\-clean\fR
is also specified, drop and recreate the target database before connecting to it\&.
.sp
With
\fB\-\-create\fR,
pg_restore
also restores the database\*(Aqs comment if any, and any configuration variable settings that are specific to this database, that is, any
\fBALTER DATABASE \&.\&.\&. SET \&.\&.\&.\fR
and
\fBALTER ROLE \&.\&.\&. IN DATABASE \&.\&.\&. SET \&.\&.\&.\fR
commands that mention this database\&. Access privileges for the database itself are also restored, unless
\fB\-\-no\-acl\fR
is specified\&.
.sp
When this option is used, the database named with
\fB\-d\fR
is used only to issue the initial
\fBDROP DATABASE\fR
and
\fBCREATE DATABASE\fR
commands\&. All data is restored into the database name that appears in the archive\&.
.RE
.PP
\fB\-d \fR\fB\fIdbname\fR\fR
.br
\fB\-\-dbname=\fR\fB\fIdbname\fR\fR
.RS 4
Connect to database
\fIdbname\fR
and restore directly into the database\&. The
\fIdbname\fR
can be a
connection string\&. If so, connection string parameters will override any conflicting command line options\&.
.RE
.PP
\fB\-e\fR
.br
\fB\-\-exit\-on\-error\fR
.RS 4
Exit if an error is encountered while sending SQL commands to the database\&. The default is to continue and to display a count of errors at the end of the restoration\&.
.RE
.PP
\fB\-f \fR\fB\fIfilename\fR\fR
.br
\fB\-\-file=\fR\fB\fIfilename\fR\fR
.RS 4
Specify output file for generated script, or for the listing when used with
\fB\-l\fR\&. Use
\-
for
stdout\&.
.RE
.PP
\fB\-F \fR\fB\fIformat\fR\fR
.br
\fB\-\-format=\fR\fB\fIformat\fR\fR
.RS 4
Specify format of the archive\&. It is not necessary to specify the format, since
pg_restore
will determine the format automatically\&. If specified, it can be one of the following:
.PP
c
.br
custom
.RS 4
The archive is in the custom format of
pg_dump\&.
.RE
.PP
d
.br
directory
.RS 4
The archive is a directory archive\&.
.RE
.PP
t
.br
tar
.RS 4
The archive is a
\fBtar\fR
archive\&.
.RE
.RE
.PP
\fB\-I \fR\fB\fIindex\fR\fR
.br
\fB\-\-index=\fR\fB\fIindex\fR\fR
.RS 4
Restore definition of named index only\&. Multiple indexes may be specified with multiple
\fB\-I\fR
switches\&.
.RE
.PP
\fB\-j \fR\fB\fInumber\-of\-jobs\fR\fR
.br
\fB\-\-jobs=\fR\fB\fInumber\-of\-jobs\fR\fR
.RS 4
Run the most time\-consuming steps of
pg_restore
\(em those that load data, create indexes, or create constraints \(em concurrently, using up to
\fInumber\-of\-jobs\fR
concurrent sessions\&. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine\&. This option is ignored when emitting a script rather than connecting directly to a database server\&.
.sp
Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server\&.
.sp
The optimal value for this option depends on the hardware setup of the server, of the client, and of the network\&. Factors include the number of CPU cores and the disk setup\&. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases\&. Of course, values that are too high will lead to decreased performance because of thrashing\&.
.sp
Only the custom and directory archive formats are supported with this option\&. The input must be a regular file or directory (not, for example, a pipe or standard input)\&. Also, multiple jobs cannot be used together with the option
\fB\-\-single\-transaction\fR\&.
.RE
.PP
\fB\-l\fR
.br
\fB\-\-list\fR
.RS 4
List the table of contents of the archive\&. The output of this operation can be used as input to the
\fB\-L\fR
option\&. Note that if filtering switches such as
\fB\-n\fR
or
\fB\-t\fR
are used with
\fB\-l\fR, they will restrict the items listed\&.
.RE
.PP
\fB\-L \fR\fB\fIlist\-file\fR\fR
.br
\fB\-\-use\-list=\fR\fB\fIlist\-file\fR\fR
.RS 4
Restore only those archive elements that are listed in
\fIlist\-file\fR, and restore them in the order they appear in the file\&. Note that if filtering switches such as
\fB\-n\fR
or
\fB\-t\fR
are used with
\fB\-L\fR, they will further restrict the items restored\&.
.sp
\fIlist\-file\fR
is normally created by editing the output of a previous
\fB\-l\fR
operation\&. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line\&. See below for examples\&.
.RE
.PP
\fB\-n \fR\fB\fIschema\fR\fR
.br
\fB\-\-schema=\fR\fB\fIschema\fR\fR
.RS 4
Restore only objects that are in the named schema\&. Multiple schemas may be specified with multiple
\fB\-n\fR
switches\&. This can be combined with the
\fB\-t\fR
option to restore just a specific table\&.
.RE
.PP
\fB\-N \fR\fB\fIschema\fR\fR
.br
\fB\-\-exclude\-schema=\fR\fB\fIschema\fR\fR
.RS 4
Do not restore objects that are in the named schema\&. Multiple schemas to be excluded may be specified with multiple
\fB\-N\fR
switches\&.
.sp
When both
\fB\-n\fR
and
\fB\-N\fR
are given for the same schema name, the
\fB\-N\fR
switch wins and the schema is excluded\&.
.RE
.PP
\fB\-O\fR
.br
\fB\-\-no\-owner\fR
.RS 4
Do not output commands to set ownership of objects to match the original database\&. By default,
pg_restore
issues
\fBALTER OWNER\fR
or
\fBSET SESSION AUTHORIZATION\fR
statements to set ownership of created schema elements\&. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script)\&. With
\fB\-O\fR, any user name can be used for the initial connection, and this user will own all the created objects\&.
.RE
.PP
\fB\-P \fR\fB\fIfunction\-name(argtype [, \&.\&.\&.])\fR\fR
.br
\fB\-\-function=\fR\fB\fIfunction\-name(argtype [, \&.\&.\&.])\fR\fR
.RS 4
Restore the named function only\&. Be careful to spell the function name and arguments exactly as they appear in the dump file\*(Aqs table of contents\&. Multiple functions may be specified with multiple
\fB\-P\fR
switches\&.
.RE
.PP
\fB\-R\fR
.br
\fB\-\-no\-reconnect\fR
.RS 4
This option is obsolete but still accepted for backwards compatibility\&.
.RE
.PP
\fB\-s\fR
.br
\fB\-\-schema\-only\fR
.RS 4
Restore only the schema (data definitions), not data, to the extent that schema entries are present in the archive\&.
.sp
This option cannot be used with
\fB\-\-data\-only\fR
or
\fB\-\-statistics\-only\fR\&. It is similar to, but for historical reasons not identical to, specifying
\fB\-\-section=pre\-data \-\-section=post\-data \-\-no\-statistics\fR\&.
.sp
(Do not confuse this with the
\fB\-\-schema\fR
option, which uses the word
\(lqschema\(rq
in a different meaning\&.)
.RE
.PP
\fB\-S \fR\fB\fIusername\fR\fR
.br
\fB\-\-superuser=\fR\fB\fIusername\fR\fR
.RS 4
Specify the superuser user name to use when disabling triggers\&. This is relevant only if
\fB\-\-disable\-triggers\fR
is used\&.
.RE
.PP
\fB\-t \fR\fB\fItable\fR\fR
.br
\fB\-\-table=\fR\fB\fItable\fR\fR
.RS 4
Restore definition and/or data of only the named table\&. For this purpose,
\(lqtable\(rq
includes views, materialized views, sequences, and foreign tables\&. Multiple tables can be selected by writing multiple
\fB\-t\fR
switches\&. This option can be combined with the
\fB\-n\fR
option to specify table(s) in a particular schema\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
When
\fB\-t\fR
is specified,
pg_restore
makes no attempt to restore any other database objects that the selected table(s) might depend upon\&. Therefore, there is no guarantee that a specific\-table restore into a clean database will succeed\&.
.sp .5v
.RE
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
This flag does not behave identically to the
\fB\-t\fR
flag of
pg_dump\&. There is not currently any provision for wild\-card matching in
pg_restore, nor can you include a schema name within its
\fB\-t\fR\&. And, while
pg_dump\*(Aqs
\fB\-t\fR
flag will also dump subsidiary objects (such as indexes) of the selected table(s),
pg_restore\*(Aqs
\fB\-t\fR
flag does not include such subsidiary objects\&.
.sp .5v
.RE
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
In versions prior to
PostgreSQL
9\&.6, this flag matched only tables, not any other type of relation\&.
.sp .5v
.RE
.RE
.PP
\fB\-T \fR\fB\fItrigger\fR\fR
.br
\fB\-\-trigger=\fR\fB\fItrigger\fR\fR
.RS 4
Restore named trigger only\&. Multiple triggers may be specified with multiple
\fB\-T\fR
switches\&.
.RE
.PP
\fB\-v\fR
.br
\fB\-\-verbose\fR
.RS 4
Specifies verbose mode\&. This will cause
pg_restore
to output detailed object comments and start/stop times to the output file, and progress messages to standard error\&. Repeating the option causes additional debug\-level messages to appear on standard error\&.
.RE
.PP
\fB\-V\fR
.br
\fB\-\-version\fR
.RS 4
Print the
pg_restore
version and exit\&.
.RE
.PP
\fB\-x\fR
.br
\fB\-\-no\-privileges\fR
.br
\fB\-\-no\-acl\fR
.RS 4
Prevent restoration of access privileges (grant/revoke commands)\&.
.RE
.PP
\fB\-1\fR
.br
\fB\-\-single\-transaction\fR
.RS 4
Execute the restore as a single transaction (that is, wrap the emitted commands in
\fBBEGIN\fR/\fBCOMMIT\fR)\&. This ensures that either all the commands complete successfully, or no changes are applied\&. This option implies
\fB\-\-exit\-on\-error\fR\&.
.RE
.PP
\fB\-\-disable\-triggers\fR
.RS 4
This option is relevant only when performing a restore without schema\&. It instructs
pg_restore
to execute commands to temporarily disable triggers on the target tables while the data is restored\&. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data restore\&.
.sp
Presently, the commands emitted for
\fB\-\-disable\-triggers\fR
must be done as superuser\&. So you should also specify a superuser name with
\fB\-S\fR
or, preferably, run
pg_restore
as a
PostgreSQL
superuser\&.
.RE
.PP
\fB\-\-enable\-row\-security\fR
.RS 4
This option is relevant only when restoring the contents of a table which has row security\&. By default,
pg_restore
will set
row_security
to off, to ensure that all data is restored in to the table\&. If the user does not have sufficient privileges to bypass row security, then an error is thrown\&. This parameter instructs
pg_restore
to set
row_security
to on instead, allowing the user to attempt to restore the contents of the table with row security enabled\&. This might still fail if the user does not have the right to insert the rows from the dump into the table\&.
.sp
Note that this option currently also requires the dump be in
\fBINSERT\fR
format, as
\fBCOPY FROM\fR
does not support row security\&.
.RE
.PP
\fB\-\-filter=\fR\fB\fIfilename\fR\fR
.RS 4
Specify a filename from which to read patterns for objects excluded or included from restore\&. The patterns are interpreted according to the same rules as
\fB\-n\fR/\fB\-\-schema\fR
for including objects in schemas,
\fB\-N\fR/\fB\-\-exclude\-schema\fR
for excluding objects in schemas,
\fB\-P\fR/\fB\-\-function\fR
for restoring named functions,
\fB\-I\fR/\fB\-\-index\fR
for restoring named indexes,
\fB\-t\fR/\fB\-\-table\fR
for restoring named tables or
\fB\-T\fR/\fB\-\-trigger\fR
for restoring triggers\&. To read from
STDIN, use
\-
as the filename\&. The
\fB\-\-filter\fR
option can be specified in conjunction with the above listed options for including or excluding objects, and can also be specified more than once for multiple filter files\&.
.sp
The file lists one database pattern per row, with the following format:
.sp
.if n \{\
.RS 4
.\}
.nf
{ include | exclude } { function | index | schema | table | trigger } \fIPATTERN\fR
.fi
.if n \{\
.RE
.\}
.sp
The first keyword specifies whether the objects matched by the pattern are to be included or excluded\&. The second keyword specifies the type of object to be filtered using the pattern:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
function: functions, works like the
\fB\-P\fR/\fB\-\-function\fR
option\&. This keyword can only be used with the
include
keyword\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
index: indexes, works like the
\fB\-I\fR/\fB\-\-indexes\fR
option\&. This keyword can only be used with the
include
keyword\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
schema: schemas, works like the
\fB\-n\fR/\fB\-\-schema\fR
and
\fB\-N\fR/\fB\-\-exclude\-schema\fR
options\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
table: tables, works like the
\fB\-t\fR/\fB\-\-table\fR
option\&. This keyword can only be used with the
include
keyword\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
trigger: triggers, works like the
\fB\-T\fR/\fB\-\-trigger\fR
option\&. This keyword can only be used with the
include
keyword\&.
.RE
.sp
Lines starting with
#
are considered comments and ignored\&. Comments can be placed after an object pattern row as well\&. Blank lines are also ignored\&. See
Patterns
for how to perform quoting in patterns\&.
.RE
.PP
\fB\-\-if\-exists\fR
.RS 4
Use
DROP \&.\&.\&. IF EXISTS
commands to drop objects in
\fB\-\-clean\fR
mode\&. This suppresses
\(lqdoes not exist\(rq
errors that might otherwise be reported\&. This option is not valid unless
\fB\-\-clean\fR
is also specified\&.
.RE
.PP
\fB\-\-no\-comments\fR
.RS 4
Do not output commands to restore comments, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-data\fR
.RS 4
Do not output commands to restore data, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-data\-for\-failed\-tables\fR
.RS 4
By default, table data is restored even if the creation command for the table failed (e\&.g\&., because it already exists)\&. With this option, data for such a table is skipped\&. This behavior is useful if the target database already contains the desired table contents\&. For example, auxiliary tables for
PostgreSQL
extensions such as
PostGIS
might already be loaded in the target database; specifying this option prevents duplicate or obsolete data from being loaded into them\&.
.sp
This option is effective only when restoring directly into a database, not when producing SQL script output\&.
.RE
.PP
\fB\-\-no\-policies\fR
.RS 4
Do not output commands to restore row security policies, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-publications\fR
.RS 4
Do not output commands to restore publications, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-schema\fR
.RS 4
Do not output commands to restore schema (data definitions), even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-security\-labels\fR
.RS 4
Do not output commands to restore security labels, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-statistics\fR
.RS 4
Do not output commands to restore statistics, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-subscriptions\fR
.RS 4
Do not output commands to restore subscriptions, even if the archive contains them\&.
.RE
.PP
\fB\-\-no\-table\-access\-method\fR
.RS 4
Do not output commands to select table access methods\&. With this option, all objects will be created with whichever table access method is the default during restore\&.
.RE
.PP
\fB\-\-no\-tablespaces\fR
.RS 4
Do not output commands to select tablespaces\&. With this option, all objects will be created in whichever tablespace is the default during restore\&.
.RE
.PP
\fB\-\-restrict\-key=\fR\fB\fIrestrict_key\fR\fR
.RS 4
Use the provided string as the
psql
\fB\erestrict\fR
key in the dump output\&. This can only be specified for SQL script output, i\&.e\&., when the
\fB\-\-file\fR
option is used\&. If no restrict key is specified,
pg_restore
will generate a random one as needed\&. Keys may contain only alphanumeric characters\&.
.sp
This option is primarily intended for testing purposes and other scenarios that require repeatable output (e\&.g\&., comparing dump files)\&. It is not recommended for general use, as a malicious server with advance knowledge of the key may be able to inject arbitrary code that will be executed on the machine that runs
psql
with the dump output\&.
.RE
.PP
\fB\-\-section=\fR\fB\fIsectionname\fR\fR
.RS 4
Only restore the named section\&. The section name can be
\fBpre\-data\fR,
\fBdata\fR, or
\fBpost\-data\fR\&. This option can be specified more than once to select multiple sections\&. The default is to restore all sections\&.
.sp
The data section contains actual table data as well as large\-object definitions\&. Post\-data items consist of definitions of indexes, triggers, rules and constraints other than validated check constraints\&. Pre\-data items consist of all other data definition items\&.
.RE
.PP
\fB\-\-statistics\fR
.RS 4
Output commands to restore statistics, if the archive contains them\&. This is the default\&.
.RE
.PP
\fB\-\-statistics\-only\fR
.RS 4
Restore only the statistics, not schema (data definitions) or data\&.
.RE
.PP
\fB\-\-strict\-names\fR
.RS 4
Require that each schema (\fB\-n\fR/\fB\-\-schema\fR) and table (\fB\-t\fR/\fB\-\-table\fR) qualifier match at least one schema/table in the file to be restored\&.
.RE
.PP
\fB\-\-transaction\-size=\fR\fB\fIN\fR\fR
.RS 4
Execute the restore as a series of transactions, each processing up to
\fIN\fR
database objects\&. This option implies
\fB\-\-exit\-on\-error\fR\&.
.sp
\fB\-\-transaction\-size\fR
offers an intermediate choice between the default behavior (one transaction per SQL command) and
\fB\-1\fR/\fB\-\-single\-transaction\fR
(one transaction for all restored objects)\&. While
\fB\-\-single\-transaction\fR
has the least overhead, it may be impractical for large databases because the transaction will take a lock on each restored object, possibly exhausting the server\*(Aqs lock table space\&. Using
\fB\-\-transaction\-size\fR
with a size of a few thousand objects offers nearly the same performance benefits while capping the amount of lock table space needed\&.
.RE
.PP
\fB\-\-use\-set\-session\-authorization\fR
.RS 4
Output SQL\-standard
\fBSET SESSION AUTHORIZATION\fR
commands instead of
\fBALTER OWNER\fR
commands to determine object ownership\&. This makes the dump more standards\-compatible, but depending on the history of the objects in the dump, might not restore properly\&.
.RE
.PP
\fB\-?\fR
.br
\fB\-\-help\fR
.RS 4
Show help about
pg_restore
command line arguments, and exit\&.
.RE
.PP
pg_restore
also accepts the following command line arguments for connection parameters:
.PP
\fB\-h \fR\fB\fIhost\fR\fR
.br
\fB\-\-host=\fR\fB\fIhost\fR\fR
.RS 4
Specifies the host name of the machine on which the server is running\&. If the value begins with a slash, it is used as the directory for the Unix domain socket\&. The default is taken from the
\fBPGHOST\fR
environment variable, if set, else a Unix domain socket connection is attempted\&.
.RE
.PP
\fB\-p \fR\fB\fIport\fR\fR
.br
\fB\-\-port=\fR\fB\fIport\fR\fR
.RS 4
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections\&. Defaults to the
\fBPGPORT\fR
environment variable, if set, or a compiled\-in default\&.
.RE
.PP
\fB\-U \fR\fB\fIusername\fR\fR
.br
\fB\-\-username=\fR\fB\fIusername\fR\fR
.RS 4
User name to connect as\&.
.RE
.PP
\fB\-w\fR
.br
\fB\-\-no\-password\fR
.RS 4
Never issue a password prompt\&. If the server requires password authentication and a password is not available by other means such as a
\&.pgpass
file, the connection attempt will fail\&. This option can be useful in batch jobs and scripts where no user is present to enter a password\&.
.RE
.PP
\fB\-W\fR
.br
\fB\-\-password\fR
.RS 4
Force
pg_restore
to prompt for a password before connecting to a database\&.
.sp
This option is never essential, since
pg_restore
will automatically prompt for a password if the server demands password authentication\&. However,
pg_restore
will waste a connection attempt finding out that the server wants a password\&. In some cases it is worth typing
\fB\-W\fR
to avoid the extra connection attempt\&.
.RE
.PP
\fB\-\-role=\fR\fB\fIrolename\fR\fR
.RS 4
Specifies a role name to be used to perform the restore\&. This option causes
pg_restore
to issue a
\fBSET ROLE\fR
\fIrolename\fR
command after connecting to the database\&. It is useful when the authenticated user (specified by
\fB\-U\fR) lacks privileges needed by
pg_restore, but can switch to a role with the required rights\&. Some installations have a policy against logging in directly as a superuser, and use of this option allows restores to be performed without violating the policy\&.
.RE
.SH "ENVIRONMENT"
.PP
\fBPGHOST\fR
.br
\fBPGOPTIONS\fR
.br
\fBPGPORT\fR
.br
\fBPGUSER\fR
.RS 4
Default connection parameters
.RE
.PP
\fBPG_COLOR\fR
.RS 4
Specifies whether to use color in diagnostic messages\&. Possible values are
always,
auto
and
never\&.
.RE
.PP
This utility, like most other
PostgreSQL
utilities, also uses the environment variables supported by
libpq
(see
Section\ \&32.15)\&. However, it does not read
\fBPGDATABASE\fR
when a database name is not supplied\&.
.SH "DIAGNOSTICS"
.PP
When a direct database connection is specified using the
\fB\-d\fR
option,
pg_restore
internally executes
SQL
statements\&. If you have problems running
pg_restore, make sure you are able to select information from the database using, for example,
\fBpsql\fR(1)\&. Also, any default connection settings and environment variables used by the
libpq
front\-end library will apply\&.
.SH "NOTES"
.PP
If your installation has any local additions to the
template1
database, be careful to load the output of
pg_restore
into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects\&. To make an empty database without any local additions, copy from
template0
not
template1, for example:
.sp
.if n \{\
.RS 4
.\}
.nf
CREATE DATABASE foo WITH TEMPLATE template0;
.fi
.if n \{\
.RE
.\}
.PP
The limitations of
pg_restore
are detailed below\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
When restoring data to a pre\-existing table and the option
\fB\-\-disable\-triggers\fR
is used,
pg_restore
emits commands to disable triggers on user tables before inserting the data, then emits commands to re\-enable them after the data has been inserted\&. If the restore is stopped in the middle, the system catalogs might be left in the wrong state\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
pg_restore
cannot restore large objects selectively; for instance, only those for a specific table\&. If an archive contains large objects, then all large objects will be restored, or none of them if they are excluded via
\fB\-L\fR,
\fB\-t\fR, or other options\&.
.RE
.PP
See also the
\fBpg_dump\fR(1)
documentation for details on limitations of
pg_dump\&.
.PP
By default,
\fBpg_restore\fR
will restore optimizer statistics if included in the dump file\&. If not all statistics were restored, it may be useful to run
\fBANALYZE\fR
on each restored table so the optimizer has useful statistics; see
Section\ \&24.1.3
and
Section\ \&24.1.6
for more information\&.
.SH "EXAMPLES"
.PP
Assume we have dumped a database called
mydb
into a custom\-format dump file:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBpg_dump \-Fc mydb > db\&.dump\fR
.fi
.if n \{\
.RE
.\}
.PP
To drop the database and recreate it from the dump:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBdropdb mydb\fR
$ \fBpg_restore \-C \-d postgres db\&.dump\fR
.fi
.if n \{\
.RE
.\}
.sp
The database named in the
\fB\-d\fR
switch can be any database existing in the cluster;
pg_restore
only uses it to issue the
\fBCREATE DATABASE\fR
command for
mydb\&. With
\fB\-C\fR, data is always restored into the database name that appears in the dump file\&.
.PP
To restore the dump into a new database called
newdb:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBcreatedb \-T template0 newdb\fR
$ \fBpg_restore \-d newdb db\&.dump\fR
.fi
.if n \{\
.RE
.\}
.sp
Notice we don\*(Aqt use
\fB\-C\fR, and instead connect directly to the database to be restored into\&. Also note that we clone the new database from
template0
not
template1, to ensure it is initially empty\&.
.PP
To reorder database items, it is first necessary to dump the table of contents of the archive:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBpg_restore \-l db\&.dump > db\&.list\fR
.fi
.if n \{\
.RE
.\}
.sp
The listing file consists of a header and one line for each item, e\&.g\&.:
.sp
.if n \{\
.RS 4
.\}
.nf
;
; Archive created at Mon Sep 14 13:55:39 2009
;     dbname: DBDEMOS
;     TOC Entries: 81
;     Compression: 9
;     Dump Version: 1\&.10\-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8\&.3\&.5
;     Dumped by pg_dump version: 8\&.3\&.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA \- public pasha
1861; 0 0 COMMENT \- SCHEMA public pasha
1862; 0 0 ACL \- public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
.fi
.if n \{\
.RE
.\}
.sp
Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item\&.
.PP
Lines in the file can be commented out, deleted, and reordered\&. For example:
.sp
.if n \{\
.RS 4
.\}
.nf
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
.fi
.if n \{\
.RE
.\}
.sp
could be used as input to
pg_restore
and would only restore items 10 and 6, in that order:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBpg_restore \-L db\&.list db\&.dump\fR
.fi
.if n \{\
.RE
.\}
.SH "SEE ALSO"
\fBpg_dump\fR(1), \fBpg_dumpall\fR(1), \fBpsql\fR(1)