pg_dumpall.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.41.2.1 2003/11/04 09:45:30 petere Exp $
PostgreSQL documentation
-->

<refentry id="APP-PG-DUMPALL">
 <refmeta>
  <refentrytitle id="APP-PG-DUMPALL-TITLE"><application>pg_dumpall</application></refentrytitle>
  <manvolnum>1</manvolnum>
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>pg_dumpall</refname>
  <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
 </refnamediv>

 <indexterm zone="app-pg-dumpall">
  <primary>pg_dumpall</primary>
 </indexterm>

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

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

  <para>
   <application>pg_dumpall</application> is a utility for writing out
   (<quote>dumping</quote>) all <productname>PostgreSQL</> databases
   of a cluster into one script file.  The script file contains
   <acronym>SQL</acronym> commands that can be used as input to <xref
   linkend="app-psql"> to restore the databases.  It does this by
   calling <xref linkend="app-pgdump"> for each database in a cluster.
   <application>pg_dumpall</application> also dumps global objects
   that are common to all databases.
   (<application>pg_dump</application> does not save these objects.)
   This currently includes information about database users and
   groups, and access permissions that apply to databases as a whole.
  </para>

  <para>
   Thus, <application>pg_dumpall</application> is an integrated
   solution for backing up your databases.  But note a limitation:
   it cannot dump <quote>large objects</quote>, since
   <application>pg_dump</application> cannot dump such objects into
   text files.  If you have databases containing large objects,
   they should be dumped using one of <application>pg_dump</application>'s
   non-text output modes.
  </para>

  <para>
   Since <application>pg_dumpall</application> reads tables from all
   databases you will most likely have to connect as a database
   superuser in order to produce a complete dump.  Also you will need
   superuser privileges to execute the saved script in order to be
   allowed to add users and groups, and to create databases.
  </para>

  <para>
   The SQL script will be written to the standard output.  Shell
   operators should be used to redirect it into a file.
  </para>

  <para>
  <application>pg_dumpall</application> needs to connect several
  times to the <productname>PostgreSQL</productname> server and might be asking for
  a password each time. It is convenient to have a
  <filename>$HOME/.pgpass</> file in such cases.
  </para>

 </refsect1>

 <refsect1>
  <title>Options</title>

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

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

     <varlistentry>
      <term><option>-c</option></term>
      <term><option>--clean</option></term>
      <listitem>
       <para>
	Include SQL commands to clean (drop) the databases before
	recreating them.
       </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, but it makes the output more portable to other SQL database
	management systems.
       </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,
	but it is necessary if you desire to rearrange column ordering.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-g</option></term>
      <term><option>--globals-only</option></term>
      <listitem>
       <para>
	Dump only global objects (users and groups), no databases.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-i</></term>
      <term><option>--ignore-version</></term>
      <listitem>
       <para>
        Ignore version mismatch between
        <application>pg_dumpall</application> and the database server.
       </para>

       <para>
        <application>pg_dumpall</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_dumpall</application> then
        fails, don't say you weren't warned).
       </para>
      </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 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>-s</option></term>
      <term><option>--schema-only</option></term>
      <listitem>
       <para>
	Dump only the schema (data definitions), no data.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-v</></term>
      <term><option>--verbose</></term>
      <listitem>
       <para>
	Specifies verbose mode.  This will cause
	<application>pg_dumpall</application> to print progress
	messages to standard error.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-x</></term>
      <term><option>--no-privileges</></term>
      <term><option>--no-acl</></term>
      <listitem>
       <para>
	Prevent dumping of access privileges (grant/revoke commands).
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

  <para>
   The following command-line options control the database connection parameters.

   <variablelist>
     <varlistentry>
      <term>-h <replaceable>host</replaceable></term>
      <listitem>
       <para>
	Specifies the host name of the machine on which the database
	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>-p <replaceable>port</replaceable></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>-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>
 </refsect1>


 <refsect1>
  <title>Environment</title>

  <variablelist>
   <varlistentry>
    <term><envar>PGHOST</envar></term>
    <term><envar>PGPORT</envar></term>
    <term><envar>PGUSER</envar></term>

    <listitem>
     <para>
      Default connection parameters
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>


 <refsect1>
  <title>Notes</title>

  <para>
   Since <application>pg_dumpall</application> calls
   <application>pg_dump</application> internally, some diagnostic
   messages will refer to <application>pg_dump</application>.
  </para>

  <para>
   Once restored, it is wise to run <command>ANALYZE</> on each
   database so the optimizer has useful statistics. You
   can also run <command>vacuumdb -a -z</> to analyze all
   databases.
  </para>

 </refsect1>


 <refsect1 id="app-pg-dumpall-ex">
  <title>Examples</title>
  <para>
   To dump all databases:

<screen>
<prompt>$</prompt> <userinput>pg_dumpall &gt; db.out</userinput>
</screen>
  </para>

  <para>
   To reload this database use, for example:
<screen>
<prompt>$</prompt> <userinput>psql -f db.out template1</userinput>
</screen>
   (It is not important to which database you connect here since the
   script file created by <application>pg_dumpall</application> will
   contain the appropriate commands to create and connect to the saved
   databases.)
  </para>
 </refsect1>

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

  <para>
    <xref linkend="app-pgdump">.  Check there for details on possible
    error conditions.
  </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:
-->