Skip to content
Snippets Groups Projects
Select Git revision
  • benchmark-tools
  • postgres-lambda
  • master default
  • REL9_4_25
  • REL9_5_20
  • REL9_6_16
  • REL_10_11
  • REL_11_6
  • REL_12_1
  • REL_12_0
  • REL_12_RC1
  • REL_12_BETA4
  • REL9_4_24
  • REL9_5_19
  • REL9_6_15
  • REL_10_10
  • REL_11_5
  • REL_12_BETA3
  • REL9_4_23
  • REL9_5_18
  • REL9_6_14
  • REL_10_9
  • REL_11_4
23 results

pg_dump.sgml

Blame
  • pg_dump.sgml 41.94 KiB
    <!--
    doc/src/sgml/ref/pg_dump.sgml
    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>connection-option</replaceable></arg>
       <arg rep="repeat"><replaceable>option</replaceable></arg>
       <arg choice="opt"><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. Script
       dumps are plain-text files containing the SQL commands required
       to reconstruct the database to the state it was in at the time it was
       saved. To restore from such a script, feed it to <xref
       linkend="app-psql">. Script files
       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 must be used with
       <xref linkend="app-pgrestore"> to rebuild the database.  They
       allow <application>pg_restore</application> to be selective about
       what is restored, or even to reorder the items prior to being
       restored.
       The archive file formats are 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.
      </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 control the content and
        format of the output.
    
        <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).
            Table data, large objects, and sequence values are dumped.
           </para>
    
           <para>
            This option is similar to, but for historical reasons not identical
            to, specifying <option>--section=data</>.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-b</></term>
          <term><option>--blobs</></term>
          <listitem>
           <para>
            Include large objects in the dump.  This is the default behavior
            except when <option>--schema</>, <option>--table</>, or
            <option>--schema-only</> is specified, so the <option>-b</>
            switch is only useful to add large objects to selective dumps.
           </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 outputting the commands for creating them.
            (Restore might generate some harmless error messages, if any objects
            were not present in the destination database.)
           </para>
    
           <para>
            This option is only meaningful for the plain-text format.  For
            the archive formats, you can 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 in the
            destination installation you connect to before running the script.)
            If <option>--clean</option> is also specified, the script drops and
            recreates the target database before reconnecting to it.
           </para>
    
           <para>
            This option is only meaningful for the plain-text format.  For
            the archive formats, you can specify the option when you
            call <command>pg_restore</command>.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
          <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
          <listitem>
           <para>
            Create the dump in the specified character set encoding. By default,
            the dump is created in the database encoding.  (Another way to get the
            same result is to set the <envar>PGCLIENTENCODING</envar> environment
            variable to the desired dump encoding.)
           </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. This parameter can be omitted for
            file based output formats, in which case the standard output is used.
            It must be given for the directory output format however, where it
            specifies the target directory instead of a file. In this case the
            directory is created by <command>pg_dump</command> and must not exist
            before.
           </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>
             <term><literal>plain</></term>
             <listitem>
              <para>
               Output a plain-text <acronym>SQL</acronym> script file (the default).
              </para>
             </listitem>
            </varlistentry>
    
            <varlistentry>
             <term><literal>c</></term>
             <term><literal>custom</></term>
             <listitem>
              <para>
               Output a custom-format archive suitable for input into
               <application>pg_restore</application>.
               Together with the directory output format, this is the most flexible
               output format in that it allows manual selection and reordering of
               archived items during restore. This format is also compressed by
               default.
              </para>
             </listitem>
            </varlistentry>
    
            <varlistentry>
             <term><literal>d</></term>
             <term><literal>directory</></term>
             <listitem>
              <para>
               Output a directory-format archive suitable for input into
               <application>pg_restore</application>. This will create a directory
               with one file for each table and blob being dumped, plus a
               so-called Table of Contents file describing the dumped objects in a
               machine-readable format that <application>pg_restore</application>
               can read. A directory format archive can be manipulated with
               standard Unix tools; for example, files in an uncompressed archive
               can be compressed with the <application>gzip</application> tool.
               This format is compressed by default.
              </para>
             </listitem>
            </varlistentry>
    
            <varlistentry>
             <term><literal>t</></term>
             <term><literal>tar</></term>
             <listitem>
              <para>
               Output a <command>tar</command>-format archive suitable for input
               into <application>pg_restore</application>. The tar format is
               compatible with the directory format: extracting a tar-format
               archive produces a valid directory-format archive.
               However, the tar format does not support compression. Also, when
               using tar format the relative order of table data items cannot be
               changed during restore.
              </para>
             </listitem>
            </varlistentry>
           </variablelist></para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-i</></term>
          <term><option>--ignore-version</></term>
          <listitem>
           <para>
            A deprecated option that is now ignored.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
          <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
          <listitem>
           <para>
            Dump only schemas matching <replaceable
            class="parameter">schema</replaceable>; this selects both the
            schema itself, and all its contained objects.  When this option is
            not specified, all non-system schemas in the target database will be
            dumped.  Multiple schemas can be
            selected by writing multiple <option>-n</> switches.  Also, the
            <replaceable class="parameter">schema</replaceable> parameter is
            interpreted as a pattern according to the same rules used by
            <application>psql</>'s <literal>\d</> commands (see <xref
            linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
            so multiple schemas can also be selected by writing wildcard characters
            in the pattern.  When using wildcards, be careful to quote the pattern
            if needed to prevent the shell from expanding the wildcards;  see
            <xref linkend="pg-dump-examples" endterm="pg-dump-examples-title">.
           </para>
    
           <note>
            <para>
             When <option>-n</> is specified, <application>pg_dump</application>
             makes no attempt to dump any other database objects that the selected
             schema(s) might depend upon. Therefore, there is no guarantee
             that the results of a specific-schema dump can be successfully
             restored by themselves into a clean database.
            </para>
           </note>
    
           <note>
            <para>
             Non-schema objects such as blobs are not dumped when <option>-n</> is
             specified.  You can add blobs back to the dump with the
             <option>--blobs</> switch.
            </para>
           </note>
    
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
          <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
          <listitem>
           <para>
            Do not dump any schemas matching the <replaceable
            class="parameter">schema</replaceable> pattern.  The pattern is
            interpreted according to the same rules as for <option>-n</>.
            <option>-N</> can be given more than once to exclude schemas
            matching any of several patterns.
           </para>
    
           <para>
            When both <option>-n</> and <option>-N</> are given, the behavior
            is to dump just the schemas that match at least one <option>-n</>
            switch but no <option>-N</> switches.  If <option>-N</> appears
            without <option>-n</>, then schemas matching <option>-N</> are
            excluded from what is otherwise a normal dump.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-o</></term>
          <term><option>--oids</></term>
          <listitem>
           <para>
            Dump object identifiers (<acronym>OID</acronym>s) as part of the
            data 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>ALTER OWNER</> or
            <command>SET SESSION AUTHORIZATION</command>
            statements to set ownership of created database objects.
            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 archive formats, you can 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.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-s</option></term>
          <term><option>--schema-only</option></term>
          <listitem>
           <para>
            Dump only the object definitions (schema), not data.
           </para>
           <para>
            This option is the inverse of <option>--data-only</>.
            It is similar to, but for historical reasons not identical to,
            specifying
            <option>--section=pre-data --section=post-data</>.
           </para>
           <para>
            (Do not confuse this with the <option>--schema</> option, which
            uses the word <quote>schema</> in a different meaning.)
           </para>
           <para>
            To exclude table data for only a subset of tables in the database,
            see <option>--exclude-table-data</>.
           </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>
           <para>
            Specify the superuser user name to use when disabling triggers.
            This is only relevant if <option>--disable-triggers</> is used.
            (Usually, it's better to leave this out, and instead start the
            resulting script as superuser.)
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-t <replaceable class="parameter">table</replaceable></option></term>
          <term><option>--table=<replaceable class="parameter">table</replaceable></option></term>
          <listitem>
           <para>
            Dump only tables (or views or sequences or foreign tables) matching
            <replaceable class="parameter">table</replaceable>.  Multiple tables
            can be selected by writing multiple <option>-t</> switches.  Also, the
            <replaceable class="parameter">table</replaceable> parameter is
            interpreted as a pattern according to the same rules used by
            <application>psql</>'s <literal>\d</> commands (see <xref
            linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
            so multiple tables can also be selected by writing wildcard characters
            in the pattern.  When using wildcards, be careful to quote the pattern
            if needed to prevent the shell from expanding the wildcards;  see
            <xref linkend="pg-dump-examples" endterm="pg-dump-examples-title">.
           </para>
    
           <para>
            The <option>-n</> and <option>-N</> switches have no effect when
            <option>-t</> is used, because tables selected by <option>-t</> will
            be dumped regardless of those switches, and non-table objects will not
            be dumped.
           </para>
    
           <note>
            <para>
             When <option>-t</> is specified, <application>pg_dump</application>
             makes no attempt to dump any other database objects that the selected
             table(s) might depend upon. Therefore, there is no guarantee
             that the results of a specific-table dump can be successfully
             restored by themselves into a clean database.
            </para>
           </note>
    
           <note>
            <para>
             The behavior of the <option>-t</> switch is not entirely upward
             compatible with pre-8.2 <productname>PostgreSQL</productname>
             versions.  Formerly, writing <literal>-t tab</> would dump all
             tables named <literal>tab</>, but now it just dumps whichever one
             is visible in your default search path.  To get the old behavior
             you can write <literal>-t '*.tab'</>.  Also, you must write something
             like <literal>-t sch.tab</> to select a table in a particular schema,
             rather than the old locution of <literal>-n sch -t tab</>.
            </para>
           </note>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-T <replaceable class="parameter">table</replaceable></option></term>
          <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term>
          <listitem>
           <para>
            Do not dump any tables matching the <replaceable
            class="parameter">table</replaceable> pattern.  The pattern is
            interpreted according to the same rules as for <option>-t</>.
            <option>-T</> can be given more than once to exclude tables
            matching any of several patterns.
           </para>
    
           <para>
            When both <option>-t</> and <option>-T</> are given, the behavior
            is to dump just the tables that match at least one <option>-t</>
            switch but no <option>-T</> switches.  If <option>-T</> appears
            without <option>-t</>, then tables matching <option>-T</> are
            excluded from what is otherwise a normal dump.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-v</></term>
          <term><option>--verbose</></term>
          <listitem>
           <para>
            Specifies verbose mode.  This will cause
            <application>pg_dump</application> to output detailed object
            comments and start/stop times to the dump file, and progress
            messages to standard error.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
           <term><option>-V</></term>
           <term><option>--version</></term>
           <listitem>
           <para>
           Print the <application>pg_dump</application> version and exit.
           </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>
    
         <varlistentry>
          <term><option>-Z <replaceable class="parameter">0..9</replaceable></option></term>
          <term><option>--compress=<replaceable class="parameter">0..9</replaceable></option></term>
          <listitem>
           <para>
            Specify the compression level to use.  Zero means no compression.
            For the custom archive format, this specifies compression of
            individual table-data segments, and the default is to compress
            at a moderate level.
            For plain text output, setting a nonzero compression level causes
            the entire output file to be compressed, as though it had been
            fed through <application>gzip</>; but the default is not to compress.
            The tar archive format currently does not support compression at all.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--binary-upgrade</option></term>
          <listitem>
           <para>
            This option is for use by in-place upgrade utilities.  Its use
            for other purposes is not recommended or supported.  The
            behavior of the option may change in future releases without
            notice.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <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.
            However, since this option generates a separate command for each row,
            an error in reloading a row causes only that row to be lost rather
            than the entire table contents.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--disable-dollar-quoting</></term>
          <listitem>
           <para>
            This option disables the use of dollar quoting for function bodies,
            and forces them to be quoted using SQL standard string syntax.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--disable-triggers</></term>
          <listitem>
           <para>
            This option is only relevant when creating a data-only dump.
            It instructs <application>pg_dump</application> to include commands
            to temporarily disable triggers on the target tables while
            the data is reloaded.  Use this if you have referential
            integrity checks or other triggers on the tables that you
            do not want to invoke during data reload.
           </para>
    
           <para>
            Presently, the commands emitted for <option>--disable-triggers</>
            must be done as superuser.  So, you should also specify
            a superuser name with <option>-S</>, or preferably be careful to
            start the resulting script as a superuser.
           </para>
    
           <para>
            This option is only meaningful for the plain-text format.  For
            the archive formats, you can specify the option when you
            call <command>pg_restore</command>.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--exclude-table-data=<replaceable class="parameter">table</replaceable></option></term>
          <listitem>
           <para>
            Do not dump data for any tables matching the <replaceable
            class="parameter">table</replaceable> pattern. The pattern is
            interpreted according to the same rules as for <option>-t</>.
            <option>--exclude-table-data</> can be given more than once to
            exclude tables matching any of several patterns. This option is
            useful when you need the definition of a particular table even
            though you do not need the data in it.
           </para>
           <para>
            To exclude data for all tables in the database, see <option>--schema-only</>.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <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.
            However, since this option generates a separate command for each row,
            an error in reloading a row causes only that row to be lost rather
            than the entire table contents.
            Note that
            the restore might fail altogether if you have rearranged column order.
            The <option>--column-inserts</option> option is safe against column
            order changes, though even slower.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
          <listitem>
           <para>
            Do not wait forever to acquire shared table locks at the beginning of
            the dump. Instead fail if unable to lock a table within the specified
            <replaceable class="parameter">timeout</>. The timeout may be
            specified in any of the formats accepted by <command>SET
            statement_timeout</>.  (Allowed values vary depending on the server
            version you are dumping from, but an integer number of milliseconds
            is accepted by all versions since 7.3.  This option is ignored when
            dumping from a pre-7.3 server.)
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--no-security-labels</option></term>
          <listitem>
           <para>
            Do not dump security labels.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--no-tablespaces</option></term>
          <listitem>
           <para>
            Do not output commands to select tablespaces.
            With this option, all objects will be created in whichever
            tablespace is the default during restore.
           </para>
    
           <para>
            This option is only meaningful for the plain-text format.  For
            the archive formats, you can specify the option when you
            call <command>pg_restore</command>.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--no-unlogged-table-data</option></term>
          <listitem>
           <para>
            Do not dump the contents of unlogged tables.  This option has no
            effect on whether or not the table definitions (schema) are dumped;
            it only suppresses dumping the table data. Data in unlogged tables
            is always excluded when dumping from a standby server.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--quote-all-identifiers</></term>
          <listitem>
           <para>
            Force quoting of all identifiers.  This option is recommended when
            dumping a database from a server whose <productname>PostgreSQL</>
            major version is different from <application>pg_dump</>'s, or when
            the output is intended to be loaded into a server of a different
            major version.  By default, <application>pg_dump</> quotes only
            identifiers that are reserved words in its own major version.
            This sometimes results in compatibility issues when dealing with
            servers of other versions that may have slightly different sets
            of reserved words.  Using <option>--quote-all-identifiers</> prevents
            such issues, at the price of a harder-to-read dump script.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
           <term><option>--section=<replaceable class="parameter">sectionname</replaceable></option></term>
           <listitem>
             <para>
              Only dump the named section. The section name can be
              <option>pre-data</>, <option>data</>, or <option>post-data</>.
              This option can be specified more than once to select multiple
              sections. The default is to dump all sections.
             </para>
             <para>
              The data section contains actual table data, large-object
              contents, and sequence values.
              Post-data items include definitions of indexes, triggers, rules,
              and constraints other than validated check constraints.
              Pre-data items include all other data definition items.
             </para>
           </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--serializable-deferrable</option></term>
          <listitem>
           <para>
            Use a <literal>serializable</literal> transaction for the dump, to
            ensure that the snapshot used is consistent with later database
            states; but do this by waiting for a point in the transaction stream
            at which no anomalies can be present, so that there isn't a risk of
            the dump failing or causing other transactions to roll back with a
            <literal>serialization_failure</literal>.  See <xref linkend="mvcc">
            for more information about transaction isolation and concurrency
            control.
           </para>
    
           <para>
            This option is not beneficial for a dump which is intended only for
            disaster recovery.  It could be useful for a dump used to load a
            copy of the database for reporting or other read-only load sharing
            while the original database continues to be updated.  Without it the
            dump may reflect a state which is not consistent with any serial
            execution of the transactions eventually committed.  For example, if
            batch processing techniques are used, a batch may show as closed in
            the dump without all of the items which are in the batch appearing.
           </para>
    
           <para>
            This option will make no difference if there are no read-write
            transactions active when pg_dump is started.  If read-write
            transactions are active, the start of the dump may be delayed for an
            indeterminate length of time.  Once running, performance with or
            without the switch is the same.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--use-set-session-authorization</></term>
          <listitem>
           <para>
            Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
            instead of <command>ALTER OWNER</> commands to determine object
            ownership.  This makes the dump more standards-compatible, but
            depending on the history of the objects in the dump, might not restore
            properly.  Also, a dump using <command>SET SESSION AUTHORIZATION</>
            will certainly require superuser privileges to restore correctly,
            whereas <command>ALTER OWNER</> requires lesser privileges.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
           <term><option>-?</></term>
           <term><option>--help</></term>
           <listitem>
           <para>
           Show help about <application>pg_dump</application> command line
           arguments, and exit.
           </para>
           </listitem>
         </varlistentry>
    
        </variablelist>
       </para>
    
       <para>
        The following command-line options control the database connection parameters.
    
        <variablelist>
         <varlistentry>
          <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
          <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
          <listitem>
           <para>
            Specifies the host name of the machine on which the 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><option>-p <replaceable class="parameter">port</replaceable></option></term>
          <term><option>--port=<replaceable class="parameter">port</replaceable></option></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><option>-U <replaceable>username</replaceable></option></term>
          <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
          <listitem>
           <para>
            User name to connect as.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-w</></term>
          <term><option>--no-password</></term>
          <listitem>
           <para>
            Never issue a password prompt.  If the server requires
            password authentication and a password is not available by
            other means such as a <filename>.pgpass</filename> file, the
            connection attempt will fail.  This option can be useful in
            batch jobs and scripts where no user is present to enter a
            password.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>-W</option></term>
          <term><option>--password</option></term>
          <listitem>
           <para>
            Force <application>pg_dump</application> to prompt for a
            password before connecting to a database.
           </para>
    
           <para>
            This option is never essential, since
            <application>pg_dump</application> will automatically prompt
            for a password if the server demands password authentication.
            However, <application>pg_dump</application> will waste a
            connection attempt finding out that the server wants a password.
            In some cases it is worth typing <option>-W</> to avoid the extra
            connection attempt.
           </para>
          </listitem>
         </varlistentry>
    
         <varlistentry>
          <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
          <listitem>
           <para>
            Specifies a role name to be used to create the dump.
            This option causes <application>pg_dump</> to issue a
            <command>SET ROLE</> <replaceable class="parameter">rolename</>
            command after connecting to the database. It is useful when the
            authenticated user (specified by <option>-U</>) lacks privileges
            needed by <application>pg_dump</>, but can switch to a role with
            the required rights.  Some installations have a policy against
            logging in directly as a superuser, and use of this option allows
            dumps to be made without violating the policy.
           </para>
          </listitem>
         </varlistentry>
        </variablelist>
       </para>
     </refsect1>
    
     <refsect1>
      <title>Environment</title>
    
      <variablelist>
       <varlistentry>
        <term><envar>PGDATABASE</envar></term>
        <term><envar>PGHOST</envar></term>
        <term><envar>PGOPTIONS</envar></term>
        <term><envar>PGPORT</envar></term>
    
        <term><envar>PGUSER</envar></term>
        <listitem>
         <para>
          Default connection parameters.
         </para>
        </listitem>
    
       </varlistentry>
      </variablelist>
    
      <para>
       This utility, like most other <productname>PostgreSQL</> utilities,
       also uses the environment variables supported by <application>libpq</>
       (see <xref linkend="libpq-envars">).
      </para>
    
     </refsect1>
    
     <refsect1 id="app-pgdump-diagnostics">
      <title>Diagnostics</title>
    
      <para>
       <application>pg_dump</application> internally executes
       <command>SELECT</command> statements. If you have problems running
       <application>pg_dump</application>, make sure you are able to
       select information from the database using, for example, <xref
       linkend="app-psql">.  Also, any default connection settings and environment
       variables used by the <application>libpq</application> front-end
       library will apply.
      </para>
    
      <para>
       The database activity of <application>pg_dump</application> is
       normally collected by the statistics collector.  If this is
       undesirable, you can set parameter <varname>track_counts</>
       to false via <envar>PGOPTIONS</envar> or the <literal>ALTER
       USER</literal> command.
      </para>
    
     </refsect1>
    
    
     <refsect1 id="pg-dump-notes">
      <title>Notes</title>
    
      <para>
       If your database cluster has any local additions to the <literal>template1</> database,
       be careful to restore the output of <application>pg_dump</application> into a
       truly empty database; otherwise you are likely to get errors due to
       duplicate definitions of the added objects.  To make an empty database
       without any local additions, copy from <literal>template0</> not <literal>template1</>,
       for example:
    <programlisting>
    CREATE DATABASE foo WITH TEMPLATE template0;
    </programlisting>
      </para>
    
      <para>
       When a data-only dump is chosen and the option <option>--disable-triggers</>
       is used, <application>pg_dump</application> emits commands
       to disable triggers on user tables before inserting the data,
       and then commands to re-enable them after the data has been
       inserted.  If the restore is stopped in the middle, the system
       catalogs might be left in the wrong state.
      </para>
    
      <para>
       The dump file produced by <application>pg_dump</application>
       does not contain the statistics used by the optimizer to make
       query planning decisions.  Therefore, it is wise to run
       <command>ANALYZE</command> after restoring from a dump file
       to ensure optimal performance; see <xref linkend="vacuum-for-statistics">
       and <xref linkend="autovacuum"> for more information.
       The dump file also does not
       contain any <command>ALTER DATABASE ... SET</> commands;
       these settings are dumped by <xref linkend="app-pg-dumpall">,
       along with database users and other installation-wide settings.
      </para>
    
      <para>
       Because <application>pg_dump</application> is used to transfer data
       to newer versions of <productname>PostgreSQL</>, the output of
       <application>pg_dump</application> can be expected to load into
       <productname>PostgreSQL</> server versions newer than
       <application>pg_dump</>'s version.  <application>pg_dump</> can also
       dump from <productname>PostgreSQL</> servers older than its own version.
       (Currently, servers back to version 7.0 are supported.)
       However, <application>pg_dump</> cannot dump from
       <productname>PostgreSQL</> servers newer than its own major version;
       it will refuse to even try, rather than risk making an invalid dump.
       Also, it is not guaranteed that <application>pg_dump</>'s output can
       be loaded into a server of an older major version &mdash; not even if the
       dump was taken from a server of that version.  Loading a dump file
       into an older server may require manual editing of the dump file
       to remove syntax not understood by the older server.
      </para>
     </refsect1>
    
     <refsect1 id="pg-dump-examples">
      <title id="pg-dump-examples-title">Examples</title>
    
      <para>
       To dump a database called <literal>mydb</> into a SQL-script file:
    <screen>
    <prompt>$</prompt> <userinput>pg_dump mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To reload such a script into a (freshly created) database named
       <literal>newdb</>:
    
    <screen>
    <prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To dump a database into a custom-format archive file:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -Fc mydb &gt; db.dump</userinput>
    </screen>
      </para>
    
      <para>
       To dump a database into a directory-format archive:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -Fd mydb -f dumpdir</userinput>
    </screen>
      </para>
    
      <para>
       To reload an archive file into a (freshly created) database named
       <literal>newdb</>:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput>
    </screen>
      </para>
    
      <para>
       To dump a single table named <literal>mytab</>:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -t mytab mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To dump all tables whose names start with <literal>emp</> in the
       <literal>detroit</> schema, except for the table named
       <literal>employee_log</literal>:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To dump all schemas whose names start with <literal>east</> or
       <literal>west</> and end in <literal>gsm</>, excluding any schemas whose
       names contain the word <literal>test</>:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       The same, using regular expression notation to consolidate the switches:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -n '(east|west)*gsm' -N '*test*' mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To dump all database objects except for tables whose names begin with
       <literal>ts_</literal>:
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -T 'ts_*' mydb &gt; db.sql</userinput>
    </screen>
      </para>
    
      <para>
       To specify an upper-case or mixed-case name in <option>-t</> and related
       switches, you need to double-quote the name; else it will be folded to
       lower case (see <xref
       linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">).  But
       double quotes are special to the shell, so in turn they must be quoted.
       Thus, to dump a single table with a mixed-case name, you need something
       like
    
    <screen>
    <prompt>$</prompt> <userinput>pg_dump -t '"MixedCaseName"' mydb &gt; mytab.sql</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>
      </simplelist>
     </refsect1>
    
    </refentry>