First pass at docs for pg_restore

1 files changed, 578 insertions, 0 deletions

diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
new file mode 100644
index 00000000000..b8e9e48fb22
--- /dev/null
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -0,0 +1,578 @@
+<refentry id="APP-PGRESTORE">
+ <refmeta>
+  <refentrytitle id="app-pgrestore-title">
+   <application>pg_restore</application>
+  </refentrytitle>
+  <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   <application>pg_restore</application>
+  </refname>
+  <refpurpose>
+   Restore a <PRODUCTNAME>Postgres</PRODUCTNAME> database from an archive file created by 

+<APPLICATION>pg_dump</APPLICATION>

+  </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>2000-10-11</date>
+  </refsynopsisdivinfo>
+  <synopsis>

+pg_restore [ <replaceable class="parameter">archive-file</replaceable>  ] 

+    [ -h  <replaceable class="parameter">host</replaceable>  ] 

+    [ -p  <replaceable class="parameter">port</replaceable>  ]     

+    [ -t  <replaceable class="parameter">table</replaceable>  ]     

+    [ -a  ] [ -c  ] [-C] [-d <name>] 

+    [-f <replaceable class="parameter">archive-file</replaceable>] 

+    [-F <replaceable class="parameter">format</replaceable>] 

+    [ -i  <replaceable class="parameter">index</replaceable>  ] 

+    [ -l ] [ -N  ] [ -o  ] [ -O ] 

+    [ -P <replaceable class="parameter">function-name</replaceable> ] [ -r ] [ -R ] 

+    [ -s  ] [ -S ] { -T  <replaceable class="parameter">trigger</replaceable> ] [ -u  ] 

+    [-U <replaceable class="parameter">contents-file</replaceable> ] [ -v  ] [ -x ] 

+  </synopsis>
+
+  <refsect2 id="R2-APP-PG-RESTORE-1">
+   <refsect2info>
+    <date>2000-10-11</date>
+   </refsect2info>
+   <title>
+    Inputs
+   </title>
+   <para>
+    <application>pg_restore</application> accepts the following command
+    line arguments:
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">archive-name</replaceable></term>
+      <listitem>
+       <para>
+       Specifies the location of the archive file to be restored.

+       If not specified, and no '-f' option is specified, then STDIN is used.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>-a</term>
+      <listitem>
+       <para>
+	Restore only the data, no schema (definitions).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>

+      <term>-c</term>

+      <listitem>

+       <para>

+	Clean (drop) schema prior to create.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-C</term>

+      <listitem>

+       <para>

+	Include SQL to create the schema.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>
+      <term>-d <replaceable class="parameter">dbname</replaceable></term>
+      <listitem>
+       <para>
+        Connect to database <replaceable class="parameter">dbname</replaceable> and restore 

+        directly into the database. BLOBs can only be restored by using a direct database connection.

+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>

+      <term>-f</term>

+      <listitem>

+       <para>

+	  Specify output file for generated script. Default is STDOUT.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-F <replaceable class="parameter">format</replaceable></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:

+       </para>

+

+       <variablelist>

+

+        <varlistentry>

+         <term>t</term>

+         <listitem>

+          <para>

+           archive is a TAR archive. Using this archive format allows reordering and/or 

+           exclusion of schema elements at the time the database is restored. It is also possible to limit which 

+           data is reloaded at restore time.

+          </para>

+         </listitem>

+        </varlistentry>

+

+        <varlistentry>

+         <term>c</term>

+         <listitem>

+          <para>

+           archive is in the custom format from pg_dump. This is the most flexible format 

+           in that it allows reordering of data load as well as schema elements. 

+           This format is also compressed by default.

+          </para>

+         </listitem>

+        </varlistentry>

+

+       </variablelist>

+

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-i <replaceable class="parameter">index</replaceable></term>

+      <listitem>

+       <para>

+	Restore definition for named <replaceable class="parameter">index</replaceable> only.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-l</term>

+      <listitem>

+       <para>

+        List the contents of the archive. The output of this command can be used with the '-U, --use-list' option

+        to restrict and reorder the items that are restored.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>
+      <term>-N</term>
+      <listitem>
+       <para>

+        Restore items in the original dump order. By default pg_dump will dump items in an order convenient 

+        to pg_dump, then save the archive in a modified OID order. This option overrides the OID ordering.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>-o</term>
+      <listitem>
+       <para>
+        Restore items in the OID order. By default pg_dump will dump items in an order convenient 

+        to pg_dump, then save the archive in a modified OID order. This option enforces strict OID ordering.

+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>-O</term>
+      <listitem>
+       <para>
+        Prevent any attempt to restore original object ownership. Objects will be owned by the username used

+        to attach to the database.

+       </para>
+      </listitem>
+     </varlistentry>
+

+     <varlistentry>

+      <term>-P <replaceable class="parameter">procedure-name</replaceable></term>

+      <listitem>

+       <para>

+        Specify a procedure or function to be restored.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-r</term>

+      <listitem>

+       <para>

+        Restore items in modified OID order. By default pg_dump will dump items in an order convenient 

+        to pg_dump, then save the archive in a modified OID order. Most objects

+        will be restored in OID order, but some things (eg. RULES & INDEXES) will be restored at the end of

+        the process irrespective of their OIDs. This option is the default. 

+       </para>

+      </listitem>

+     </varlistentry>

+
+     <varlistentry>

+      <term>-R</term>

+      <listitem>

+       <para>

+	  Prohibit <APPLICATION>pg_restore</APPLICATION> from issuing any <PROGRAMLISTING>\connect</PROGRAMLISTING> 

+        statements or reconnecting to the database if directly connected.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-s</term>

+      <listitem>

+       <para>

+	Restore the schema (definitions), no data. Sequence values will be reset.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-S <replaceable class="parameter">username</replaceable></term>

+      <listitem>

+       <para>

+        Specify the superuser username to use when disabling triggers and/or setting ownership of schema elements.

+        By default, <APPLICATION>pg_restore</APPLICATION> will use the current username if it is a superuser.

+       </para>

+      </listitem>

+     </varlistentry>

+
+     <varlistentry>

+      <term>-t <replaceable class="parameter">table</replaceable></term>

+      <listitem>

+       <para>

+	Restore schema/data for <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> only.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>

+      <term>-T <replaceable class="parameter">trigger</replaceable></term>

+      <listitem>

+       <para>

+	  Restore definition of <REPLACEABLE CLASS="PARAMETER">trigger</REPLACEABLE> only.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>
+      <term>-u</term>
+      <listitem>
+       <para>
+	Use password authentication. Prompts for username and password.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>

+      <term>-U <replaceable class="parameter">list-file</replaceable></term>

+      <listitem>

+       <para>

+	  Restore elements in <REPLACEABLE CLASS="PARAMETER">list-file</REPLACEABLE> only, and in the

+        order they appear in the file. Lines can be moved and may also be commented out by placing a ';' at the 

+        start of the line.

+       </para>

+      </listitem>

+     </varlistentry>

+

+     <varlistentry>
+      <term>-v</term>
+      <listitem>
+       <para>
+	Specifies verbose mode.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>-x</term>
+      <listitem>
+       <para>
+	Prevent restoration of ACLs (grant/revoke commands).
+       </para>
+      </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+   <para>
+    <application>pg_restore</application> also accepts 
+    the following command line arguments for connection parameters:
+
+    <variablelist>
+     <varlistentry>
+      <term>-h <replaceable class="parameter">host</replaceable></term>
+      <listitem>
+       <para>
+	Specifies the hostname of the machine on which the 
+	<application>postmaster</application>
+	is running.  Defaults to using a local Unix domain socket
+	rather than an IP connection.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>-p <replaceable class="parameter">port</replaceable></term>
+      <listitem>
+       <para>
+	Specifies the Internet TCP/IP port or local Unix domain socket file 
+	extension on which the <application>postmaster</application>
+	is listening for connections.  The port number defaults to 5432,
+	or the value of the <envar>PGPORT</envar>
+	environment variable (if set).
+       </para>
+      </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-APP-PG-RESTORE-2">
+   <refsect2info>
+    <date>2000-10-11</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+    <application>pg_restore</application> will create a script file,
+    write to <filename>stdout</filename>, or restore a database directly.
+
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+Connection to database 'template1' failed.
+connectDB() failed: Is the postmaster running and accepting connections
+            at 'UNIX Socket' on port '<replaceable class="parameter">port</replaceable>'?
+       </computeroutput></term>
+      <listitem>
+       <para>
+	<application>pg_restore</application> could not attach to the 
+	<application>postmaster</application> 
+	process on the specified host and port.  If you see this message,
+	ensure that the <application>postmaster</application> 
+	is running on the proper host and that you have specified the proper
+	port.  If your site uses an authentication system, ensure that you
+	have obtained the required authentication credentials.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><computeroutput>
+Connection to database '<replaceable class="parameter">dbname</replaceable>' failed.
+FATAL 1:  SetUserId: user '<replaceable class="parameter">username</replaceable>' is not in 'pg_shadow'
+       </computeroutput></term>
+      <listitem>
+       <para>
+	You do not have a valid entry in the relation <literal>pg_shadow</literal>
+	and and will not be allowed to access <productname>Postgres</productname>. 
+	Contact your <productname>Postgres</productname> administrator.
+       </para>
+      </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+
+   <note>
+    <para>
+     When a direct database connection is specified using the -d option, <application>pg_restore</application> 

+     internally executes  <command>SQL</command> statements. If you  have problems running 

+     <application>pg_restore</application>,
+     make sure you are able to select information from the database using, for
+     example, <application>psql</application>.
+    </para>
+   </note>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-APP-PG-RESTORE-1">
+  <refsect1info>
+   <date>2000-10-11</date>
+  </refsect1info>
+  <title>
+   Description
+  </title>
+  <para>
+   <application>pg_restore</application> is a utility for restoring a

+      <productname>Postgres</productname> database dumped by <application>pg_dump</application>

+      from any one of the non-plain-text output formats.

+  </para>
+
+  <para>
+   The archive files, new with this relase, contain enough information for 

+   <application>pg_restore</application> to rebuild the database, but 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 should 

+   also be portable across architectures. <application>pg_dump</application> will 

+   produce the queries necessary to re-generate all user-defined types, functions, 

+   tables, indices, aggregates, and operators.  In addition, all the data is copied 

+   out (in text format for scripts) so that it can be readily copied in again.

+  </para>
+

+  <para>

+   <application>pg_restore</application> reads the archive file and outputs the appropriate 

+   SQL in the required order based on the command parameters. Obviously, it can not restore 

+   information that is not present in the dump file; so if the dump is made using the 

+   'dump data as inserts' option, <application>pg_restore</application> will not be able to

+   load the data using <command>COPY</command> statements.

+  </para>

+

+  <para>

+   The most flexible output file format is the new 'custom' format (-Fc). It allows for 

+   selection and reordering of all archived items, and is compressed by default. The TAR 

+   format (-Ft) is not compressed and it is not possible to reorder

+   data load, but it is otherwise quite flexible.

+  </para>

+

+  <para>

+   To reorder the items, it is first necessary to dump the contents of the archive:

+   <programlisting>

+    $ pg_restore acrhive.file --list > archive.lis    

+   </programlisting>

+   This file consists of a header and one line for each item, eg.

+   <programlisting>

+;

+; Archive created at Fri Jul 28 22:28:36 2000

+;     dbname: birds

+;     TOC Entries: 74

+;     Compression: 0

+;     Dump Version: 1.4-0

+;     Format: CUSTOM

+;

+;

+; Selected TOC Entries:

+;

+2; 145344 TABLE species postgres

+3; 145344 ACL species

+4; 145359 TABLE nt_header postgres

+5; 145359 ACL nt_header

+6; 145402 TABLE species_records postgres

+7; 145402 ACL species_records

+8; 145416 TABLE ss_old postgres

+9; 145416 ACL ss_old

+10; 145433 TABLE map_resolutions postgres

+11; 145433 ACL map_resolutions

+12; 145443 TABLE hs_old postgres

+13; 145443 ACL hs_old

+</programlisting>

+

+   Where semi-colons are comment delimiters, and the numbers at the start of lines refer to the 

+   internal archive ID assigned to each item. Lines in the file can be commented out, deleted, 

+   and/or 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>

+  </para>

+  <para>

+   Could be used as input to <application>pg_restore</application> and would only restore 

+   items 10 and 6, in that order.

+   <programlisting>

+    $ pg_restore acrhive.file --use=archive.lis    

+   </programlisting>

+  </para>

+

+ </refsect1>
+
+ <refsect1 id="R1-APP-PG-RESTORE-2">
+  <refsect1info>
+   <date>2000-10-11</date>
+  </refsect1info>
+  <title>
+   Notes
+  </title>
+  <para>
+   See the <application>pg_dump</application> section for details on limitation of

+   <application>pg_dump</application>.

+  </para>

+  <para>
+   The limitations of pg_restore are detailed below.
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      When restoring data to a table, <application>pg_restore</application> emits queries 

+      to disable triggers on user tables before inserting the data then emits queries to 

+      re-enable them after the data has been inserted.  If the restore is stopped in the 

+      middle, the system catalogs may be left in the wrong state.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      <application>pg_restore</application> will not restore BLOBs for a single table. If 

+      an archive contains BLOBs, then all BLOBs will be restored. 
+     </para>
+    </listitem>
+
+   </itemizedlist>
+  </para>
+ </refsect1>
+
+ <refsect1 id="R1-APP-PG-RESTORE-3">
+  <refsect1info>
+   <date>2000-10-11</date>
+  </refsect1info>
+  <title>
+   Usage
+  </title>
+  <para>
+   To create a custom archive for a database of the same name as the user:
+
+   <programlisting>
+$ pg_dump -Fc > db.out
+   </programlisting>
+  </para>
+
+  <para>

+   To reload this database:

+

+   <programlisting>

+$ pg_restore db.out | psql -e database

+   </programlisting>

+  </para>

+

+  <para>

+   To dump a database called mydb that contains BLOBs to a TAR file:

+

+   <programlisting>

+$ pg_dump -Ft mydb --blobs > db.tar

+   </programlisting>

+  </para>

+

+  <para>

+   To reload this database (with BLOBs) to an existing db called newdb:

+

+   <programlisting>

+$ pg_restore db.tar --db=newdb

+   </programlisting>

+  </para>

+

+

+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->