diff options
Diffstat (limited to 'doc/src/sgml/man1/pg_restore.1')
| -rw-r--r-- | doc/src/sgml/man1/pg_restore.1 | 879 |
1 files changed, 879 insertions, 0 deletions
diff --git a/doc/src/sgml/man1/pg_restore.1 b/doc/src/sgml/man1/pg_restore.1 new file mode 100644 index 0000000..b344310 --- /dev/null +++ b/doc/src/sgml/man1/pg_restore.1 @@ -0,0 +1,879 @@ +'\" t +.\" Title: pg_restore +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "PG_RESTORE" "1" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 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\&. +.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)\&. 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 is the inverse of +\fB\-\-data\-only\fR\&. It is similar to, but for historical reasons not identical to, specifying +\fB\-\-section=pre\-data \-\-section=post\-data\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 data\-only restore\&. 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\-\-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\-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\-publications\fR +.RS 4 +Do not output commands to restore publications, 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\-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 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\-\-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\-\-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 backup file\&. +.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\ \&34.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 +Once restored, it is wise to run +\fBANALYZE\fR +on each restored table so the optimizer has useful statistics; see +Section\ \&25.1.3 +and +Section\ \&25.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) |