| <!-- |
| doc/src/sgml/ref/pg_restore.sgml |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="app-pgrestore"> |
| <indexterm zone="app-pgrestore"> |
| <primary>pg_restore</primary> |
| </indexterm> |
| |
| <refmeta> |
| <refentrytitle><application>pg_restore</application></refentrytitle> |
| <manvolnum>1</manvolnum> |
| <refmiscinfo>Application</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>pg_restore</refname> |
| |
| <refpurpose> |
| restore a <productname>PostgreSQL</productname> database from an |
| archive file created by <application>pg_dump</application> |
| </refpurpose> |
| </refnamediv> |
| |
| <refsynopsisdiv> |
| <cmdsynopsis> |
| <command>pg_restore</command> |
| <arg rep="repeat"><replaceable>connection-option</replaceable></arg> |
| <arg rep="repeat"><replaceable>option</replaceable></arg> |
| <arg choice="opt"><replaceable>filename</replaceable></arg> |
| </cmdsynopsis> |
| </refsynopsisdiv> |
| |
| |
| <refsect1 id="app-pgrestore-description"> |
| <title>Description</title> |
| |
| <para> |
| <application>pg_restore</application> is a utility for restoring a |
| <productname>PostgreSQL</productname> database from an archive |
| created by <xref linkend="app-pgdump"/> 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 <application>pg_restore</application> 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. |
| </para> |
| |
| <para> |
| <application>pg_restore</application> can operate in two modes. |
| If a database name is specified, <application>pg_restore</application> |
| 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 <application>pg_dump</application>. |
| Some of the options controlling the output are therefore analogous to |
| <application>pg_dump</application> options. |
| </para> |
| |
| <para> |
| Obviously, <application>pg_restore</application> cannot restore information |
| that is not present in the archive file. For instance, if the |
| archive was made using the <quote>dump data as |
| <command>INSERT</command> commands</quote> option, |
| <application>pg_restore</application> will not be able to load the data |
| using <command>COPY</command> statements. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="app-pgrestore-options"> |
| <title>Options</title> |
| |
| <para> |
| <application>pg_restore</application> accepts the following command |
| line arguments. |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">filename</replaceable></term> |
| <listitem> |
| <para> |
| 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-a</option></term> |
| <term><option>--data-only</option></term> |
| <listitem> |
| <para> |
| Restore only the data, not the schema (data definitions). |
| Table data, large objects, and sequence values are restored, |
| if present in the archive. |
| </para> |
| |
| <para> |
| This option is similar to, but for historical reasons not identical |
| to, specifying <option>--section=data</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-c</option></term> |
| <term><option>--clean</option></term> |
| <listitem> |
| <para> |
| Clean (drop) database objects before recreating them. |
| (Unless <option>--if-exists</option> is used, |
| this might generate some harmless error messages, if any objects |
| were not present in the destination database.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-C</option></term> |
| <term><option>--create</option></term> |
| <listitem> |
| <para> |
| Create the database before restoring into it. |
| If <option>--clean</option> is also specified, drop and |
| recreate the target database before connecting to it. |
| </para> |
| |
| <para> |
| With <option>--create</option>, <application>pg_restore</application> |
| also restores the database's comment if any, and any configuration |
| variable settings that are specific to this database, that is, |
| any <command>ALTER DATABASE ... SET ...</command> |
| and <command>ALTER ROLE ... IN DATABASE ... SET ...</command> |
| commands that mention this database. |
| Access privileges for the database itself are also restored, |
| unless <option>--no-acl</option> is specified. |
| </para> |
| |
| <para> |
| When this option is used, the database named with <option>-d</option> |
| is used only to issue the initial <command>DROP DATABASE</command> and |
| <command>CREATE DATABASE</command> commands. All data is restored into the |
| database name that appears in the archive. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term> |
| <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term> |
| <listitem> |
| <para> |
| Connect to database <replaceable |
| class="parameter">dbname</replaceable> and restore directly |
| into the database. The <replaceable>dbname</replaceable> can |
| be a <link linkend="libpq-connstring">connection string</link>. |
| If so, connection string parameters will override any conflicting |
| command line options. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-e</option></term> |
| <term><option>--exit-on-error</option></term> |
| <listitem> |
| <para> |
| 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-f <replaceable>filename</replaceable></option></term> |
| <term><option>--file=<replaceable>filename</replaceable></option></term> |
| <listitem> |
| <para> |
| Specify output file for generated script, or for the listing |
| when used with <option>-l</option>. Use <literal>-</literal> |
| for <systemitem>stdout</systemitem>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-F <replaceable class="parameter">format</replaceable></option></term> |
| <term><option>--format=<replaceable class="parameter">format</replaceable></option></term> |
| <listitem> |
| <para> |
| Specify format of the archive. It is not necessary to specify |
| the format, since <application>pg_restore</application> will |
| determine the format automatically. If specified, it can be |
| one of the following: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>c</literal></term> |
| <term><literal>custom</literal></term> |
| <listitem> |
| <para> |
| The archive is in the custom format of |
| <application>pg_dump</application>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>d</literal></term> |
| <term><literal>directory</literal></term> |
| <listitem> |
| <para> |
| The archive is a directory archive. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>t</literal></term> |
| <term><literal>tar</literal></term> |
| <listitem> |
| <para> |
| The archive is a <command>tar</command> archive. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist></para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-I <replaceable class="parameter">index</replaceable></option></term> |
| <term><option>--index=<replaceable class="parameter">index</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore definition of named index only. Multiple indexes |
| may be specified with multiple <option>-I</option> switches. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-j <replaceable class="parameter">number-of-jobs</replaceable></option></term> |
| <term><option>--jobs=<replaceable class="parameter">number-of-jobs</replaceable></option></term> |
| <listitem> |
| <para> |
| Run the most time-consuming steps |
| of <application>pg_restore</application> — those that load data, |
| create indexes, or create constraints — concurrently, using up |
| to <replaceable class="parameter">number-of-jobs</replaceable> |
| 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. |
| </para> |
| |
| <para> |
| Each job is one process or one thread, depending on the |
| operating system, and uses a separate connection to the |
| server. |
| </para> |
| |
| <para> |
| 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. |
| </para> |
| |
| <para> |
| 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 <option>--single-transaction</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-l</option></term> |
| <term><option>--list</option></term> |
| <listitem> |
| <para> |
| List the table of contents of the archive. The output of this operation |
| can be used as input to the <option>-L</option> option. Note that |
| if filtering switches such as <option>-n</option> or <option>-t</option> are |
| used with <option>-l</option>, they will restrict the items listed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-L <replaceable class="parameter">list-file</replaceable></option></term> |
| <term><option>--use-list=<replaceable class="parameter">list-file</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore only those archive elements that are listed in <replaceable |
| class="parameter">list-file</replaceable>, and restore them in the |
| order they appear in the file. Note that |
| if filtering switches such as <option>-n</option> or <option>-t</option> are |
| used with <option>-L</option>, they will further restrict the items restored. |
| </para> |
| <para><replaceable class="parameter">list-file</replaceable> is normally created by |
| editing the output of a previous <option>-l</option> operation. |
| Lines can be moved or removed, and can also |
| be commented out by placing a semicolon (<literal>;</literal>) at the |
| start of the line. See below for examples. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> |
| <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore only objects that are in the named schema. Multiple schemas |
| may be specified with multiple <option>-n</option> switches. This can be |
| combined with the <option>-t</option> option to restore just a |
| specific table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> |
| <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> |
| <listitem> |
| <para> |
| Do not restore objects that are in the named schema. Multiple schemas |
| to be excluded may be specified with multiple <option>-N</option> switches. |
| </para> |
| |
| <para> |
| When both <option>-n</option> and <option>-N</option> are given for the same |
| schema name, the <option>-N</option> switch wins and the schema is excluded. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-O</option></term> |
| <term><option>--no-owner</option></term> |
| <listitem> |
| <para> |
| Do not output commands to set |
| ownership of objects to match the original database. |
| By default, <application>pg_restore</application> issues |
| <command>ALTER OWNER</command> or |
| <command>SET SESSION AUTHORIZATION</command> |
| 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 <option>-O</option>, any user name can be used for the |
| initial connection, and this user will own all the created objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-P <replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term> |
| <term><option>--function=<replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore the named function only. Be careful to spell the function |
| name and arguments exactly as they appear in the dump file's table |
| of contents. Multiple functions may be specified with multiple |
| <option>-P</option> switches. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-R</option></term> |
| <term><option>--no-reconnect</option></term> |
| <listitem> |
| <para> |
| This option is obsolete but still accepted for backwards |
| compatibility. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-s</option></term> |
| <term><option>--schema-only</option></term> |
| <listitem> |
| <para> |
| Restore only the schema (data definitions), not data, |
| to the extent that schema entries are present in the archive. |
| </para> |
| <para> |
| This option is the inverse of <option>--data-only</option>. |
| It is similar to, but for historical reasons not identical to, |
| specifying |
| <option>--section=pre-data --section=post-data</option>. |
| </para> |
| <para> |
| (Do not confuse this with the <option>--schema</option> option, which |
| uses the word <quote>schema</quote> in a different meaning.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-S <replaceable class="parameter">username</replaceable></option></term> |
| <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term> |
| <listitem> |
| <para> |
| Specify the superuser user name to use when disabling triggers. |
| This is relevant only if <option>--disable-triggers</option> is used. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-t <replaceable class="parameter">table</replaceable></option></term> |
| <term><option>--table=<replaceable class="parameter">table</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore definition and/or data of only the named table. |
| For this purpose, <quote>table</quote> includes views, materialized views, |
| sequences, and foreign tables. Multiple tables |
| can be selected by writing multiple <option>-t</option> switches. |
| This option can be combined with the <option>-n</option> option to |
| specify table(s) in a particular schema. |
| </para> |
| |
| <note> |
| <para> |
| When <option>-t</option> is specified, <application>pg_restore</application> |
| 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. |
| </para> |
| </note> |
| |
| <note> |
| <para> |
| This flag does not behave identically to the <option>-t</option> |
| flag of <application>pg_dump</application>. There is not currently |
| any provision for wild-card matching in <application>pg_restore</application>, |
| nor can you include a schema name within its <option>-t</option>. |
| And, while <application>pg_dump</application>'s <option>-t</option> |
| flag will also dump subsidiary objects (such as indexes) of the |
| selected table(s), |
| <application>pg_restore</application>'s <option>-t</option> |
| flag does not include such subsidiary objects. |
| </para> |
| </note> |
| |
| <note> |
| <para> |
| In versions prior to <productname>PostgreSQL</productname> 9.6, this flag |
| matched only tables, not any other type of relation. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-T <replaceable class="parameter">trigger</replaceable></option></term> |
| <term><option>--trigger=<replaceable class="parameter">trigger</replaceable></option></term> |
| <listitem> |
| <para> |
| Restore named trigger only. Multiple triggers may be specified with |
| multiple <option>-T</option> switches. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-v</option></term> |
| <term><option>--verbose</option></term> |
| <listitem> |
| <para> |
| Specifies verbose mode. This will cause |
| <application>pg_restore</application> 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-V</option></term> |
| <term><option>--version</option></term> |
| <listitem> |
| <para> |
| Print the <application>pg_restore</application> version and exit. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-x</option></term> |
| <term><option>--no-privileges</option></term> |
| <term><option>--no-acl</option></term> |
| <listitem> |
| <para> |
| Prevent restoration of access privileges (grant/revoke commands). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-1</option></term> |
| <term><option>--single-transaction</option></term> |
| <listitem> |
| <para> |
| Execute the restore as a single transaction (that is, wrap the |
| emitted commands in <command>BEGIN</command>/<command>COMMIT</command>). This |
| ensures that either all the commands complete successfully, or no |
| changes are applied. This option implies |
| <option>--exit-on-error</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--disable-triggers</option></term> |
| <listitem> |
| <para> |
| This option is relevant only when performing a data-only restore. |
| It instructs <application>pg_restore</application> 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. |
| </para> |
| |
| <para> |
| Presently, the commands emitted for |
| <option>--disable-triggers</option> must be done as superuser. So you |
| should also specify a superuser name with <option>-S</option> or, |
| preferably, run <application>pg_restore</application> as a |
| <productname>PostgreSQL</productname> superuser. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--enable-row-security</option></term> |
| <listitem> |
| <para> |
| This option is relevant only when restoring the contents of a table |
| which has row security. By default, <application>pg_restore</application> will set |
| <xref linkend="guc-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 <application>pg_restore</application> to set |
| <xref linkend="guc-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. |
| </para> |
| |
| <para> |
| Note that this option currently also requires the dump be in <command>INSERT</command> |
| format, as <command>COPY FROM</command> does not support row security. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--if-exists</option></term> |
| <listitem> |
| <para> |
| Use conditional commands (i.e., add an <literal>IF EXISTS</literal> |
| clause) to drop database objects. This option is not valid |
| unless <option>--clean</option> is also specified. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-comments</option></term> |
| <listitem> |
| <para> |
| Do not output commands to restore comments, even if the archive |
| contains them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-data-for-failed-tables</option></term> |
| <listitem> |
| <para> |
| 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 <productname>PostgreSQL</productname> extensions |
| such as <productname>PostGIS</productname> might already be loaded in |
| the target database; specifying this option prevents duplicate |
| or obsolete data from being loaded into them. |
| </para> |
| |
| <para> |
| This option is effective only when restoring directly into a |
| database, not when producing SQL script output. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-publications</option></term> |
| <listitem> |
| <para> |
| Do not output commands to restore publications, even if the archive |
| contains them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-security-labels</option></term> |
| <listitem> |
| <para> |
| Do not output commands to restore security labels, |
| even if the archive contains them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-subscriptions</option></term> |
| <listitem> |
| <para> |
| Do not output commands to restore subscriptions, even if the archive |
| contains them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--no-tablespaces</option></term> |
| <listitem> |
| <para> |
| Do not output commands to select tablespaces. |
| With this option, all objects will be created in whichever |
| tablespace is the default during restore. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--section=<replaceable class="parameter">sectionname</replaceable></option></term> |
| <listitem> |
| <para> |
| Only restore the named section. The section name can be |
| <option>pre-data</option>, <option>data</option>, or <option>post-data</option>. |
| This option can be specified more than once to select multiple |
| sections. The default is to restore all sections. |
| </para> |
| <para> |
| 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--strict-names</option></term> |
| <listitem> |
| <para> |
| Require that each schema |
| (<option>-n</option>/<option>--schema</option>) and table |
| (<option>-t</option>/<option>--table</option>) qualifier match at |
| least one schema/table in the backup file. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--use-set-session-authorization</option></term> |
| <listitem> |
| <para> |
| Output SQL-standard <command>SET SESSION AUTHORIZATION</command> commands |
| instead of <command>ALTER OWNER</command> 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-?</option></term> |
| <term><option>--help</option></term> |
| <listitem> |
| <para> |
| Show help about <application>pg_restore</application> command line |
| arguments, and exit. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| |
| <para> |
| <application>pg_restore</application> also accepts |
| the following command line arguments for connection parameters: |
| |
| <variablelist> |
| <varlistentry> |
| <term><option>-h <replaceable class="parameter">host</replaceable></option></term> |
| <term><option>--host=<replaceable class="parameter">host</replaceable></option></term> |
| <listitem> |
| <para> |
| 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 <envar>PGHOST</envar> environment variable, if set, |
| else a Unix domain socket connection is attempted. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-p <replaceable class="parameter">port</replaceable></option></term> |
| <term><option>--port=<replaceable class="parameter">port</replaceable></option></term> |
| <listitem> |
| <para> |
| Specifies the TCP port or local Unix domain socket file |
| extension on which the server is listening for connections. |
| Defaults to the <envar>PGPORT</envar> environment variable, if |
| set, or a compiled-in default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-U <replaceable>username</replaceable></option></term> |
| <term><option>--username=<replaceable class="parameter">username</replaceable></option></term> |
| <listitem> |
| <para> |
| User name to connect as. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-w</option></term> |
| <term><option>--no-password</option></term> |
| <listitem> |
| <para> |
| Never issue a password prompt. If the server requires |
| password authentication and a password is not available by |
| other means such as a <filename>.pgpass</filename> 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-W</option></term> |
| <term><option>--password</option></term> |
| <listitem> |
| <para> |
| Force <application>pg_restore</application> to prompt for a |
| password before connecting to a database. |
| </para> |
| |
| <para> |
| This option is never essential, since |
| <application>pg_restore</application> will automatically prompt |
| for a password if the server demands password authentication. |
| However, <application>pg_restore</application> will waste a |
| connection attempt finding out that the server wants a password. |
| In some cases it is worth typing <option>-W</option> to avoid the extra |
| connection attempt. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term> |
| <listitem> |
| <para> |
| Specifies a role name to be used to perform the restore. |
| This option causes <application>pg_restore</application> to issue a |
| <command>SET ROLE</command> <replaceable class="parameter">rolename</replaceable> |
| command after connecting to the database. It is useful when the |
| authenticated user (specified by <option>-U</option>) lacks privileges |
| needed by <application>pg_restore</application>, 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Environment</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><envar>PGHOST</envar></term> |
| <term><envar>PGOPTIONS</envar></term> |
| <term><envar>PGPORT</envar></term> |
| <term><envar>PGUSER</envar></term> |
| |
| <listitem> |
| <para> |
| Default connection parameters |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>PG_COLOR</envar></term> |
| <listitem> |
| <para> |
| Specifies whether to use color in diagnostic messages. Possible values |
| are <literal>always</literal>, <literal>auto</literal> and |
| <literal>never</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| This utility, like most other <productname>PostgreSQL</productname> utilities, |
| also uses the environment variables supported by <application>libpq</application> |
| (see <xref linkend="libpq-envars"/>). However, it does not read |
| <envar>PGDATABASE</envar> when a database name is not supplied. |
| </para> |
| |
| </refsect1> |
| |
| |
| <refsect1 id="app-pgrestore-diagnostics"> |
| <title>Diagnostics</title> |
| |
| <para> |
| When a direct database connection is specified using the |
| <option>-d</option> option, <application>pg_restore</application> |
| internally executes <acronym>SQL</acronym> statements. If you have |
| problems running <application>pg_restore</application>, make sure |
| you are able to select information from the database using, for |
| example, <xref linkend="app-psql"/>. Also, any default connection |
| settings and environment variables used by the |
| <application>libpq</application> front-end library will apply. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1 id="app-pgrestore-notes"> |
| <title>Notes</title> |
| |
| <para> |
| If your installation has any local additions to the |
| <literal>template1</literal> database, be careful to load the output of |
| <application>pg_restore</application> 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 <literal>template0</literal> not <literal>template1</literal>, for example: |
| <programlisting> |
| CREATE DATABASE foo WITH TEMPLATE template0; |
| </programlisting> |
| </para> |
| |
| <para> |
| The limitations of <application>pg_restore</application> are detailed below. |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| When restoring data to a pre-existing table and the option |
| <option>--disable-triggers</option> is used, |
| <application>pg_restore</application> 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. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para><application>pg_restore</application> 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 <option>-L</option>, |
| <option>-t</option>, or other options. |
| </para> |
| </listitem> |
| |
| </itemizedlist> |
| </para> |
| |
| <para> |
| See also the <xref linkend="app-pgdump"/> documentation for details on |
| limitations of <application>pg_dump</application>. |
| </para> |
| |
| <para> |
| Once restored, it is wise to run <command>ANALYZE</command> on each |
| restored table so the optimizer has useful statistics; see |
| <xref linkend="vacuum-for-statistics"/> and |
| <xref linkend="autovacuum"/> for more information. |
| </para> |
| |
| </refsect1> |
| |
| |
| <refsect1 id="app-pgrestore-examples"> |
| <title>Examples</title> |
| |
| <para> |
| Assume we have dumped a database called <literal>mydb</literal> into a |
| custom-format dump file: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -Fc mydb > db.dump</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To drop the database and recreate it from the dump: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>dropdb mydb</userinput> |
| <prompt>$</prompt> <userinput>pg_restore -C -d postgres db.dump</userinput> |
| </screen> |
| |
| The database named in the <option>-d</option> switch can be any database existing |
| in the cluster; <application>pg_restore</application> only uses it to issue the |
| <command>CREATE DATABASE</command> command for <literal>mydb</literal>. With |
| <option>-C</option>, data is always restored into the database name that appears |
| in the dump file. |
| </para> |
| |
| <para> |
| To restore the dump into a new database called <literal>newdb</literal>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>createdb -T template0 newdb</userinput> |
| <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput> |
| </screen> |
| |
| Notice we don't use <option>-C</option>, and instead connect directly to the |
| database to be restored into. Also note that we clone the new database |
| from <literal>template0</literal> not <literal>template1</literal>, to ensure it is |
| initially empty. |
| </para> |
| |
| <para> |
| To reorder database items, it is first necessary to dump the table of |
| contents of the archive: |
| <screen> |
| <prompt>$</prompt> <userinput>pg_restore -l db.dump > db.list</userinput> |
| </screen> |
| The listing file consists of a header and one line for each item, e.g.: |
| <programlisting> |
| ; |
| ; 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 |
| </programlisting> |
| Semicolons start a comment, and the numbers at the start of lines refer to the |
| internal archive ID assigned to each item. |
| </para> |
| |
| <para> |
| Lines in the file can be commented out, deleted, and reordered. For example: |
| <programlisting> |
| 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 |
| </programlisting> |
| could be used as input to <application>pg_restore</application> and would only restore |
| items 10 and 6, in that order: |
| <screen> |
| <prompt>$</prompt> <userinput>pg_restore -L db.list db.dump</userinput> |
| </screen></para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="app-pgdump"/></member> |
| <member><xref linkend="app-pg-dumpall"/></member> |
| <member><xref linkend="app-psql"/></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |
