<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.9 2000/07/21 18:51:24 momjian Exp $
-Postgres documentation
+doc/src/sgml/ref/pg_dumpall.sgml
+PostgreSQL documentation
-->
-<refentry id="APP-PGDUMPALL">
+<refentry id="APP-PG-DUMPALL">
<refmeta>
- <refentrytitle id="APP-PGDUMPALL-TITLE">
- <application>pg_dumpall</application>
- </refentrytitle>
+ <refentrytitle><application>pg_dumpall</application></refentrytitle>
+ <manvolnum>1</manvolnum>
<refmiscinfo>Application</refmiscinfo>
</refmeta>
+
<refnamediv>
- <refname>
- <application>pg_dumpall</application>
- </refname>
- <refpurpose>
- Extract all <productname>Postgres</productname> databases into a script file
- </refpurpose>
+ <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>
- <refsynopsisdivinfo>
- <date>1999-07-20</date>
- </refsynopsisdivinfo>
- <synopsis>
-pg_dumpall [ -h <replaceable class="parameter">host</replaceable> ] [ -p <replaceable class="parameter">port</replaceable> ] [ -a ] [ -d ] [ -D ] [ -O ] [ -s ] [ -u ] [ -v ] [ -x ]
- </synopsis>
-
- <refsect2 id="R2-APP-PG-DUMPALL-1">
- <refsect2info>
- <date>1998-10-04</date>
- </refsect2info>
- <title>
- Inputs
- </title>
+ <cmdsynopsis>
+ <command>pg_dumpall</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <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, tablespaces, and properties such as access permissions
+ that apply to databases as a whole.
+ </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. Use the
+ [-f|file] option or shell operators to redirect it into a file.
+ </para>
+
+ <para>
+ <application>pg_dumpall</application> needs to connect several
+ times to the <productname>PostgreSQL</productname> server (once per
+ database). If you use password authentication it will ask for
+ a password each time. It is convenient to have a
+ <filename>~/.pgpass</> file in such cases. See <xref
+ linkend="libpq-pgpass"> for more information.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Options</title>
+
<para>
- <application>pg_dumpall</application> accepts the following command line arguments:
+ The following command-line options control the content and
+ format of the output.
<variablelist>
<varlistentry>
- <term>-a</term>
+ <term><option>-a</></term>
+ <term><option>--data-only</></term>
<listitem>
<para>
- Dump out only the data, no schema (definitions).
+ Dump only the data, not the schema (data definitions).
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-d</term>
+ <term><option>-c</option></term>
+ <term><option>--clean</option></term>
<listitem>
<para>
- Dump data as proper insert strings.
+ Include SQL commands to clean (drop) databases before
+ recreating them. <command>DROP</> commands for roles and
+ tablespaces are added as well.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-D</term>
+ <term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
+ <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
<listitem>
<para>
- Dump data as inserts with attribute names
+ Send output to the specified file. If this is omitted, the
+ standard output is used.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-n</term>
+ <term><option>-g</option></term>
+ <term><option>--globals-only</option></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.
+ Dump only global objects (roles and tablespaces), no databases.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-o</term>
+ <term><option>-i</></term>
+ <term><option>--ignore-version</></term>
<listitem>
<para>
- Dump object identifiers (<acronym>OID</acronym>s) for every table.
+ A deprecated option that is now ignored.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-s</term>
+ <term><option>-o</></term>
+ <term><option>--oids</></term>
<listitem>
<para>
- Dump out only the schema (definitions), no data.
+ 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>-u</term>
+ <term><option>-O</></term>
+ <term><option>--no-owner</option></term>
<listitem>
<para>
- Use password authentication. Prompts for username and password.
+ Do not output commands to set
+ ownership of objects to match the original database.
+ By default, <application>pg_dumpall</application> issues
+ <command>ALTER OWNER</> or
+ <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>
</listitem>
</varlistentry>
<varlistentry>
- <term>-v</term>
+ <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
<listitem>
<para>
- Specifies verbose mode
+ 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>-x</term>
+ <term><option>--no-tablespaces</option></term>
<listitem>
<para>
- Prevent dumping ACLs (grant/revoke commands) and table ownership information.
+ Do not output commands to create tablespaces nor select tablespaces
+ for objects.
+ With this option, all objects will be created in whichever
+ tablespace is the default during restore.
</para>
</listitem>
</varlistentry>
- </variablelist>
- </para>
+ <varlistentry>
+ <term><option>-r</option></term>
+ <term><option>--roles-only</option></term>
+ <listitem>
+ <para>
+ Dump only roles, no databases or tablespaces.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- <application>pg_dumpall</application> also accepts
- the following command line arguments for connection parameters:
+ <varlistentry>
+ <term><option>-s</option></term>
+ <term><option>--schema-only</option></term>
+ <listitem>
+ <para>
+ Dump only the object definitions (schema), not data.
+ </para>
+ </listitem>
+ </varlistentry>
- <variablelist>
<varlistentry>
- <term>-h <replaceable class="parameter">host</replaceable></term>
+ <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
+ <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
<listitem>
<para>
- Specifies the hostname of the machine on which the
- <application>postmaster</application>
- is running. Defaults to using a local Unix domain socket
- rather than an IP connection..
+ 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>-p <replaceable class="parameter">port</replaceable></term>
+ <term><option>-t</option></term>
+ <term><option>--tablespaces-only</option></term>
<listitem>
<para>
- Specifies the Internet TCP/IP port or local Unix domain socket file
- extension on which the <application>postmaster</application>
- is listening for connections. The port number defaults to 5432,
- or the value of the <envar>PGPORT</envar>
- environment variable (if set).
+ Dump only tablespaces, no databases or roles.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-u</term>
+ <term><option>-v</></term>
+ <term><option>--verbose</></term>
<listitem>
<para>
- Use password authentication.
- Prompts for
- <replaceable class="parameter">username</replaceable>
- and <replaceable class="parameter">password</replaceable>.
+ Specifies verbose mode. This will cause
+ <application>pg_dumpall</application> to output start/stop
+ times to the dump file, and progress messages to standard error.
+ It will also enable verbose output in <application>pg_dump</>.
</para>
</listitem>
</varlistentry>
- </variablelist>
- </para>
- </refsect2>
-
- <refsect2 id="R2-APP-PG-DUMPALL-2">
- <refsect2info>
- <date>1998-10-04</date>
- </refsect2info>
- <title>
- Outputs
- </title>
- <para>
- <application>pg_dumpall</application> will create a file or
- write to <filename>stdout</filename>.
- <variablelist>
<varlistentry>
- <term><computeroutput>
-Connection to database 'template1' failed.
-connectDB() failed: Is the postmaster running and accepting connections at 'UNIX Socket' on port '<replaceable class="parameter">port</replaceable>'?
- </computeroutput></term>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>pg_dumpall</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-x</></term>
+ <term><option>--no-privileges</></term>
+ <term><option>--no-acl</></term>
<listitem>
<para>
- <application>pg_dumpall</application> could not attach to the
- <application>postmaster</application>
- process on the specified host and port. If you see this message,
- ensure that the <application>postmaster</application>
- is running on the proper host and that you have specified the proper
- port. If your site uses an authentication system, ensure that you
- have obtained the required authentication credentials.
+ Prevent dumping of access privileges (grant/revoke commands).
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><computeroutput>
-Connection to database '<replaceable class="parameter">dbname</replaceable>' failed.
-FATAL 1: SetUserId: user '<replaceable class="parameter">username</replaceable>' is not in 'pg_shadow'
- </computeroutput></term>
+ <term><option>--binary-upgrade</option></term>
<listitem>
<para>
- You do not have a valid entry in the relation <literal>pg_shadow</literal>
- and and will not be allowed to access <productname>Postgres</productname>.
- Contact your <productname>Postgres</productname> administrator.
+ 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><computeroutput>
-dumpSequence(<replaceable class="parameter">table</replaceable>): SELECT failed
- </computeroutput></term>
+ <term><option>--inserts</option></term>
<listitem>
<para>
- You do not have permission to read the database.
- Contact your <productname>Postgres</productname> site administrator.
+ 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 might fail altogether if you have rearranged column order.
+ The <option>--column-inserts</option> option is safer, though even
+ slower.
</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.
+ </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_dumpall</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>
+ </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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--quote-all-identifiers</></term>
+ <listitem>
+ <para>
+ Force quoting of all identifiers. This may be useful when dumping a
+ database for migration to a future version that may have introduced
+ additional keywords.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>pg_dumpall</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
- <note>
- <para>
- <application>pg_dumpall</application> internally executes
- <command>SELECT</command> statements. If you have problems running
- <application>pg_dumpall</application>,
- make sure you are able to select information from the database using, for
- example, <application>psql</application>.
- </para>
- </note>
- </refsect2>
- </refsynopsisdiv>
+ <para>
+ The following command-line options control the database connection parameters.
+
+ <variablelist>
+ <varlistentry>
+ <term><option>-h <replaceable>host</replaceable></option></term>
+ <term><option>--host=<replaceable>host</replaceable></option></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><option>-l <replaceable>dbname</replaceable></option></term>
+ <term><option>--database=<replaceable>dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the name of the database to connect to to dump global
+ objects and discover what other databases should be dumped. If
+ not specified, the <literal>postgres</literal> database will be used,
+ and if that does not exist, <literal>template1</literal> will be used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-p <replaceable>port</replaceable></option></term>
+ <term><option>--port=<replaceable>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>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_dumpall</application> to prompt for a
+ password before connecting to a database.
+ </para>
+
+ <para>
+ This option is never essential, since
+ <application>pg_dumpall</application> will automatically prompt
+ for a password if the server demands password authentication.
+ However, <application>pg_dumpall</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>
+
+ <para>
+ Note that the password prompt will occur again for each database
+ to be dumped. Usually, it's better to set up a
+ <filename>~/.pgpass</> file than to rely on manual password entry.
+ </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_dumpall</> 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_dumpall</>, 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>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>
- <refsect1 id="R1-APP-PG-DUMPALL-1">
- <refsect1info>
- <date>1998-10-04</date>
- </refsect1info>
- <title>
- Description
- </title>
<para>
- <application>pg_dumpall</application>
- is a utility for dumping out all Postgres databases into one file.
- It also dumps the pg_shadow table, which is global to all databases.
- <application>pg_dumpall</application> includes in this file the proper commands
- to automatically create each dumped database before loading.
+ 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>
+ <title>Notes</title>
+
<para>
- <application>pg_dumpall</application> takes all <application>pg_dump</application>
- options, but <option>-f</option>, <option>-t</option> and
- <replaceable class="parameter">dbname</replaceable>
- should be omitted.
+ Since <application>pg_dumpall</application> calls
+ <application>pg_dump</application> internally, some diagnostic
+ messages will refer to <application>pg_dump</application>.
</para>
+
<para>
- Refer to
- <xref endterm="app-pgdump-title" linkend="app-pgdump-title">
- for more information on this capability.
+ 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>
+
+ <para>
+ <application>pg_dumpall</application> requires all needed
+ tablespace directories to exist before the restore; otherwise,
+ database creation will fail for databases in non-default
+ locations.
</para>
</refsect1>
- <refsect1 id="R1-APP-PG-DUMPALL-2">
- <refsect1info>
- <date>1998-10-04</date>
- </refsect1info>
- <title>
- Usage
- </title>
+
+ <refsect1 id="app-pg-dumpall-ex">
+ <title>Examples</title>
<para>
To dump all databases:
- <programlisting>
-$ pg_dumpall > db.out
- </programlisting>
-
- <tip>
- <para>
- You can use most <application>pg_dump</application> options
- for <application>pg_dumpall</application>.
- </para>
- </tip>
+<screen>
+<prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
+</screen>
</para>
+
<para>
- To reload this database:
+ To reload database(s) from this file, you can use:
+<screen>
+<prompt>$</prompt> <userinput>psql -f db.out postgres</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>
- <programlisting>
-$ psql -e template1 < db.out
- </programlisting>
+ <refsect1>
+ <title>See Also</title>
+
+ <para>
+ Check <xref linkend="app-pgdump"> for details on possible
+ error conditions.
</para>
- <tip>
- <para>
- You can use most <application>psql</application> options
- when reloading.
- </para>
- </tip>
</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:
--->
+</refentry>
projects / postgresql / blobdiff