path: root/doc/src/sgml/ref/pg_dump.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.34 2001/08/12 19:02:39 petere Exp $
Postgres documentation
-->

<refentry id="APP-PGDUMP">
 <docinfo>
  <date>2001-03-06</date>
 </docinfo>

 <refmeta>
  <refentrytitle>
   pg_dump
  </refentrytitle>
  <manvolnum>1</manvolnum>
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>pg_dump</refname>

  <refpurpose>
   Extract a <productname>Postgres</productname> database into a script file or other archive file 
  </refpurpose>
 </refnamediv>

 <refsynopsisdiv>
  <cmdsynopsis>
   <command>pg_dump</command>
   <group> <arg>-a</arg> <arg>-s</arg> </group>
   <arg>-b</arg>
   <arg>-c</arg>
   <arg>-C</arg>
   <group> <arg>-d</arg> <arg>-D</arg> </group>
   <arg>-f <replaceable>file</replaceable></arg> 
   <arg>-F <replaceable>format</replaceable></arg>
   <arg>-i</arg>
   <group> <arg>-n</arg> <arg>-N</arg> </group>
   <arg>-o</arg>
   <arg>-O</arg>
   <arg>-R</arg>
   <arg>-S</arg>
   <arg>-t <replaceable>table</replaceable></arg>
   <arg>-v</arg>
   <arg>-x</arg>
   <arg>-Z <replaceable>0...9</replaceable></arg>
   <arg>-h <replaceable>host</replaceable></arg>
   <arg>-p <replaceable>port</replaceable></arg>
   <arg>-U <replaceable>username</replaceable></arg>
   <arg>-W</arg>
   <arg choice="plain"><replaceable>dbname</replaceable></arg>
  </cmdsynopsis>
 </refsynopsisdiv>


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

  <para>
   <command>pg_dump</command> is a utility for dumping out a 
   <productname>Postgres</productname> database into a script or archive 
   file containing query commands. The script files are in text format 
   and can be used to reconstruct the database, even on other machines 
   and other architectures.
   The archive files, new with version 7.1, contain enough information for 
   <xref linkend="app-pgrestore"> to rebuild the database, but also
   allow <command>pg_restore</command> 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>
   <command>pg_dump</command> 
   will produce the queries necessary to re-generate all
   user-defined types, functions, tables, indexes, aggregates, and
   operators.  In addition, all the data is copied out in text format so
   that it can be readily copied in again, as well as imported into tools
   for editing.
  </para>

  <para>
   <command>pg_dump</command> 
   is useful for dumping out the contents of a database to move from one
   <productname>Postgres</productname> installation to another.  After running 
   <command>pg_dump</command>,
   one should examine the output for any warnings, especially
   in light of the limitations listed below. 
  </para>

  <para>
   When used with one of the alternate file formats and combined with 
   <command>pg_restore</command>, it provides a flexible archival 
   and transfer mechanism. <command>pg_dump</command> can be used 
   to backup an entire database, then <command>pg_restore</command> 
   can be used to examine the archive and/or select which parts of the 
   database are to be restored.
   See the <xref linkend="app-pgrestore"> documentation for details.
  </para>

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

   <para>
    <command>pg_dump</command> accepts the following command
    line arguments.  (Long option forms are only available on some platforms.)

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">dbname</replaceable></term>
      <listitem>
       <para>
	Specifies the name of the database to be extracted.
       </para>
      </listitem>
     </varlistentry>

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

     <varlistentry>
      <term>-b</term>
      <term>--blobs</term>
      <listitem>
       <para>
 	Dump data and <acronym>BLOB</acronym> data.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-c</term>
      <term>--clean</term>
      <listitem>
       <para>
        Dump commands to clean (drop) the schema prior to (the
        commands for) creating it.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-C</term>
      <term>--create</term>
      <listitem>
       <para>
	For plain text (script) output, include commands to create the database itself.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-d</term>
      <term>--inserts</term>
      <listitem>
       <para>
	Dump data as proper <command>INSERT</command> commands (not
	<command>COPY</command>). This will make restoration very
	slow.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-D</term>
      <term>--column-inserts</term>
      <term>--attribute-inserts</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.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-f <replaceable class="parameter">file</replaceable></term>
      <term>--file=<replaceable class="parameter">file</replaceable></term>
      <listitem>
       <para>
	Send output to the specified file.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-F <replaceable class="parameter">format</replaceable></term>
      <term>--format=<replaceable class="parameter">format</replaceable></term>
      <listitem>
       <para>
	Format can be one of the following:

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

        <varlistentry>
         <term>t</term>
         <listitem>
          <para>
         output a <filename>tar</filename> archive suitable for input into 
         <command>pg_restore</command>. 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>
         output a custom archive suitable for input into 
         <command>pg_restore</command>. 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>-i</term>
      <term>--ignore-version</term>
      <listitem>
       <para>
        Ignore version mismatch between <command>pg_dump</command>
	and the database server.  Since <command>pg_dump</command>
	knows a great deal about system catalogs, any given version of
	<command>pg_dump</command> is only intended to work with
	the corresponding release of the database server.  Use this option
	if you need to override the version check (and if
	<command>pg_dump</command> then fails, don't
	say you weren't warned).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-n</term>
      <term>--no-quotes</term>
      <listitem>
       <para>
	Suppress double quotes around identifiers unless absolutely necessary.
	This may cause trouble loading this dumped data if there are reserved words
	used for identifiers. 
	This was the default behavior for
	<command>pg_dump</command> prior to version 6.4.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-N</term>
      <term>--quotes</term>
      <listitem>
       <para>
	Include double quotes around identifiers.
	This is the default.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-o</term>
      <term>--oids</term>
      <listitem>
       <para>
	Dump object identifiers (<acronym>OID</acronym>s) for every table.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-O</term>
      <term>--no-owner</term>
      <listitem>
       <para>
	In plain text output mode, do not set object ownership to
        match the original database. Typically,
        <command>pg_dump</command> issues
        (<command>psql</command>-specific) <command>\connect</command>
        statements to set ownership of schema elements.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-R</term>
      <term>--no-reconnect</term>
      <listitem>
       <para>
	In plain text output mode, prohibit <command>pg_dump</command> 
        from issuing any <command>\connect</command> statements.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-s</term>
      <term>--schema-only</term>
      <listitem>
       <para>
	Dump only the schema (data definitions), no data.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-S <replaceable class="parameter">username</replaceable></term>
      <term>--superuser=<replaceable class="parameter">username</replaceable></term>
      <listitem>
       <para>
	Specify the superuser user name to use when disabling triggers and/or 
      setting ownership of schema elements.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-t <replaceable class="parameter">table</replaceable></term>
      <term>--table=<replaceable class="parameter">table</replaceable></term>
      <listitem>
       <para>
	Dump data for <replaceable class="parameter">table</replaceable> only.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-v</term>
      <term>--verbose</term>
      <listitem>
       <para>
	Specifies verbose mode.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-x</term>
      <term>--no-privileges</term>
      <term>--no-acl</term>
      <listitem>
       <para>
	Prevent dumping of access privileges (grant/revoke commands)
	and table ownership information.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-Z <replaceable class="parameter">0..9</replaceable></term>
      <term>--compress=<replaceable class="parameter">0..9</replaceable></term>
      <listitem>
       <para>
	Specify the compression level to use in archive formats that support 
      compression (currently only the custom archive format supports compression).
       </para>
      </listitem>
     </varlistentry>


    </variablelist>
   </para>
   <para>
    <command>pg_dump</command> also accepts 
    the following command line arguments for connection parameters:

    <variablelist>
     <varlistentry>
      <term>-h <replaceable class="parameter">host</replaceable></term>
      <term>--host=<replaceable class="parameter">host</replaceable></term>
      <listitem>
       <para>
	Specifies the host name of the machine on which the 
	<command>postmaster</command>
	is running.  If host begins with a slash, it is used 
	as the directory for the Unix domain socket.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-p <replaceable class="parameter">port</replaceable></term>
      <term>--port=<replaceable class="parameter">port</replaceable></term>
      <listitem>
       <para>
	Specifies the Internet TCP/IP port or local Unix domain socket file 
	extension on which the <command>postmaster</command>
	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>

     <varlistentry>
      <term>-U <replaceable>username</replaceable></term>
      <listitem>
       <para>
        Connect as the given user.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-W</term>
      <listitem>
       <para>
        Force a password prompt.  This should happen automatically if
        the server requires password authentication.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

 </refsect1>


 <refsect1 id="app-pgdump-diagnostics">
  <title>Diagnostics</title>

  <msgset>
   <msgentry>
    <msg>
     <msgmain>
      <msgtext>
<screen><computeroutput>
Connection to database 'template1' failed.
connectDBStart() -- connect() failed: No such file or directory
        Is the postmaster running locally
        and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
</computeroutput></screen>
      </msgtext>
     </msgmain> 
    </msg>

    <msgexplan>
       <para>
	<command>pg_dump</command> could not attach to the 
	<command>postmaster</command> 
	process on the specified host and port.  If you see this message,
	ensure that the <command>postmaster</command> 
	is running on the proper host and that you have specified the proper
	port.
       </para>
    </msgexplan>
   </msgentry>

  </msgset>

   <note>
    <para>
     <command>pg_dump</command> internally executes
     <command>SELECT</command> statements. If you have problems running
     <command>pg_dump</command>,
     make sure you are able to select information from the database using, for
     example, <xref linkend="app-psql">.
    </para>
   </note>
 </refsect1>


 <refsect1 id="pg-dump-notes">
  <title>Notes</title>
  <para>
   <command>pg_dump</command> has a few limitations.

   <itemizedlist>
    <listitem>
     <para>
      When dumping a single table or as plain text, <command>pg_dump</command> 
      does not handle large objects. Large objects must be dumped in their
      entirety using one of the binary archive formats.
     </para>
    </listitem>

    <listitem>
     <para>
      When doing a data only dump, <command>pg_dump</command> emits queries
      to disable triggers on user tables before inserting the data and 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>

   </itemizedlist>
  </para>
 </refsect1>

 <refsect1 id="pg-dump-examples">
  <title>Examples</title>

  <para>
   To dump a database:
<screen>
<prompt>$</prompt> <userinput>pg_dump mydb &gt; db.out</userinput>
</screen>
  </para>

  <para>
   To reload this database:
<screen>
<prompt>$</prompt> <userinput>psql -d database -f db.out</userinput>
</screen>
  </para>

  <para>
   To dump a database called mydb that contains
   <acronym>BLOB</acronym>s to a <filename>tar</filename> file:

<screen>
<prompt>$</prompt> <userinput>pg_dump -Ft -b mydb &gt; db.tar</userinput>
</screen>
  </para>

  <para>
   To reload this database (with <acronym>BLOB</acronym>s) to an
   existing database called newdb:

<screen>
<prompt>$</prompt> <userinput>pg_restore -d newdb db.tar</userinput>
</screen>
  </para>

 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="app-pg-dumpall"></member>
   <member><xref linkend="app-pgrestore"></member>
   <member><xref linkend="app-psql"></member>
   <member><citetitle>PostgreSQL Administrator's Guide</citetitle></member>
  </simplelist>
 </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:
-->