pg_restore.sgml

PostgreSQL7.4.6 for Linux

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v 1.43 2003/09/23 22:48:53 tgl Exp $ -->

<refentry id="APP-PGRESTORE">
 <refmeta>
  <refentrytitle>pg_restore</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 pg_dump
  </refpurpose>
 </refnamediv>

 <indexterm zone="app-pgrestore">
  <primary>pg_restore</primary>
 </indexterm>

 <refsynopsisdiv>
  <cmdsynopsis>
   <command>pg_restore</command>
   <arg rep="repeat"><replaceable>option</replaceable></arg>
   <arg><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, the archive is restored directly into
   the database.  (Large objects can only be restored by using such a direct
   database connection.)  Otherwise, a script containing the SQL
   commands necessary to rebuild the database is created (and written
   to a file or standard output), similar to the ones created by the
   <application>pg_dump</application> plain text format.  Some of the
   options controlling the script 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 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).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-c</option></term>
      <term><option>--clean</option></term>
      <listitem>
       <para>
	Clean (drop) database objects before recreating them.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-C</option></term>
      <term><option>--create</option></term>
      <listitem>
       <para>
        Create the database before restoring into it.  (When this
        option is used, the database named with <option>-d</option> is
        used only to issue the initial <literal>CREATE DATABASE</>
        command.  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.
       </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>. Default is the standard
        output.
       </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>t</></term>
         <listitem>
          <para>
           The archive is a <command>tar</command> 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><literal>c</></term>
         <listitem>
          <para>
           The archive is in the custom format of
           <application>pg_dump</application>. 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>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-i</option></term>
      <term><option>--ignore-version</option></term>
      <listitem>
       <para>
	Ignore database version checks.
       </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.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-l</option></term>
      <term><option>--list</option></term>
      <listitem>
       <para>
        List the contents of the archive. The output of this operation
        can be used with the <option>-L</option> option to restrict
        and reorder the items that are restored.
       </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 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 <literal>;</literal> at the
        start of the line.  (See below for examples.)
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-N</option></term>
      <term><option>--orig-order</option></term>
      <listitem>
       <para>
        Restore items in the order they were originally generated within
        <application>pg_dump</application>.  This option has no known
	practical use, since <application>pg_dump</application> generates
	the items in an order convenient to it, which is unlikely to be a
	safe order for restoring them.  (This is <emphasis>not</> the order
	in which the items are ultimately listed in the archive's table of
	contents.)  See also <option>-r</>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-o</option></term>
      <term><option>--oid-order</option></term>
      <listitem>
       <para>
        Restore items in order by OID.  This option is of limited usefulness,
	since OID is only an approximate indication of original creation
	order.  This option overrides <option>-N</> if both are specified.
	See also <option>-r</>.
       </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>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.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-r</option></term>
      <term><option>--rearrange</option></term>
      <listitem>
       <para>
        Rearrange items by object type (this occurs after the sorting
	specified by <option>-N</option> or <option>-o</option>, if
	given).  The rearrangement is intended to give the best possible
	restore performance.
       </para>

       <para>
        When none of <option>-N</option>, <option>-o</option>, and
	<option>-r</> appear, <application>pg_restore</application> restores
	items in the order they appear in the dump's table of contents,
	or in the order they appear in the  <REPLACEABLE
        CLASS="PARAMETER">list-file</REPLACEABLE> if <option>-L</> is
	given.  The combination of <option>-o</> and <option>-r</>
	duplicates the sorting done by <application>pg_dump</application>
	before creating the dump's table of contents,
	and so it is normally unnecessary to specify it.
       </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 the data.
        Sequence values will be reset.
       </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>