pg_dump.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.65.2.2 2004/10/21 22:49:04 tgl Exp $
PostgreSQL documentation
-->

<refentry id="APP-PGDUMP">
 <refmeta>
  <refentrytitle>pg_dump</refentrytitle>
  <manvolnum>1</manvolnum>
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>pg_dump</refname>

  <refpurpose>
   extract a <productname>PostgreSQL</productname> database into a script file or other archive file 
  </refpurpose>
 </refnamediv>

 <indexterm zone="app-pgdump">
  <primary>pg_dump</primary>
 </indexterm>

 <refsynopsisdiv>
  <cmdsynopsis>
   <command>pg_dump</command>
   <arg rep="repeat"><replaceable>option</replaceable></arg>
   <arg><replaceable>dbname</replaceable></arg>
  </cmdsynopsis>
 </refsynopsisdiv>


 <refsect1 id="pg-dump-description">
  <title>
   Description
  </title>

  <para>
   <application>pg_dump</application> is a utility for backing up a
   <productname>PostgreSQL</productname> database. It makes consistent
   backups even if the database is being used concurrently.
   <application>pg_dump</application> does not block other users
   accessing the database (readers or writers).
  </para>

  <para>
   Dumps can be output in script or archive file formats. The script
   files are in plain-text format and contain the SQL commands required
   to reconstruct the database to the state it was in at the time it was
   saved. To restore these scripts, use <xref linkend="app-psql">. They
   can be used to reconstruct the database even on other machines and
   other architectures, with some modifications even on other SQL
   database products.
  </para>

  <para>
   The alternative archive file formats that are meant to be used with
   <xref linkend="app-pgrestore"> to rebuild the database, and they 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 also designed to be portable across
   architectures.
  </para>

  <para>
   When used with one of the archive file formats and combined with
   <application>pg_restore</application>,
   <application>pg_dump</application> provides a flexible archival and
   transfer mechanism. <application>pg_dump</application> can be used to
   backup an entire database, then <application>pg_restore</application>
   can be used to examine the archive and/or select which parts of the
   database are to be restored. The most flexible output file format is
   the <quote>custom</quote> format (<option>-Fc</option>). It allows
   for selection and reordering of all archived items, and is compressed
   by default. The <application>tar</application> format
   (<option>-Ft</option>) is not compressed and it is not possible to
   reorder data when loading, but it is otherwise quite flexible;
   moreover, it can be manipulated with other tools such as
   <command>tar</command>.
  </para>

  <para>
   While running <application>pg_dump</application>, one should examine the
   output for any warnings (printed on standard error), especially in
   light of the limitations listed below.
  </para>

 </refsect1>

 <refsect1 id="pg-dump-options">
  <title>Options</title>

  <para>
   The following command-line options are used to control the output format.

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">dbname</replaceable></term>
      <listitem>
       <para>
	Specifies the name of the database to be dumped.  If this is
	not specified, the environment variable
	<envar>PGDATABASE</envar> is used.  If that is not set, the
	user name specified for the connection is used.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-a</></term>
      <term><option>--data-only</></term>
      <listitem>
       <para>
	Dump only the data, not the schema (data definitions).
       </para>

       <para>
        This option is only meaningful for the plain-text format.  For
        the other formats, you may specify the option when you
        call <command>pg_restore</command>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-b</></term>
      <term><option>--blobs</></term>
      <listitem>
       <para>
 	Include large objects in dump.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-c</option></term>
      <term><option>--clean</option></term>
      <listitem>
       <para>
        Output commands to clean (drop)
	database objects prior to (the commands for) creating them.
       </para>

       <para>
        This option is only meaningful for the plain-text format.  For
        the other formats, you may specify the option when you
        call <command>pg_restore</command>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-C</></term>
      <term><option>--create</></term>
      <listitem>
       <para>
	Begin the output with a command to create the
	database itself and reconnect to the created database.  (With a
	script of this form, it doesn't matter which database you connect
	to before running the script.)
       </para>

       <para>
        This option is only meaningful for the plain-text format.  For
        the other formats, you may specify the option when you
        call <command>pg_restore</command>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-d</option></term>
      <term><option>--inserts</option></term>
      <listitem>
       <para>
	Dump data as <command>INSERT</command> commands (rather
	than <command>COPY</command>).  This will make restoration very slow;
	it is mainly useful for making dumps that can be loaded into
	non-<productname>PostgreSQL</productname> databases.  Note that
	the restore may fail altogether if you have rearranged column order.
	The <option>-D</option> option is safer, though even slower.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-D</option></term>
      <term><option>--column-inserts</option></term>
      <term><option>--attribute-inserts</option></term>
      <listitem>
       <para>
	Dump data as <command>INSERT</command> commands with explicit
	column names (<literal>INSERT INTO
	<replaceable>table</replaceable>
	(<replaceable>column</replaceable>, ...) VALUES
	...</literal>).  This will make restoration very slow; it is mainly
	useful for making dumps that can be loaded into
	non-<productname>PostgreSQL</productname> databases.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-f <replaceable class="parameter">file</replaceable></option></term>
      <term><option>--file=<replaceable class="parameter">file</replaceable></option></term>
      <listitem>
       <para>
	Send output to the specified file.  If this is omitted, the
	standard output is used.
       </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>
        Selects the format of the output.
	<replaceable>format</replaceable> can be one of the following:

       <variablelist>
        <varlistentry>
         <term><literal>p</></term>
         <listitem>
          <para>
           Output a plain-text <acronym>SQL</acronym> script file (default)
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term><literal>t</></term>
         <listitem>
          <para>
         Output a <command>tar</command> archive suitable for input into 
         <application>pg_restore</application>. 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>
         Output a custom archive suitable for input into 
         <application>pg_restore</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</></term>
      <term><option>--ignore-version</></term>
      <listitem>
       <para>
        Ignore version mismatch between
        <application>pg_dump</application> and the database server.
       </para>

       <para>
        <application>pg_dump</application> can handle databases from
        previous releases of <productname>PostgreSQL</>, but very old
        versions are not supported anymore (currently prior to 7.0).
        Use this option if you need to override the version check (and
        if <application>pg_dump</application> then fails, don't say
        you weren't warned).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-n <replaceable class="parameter">namespace</replaceable></option></term>
      <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
      <listitem>
       <para>
        Dump the contents of <replaceable class="parameter">schema</>
        only. If this option is not specified, all non-system schemas
        in the target database will be dumped.
       </para>

       <note>
        <para>
         In this mode, <application>pg_dump</application> makes no
         attempt to dump any other database objects that objects in the
	 selected schema may depend upon. Therefore, there is no
         guarantee that the results of a single-schema dump can be
         successfully restored by themselves into a clean database.
        </para>
       </note>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-o</></term>
      <term><option>--oids</></term>
      <listitem>
       <para>
	Dump object identifiers (<acronym>OID</acronym>s) for every
	table.  Use this option if your application references the <acronym>OID</>
	columns in some way (e.g., in a foreign key constraint).
	Otherwise, this option should not be used.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-O</></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_dump</application> issues
	<command>SET SESSION AUTHORIZATION</command>
	statements to set ownership of created schema elements.
	These statements
	will fail when the script is run unless it is started by a superuser
	(or the same user that owns all of the objects in the script).
	To make a script that can be restored by any user, but will give
	that user ownership of all the objects, specify <option>-O</>.
       </para>

       <para>
        This option is only meaningful for the plain-text format.  For
        the other formats, you may specify the option when you
        call <command>pg_restore</command>.
       </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.