<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.46 2004/07/19 21:39:46 momjian Exp $
+doc/src/sgml/ref/pg_dumpall.sgml
PostgreSQL documentation
-->
<refentry id="APP-PG-DUMPALL">
<refmeta>
- <refentrytitle
id="APP-PG-DUMPALL-TITLE"><application>pg_dumpall</application></refentrytitle>
+ <refentrytitle><application>pg_dumpall</application></refentrytitle>
<manvolnum>1</manvolnum>
<refmiscinfo>Application</refmiscinfo>
</refmeta>
<refsynopsisdiv>
<cmdsynopsis>
<command>pg_dumpall</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
<arg rep="repeat"><replaceable>option</replaceable></arg>
</cmdsynopsis>
</refsynopsisdiv>
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.
+ groups, tablespaces, and properties such as access permissions
+ that apply to databases as a whole.
</para>
<para>
</para>
<para>
- The SQL script will be written to the standard output. Shell
- operators should be used to redirect it into a file.
+ 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 and might be asking for
+ 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>$HOME/.pgpass</> file in such cases.
+ <filename>~/.pgpass</> file in such cases. See <xref
+ linkend="libpq-pgpass"> for more information.
</para>
</refsect1>
<title>Options</title>
<para>
- The following command-line options are used to control the content and
+ The following command-line options control the content and
format of the output.
<variablelist>
<term><option>--data-only</></term>
<listitem>
<para>
- Dump only the data, not the schema (data definitions).
+ Dump only the data, not the schema (data definitions).
</para>
</listitem>
</varlistentry>
<term><option>--clean</option></term>
<listitem>
<para>
- Include SQL commands to clean (drop) the databases before
- recreating them.
+ 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><option>-d</option></term>
- <term><option>--inserts</option></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 <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.
+ Send output to the specified file. If this is omitted, the
+ standard output is used.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><option>-D</option></term>
- <term><option>--column-inserts</option></term>
- <term><option>--attribute-inserts</option></term>
+ <term><option>-g</option></term>
+ <term><option>--globals-only</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.
+ Dump only global objects (roles and tablespaces), no databases.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><option>-g</option></term>
- <term><option>--globals-only</option></term>
+ <term><option>-i</></term>
+ <term><option>--ignore-version</></term>
<listitem>
<para>
- Dump only global objects (users and groups), no databases.
+ A deprecated option that is now ignored.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><option>-i</></term>
- <term><option>--ignore-version</></term>
+ <term><option>-o</></term>
+ <term><option>--oids</></term>
<listitem>
<para>
- Ignore version mismatch between
- <application>pg_dumpall</application> and the database server.
+ 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>
- <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).
+ 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><option>-o</></term>
- <term><option>--oids</></term>
+ <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></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.
+ 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>-O</></term>
- <term><option>--no-owner</option></term>
+ <term><option>--no-tablespaces</option></term>
<listitem>
<para>
- Do not output commands to set
- ownership of objects to match the original database.
- By default, <application>pg_dumpall</application> issues
- <command>SET SESSION AUTHORIZATION</command>
- statements to set ownership of created schema elements.
- These statements
- will fail when the script is run unless it is started by a superuser
- (or the same user that owns all of the objects in the script).
- To make a script that can be restored by any user, but will give
- that user ownership of all the objects, specify <option>-O</>.
+ 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>
+
+ <varlistentry>
+ <term><option>-r</option></term>
+ <term><option>--roles-only</option></term>
+ <listitem>
+ <para>
+ Dump only roles, no databases or tablespaces.
</para>
</listitem>
</varlistentry>
<term><option>--schema-only</option></term>
<listitem>
<para>
- Dump only the schema (data definitions), no data.
+ Dump only the object definitions (schema), not data.
</para>
</listitem>
</varlistentry>
<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.)
+ 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</option></term>
+ <term><option>--tablespaces-only</option></term>
+ <listitem>
+ <para>
+ Dump only tablespaces, no databases or roles.
</para>
</listitem>
</varlistentry>
<term><option>--verbose</></term>
<listitem>
<para>
- Specifies verbose mode. This will cause
- <application>pg_dumpall</application> to output start/stop
+ 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>
+ <varlistentry>
+ <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>
- Prevent dumping of access privileges (grant/revoke commands).
+ Prevent dumping of access privileges (grant/revoke commands).
+ </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>--inserts</option></term>
+ <listitem>
+ <para>
+ Dump data as <command>INSERT</command> commands (rather
+ than <command>COPY</command>). This will make restoration very slow;
+ it is mainly useful for making dumps that can be loaded into
+ non-<productname>PostgreSQL</productname> databases. Note that
+ the restore 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>-X disable-dollar-quoting</></term>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
</varlistentry>
<varlistentry>
- <term><option>-X disable-triggers</></term>
<term><option>--disable-triggers</></term>
<listitem>
<para>
</varlistentry>
<varlistentry>
- <term><option>-X use-set-session-authorization</></term>
<term><option>--use-set-session-authorization</></term>
<listitem>
<para>
- Output SQL standard SET SESSION AUTHORIZATION commands instead
- of OWNER TO commands. This makes the dump more standards compatible,
- but depending on the history of the objects in the dump, may not
- restore properly.
+ 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>
<variablelist>
<varlistentry>
- <term>-h <replaceable>host</replaceable></term>
+ <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 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.
+ 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>-p <replaceable>port</replaceable></term>
+ <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.
+ 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>
+ <term><option>-U <replaceable>username</replaceable></option></term>
+ <term><option>--username=<replaceable>username</replaceable></option></term>
<listitem>
<para>
- Connect as the given user.
+ User name to connect as.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>-W</term>
+ <term><option>-w</></term>
+ <term><option>--no-password</></term>
<listitem>
<para>
- Force a password prompt. This should happen automatically if
- the server requires password authentication.
+ 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>
<varlistentry>
<term><envar>PGHOST</envar></term>
+ <term><envar>PGOPTIONS</envar></term>
<term><envar>PGPORT</envar></term>
<term><envar>PGUSER</envar></term>
</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>
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>
</para>
<para>
- To reload this database use, for example:
+ To reload database(s) from this file, you can use:
<screen>
-<prompt>$</prompt> <userinput>psql -f db.out template1</userinput>
+<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
<title>See Also</title>
<para>
- <xref linkend="app-pgdump">. Check there for details on possible
+ Check <xref linkend="app-pgdump"> for details on possible
error conditions.
</para>
- </refsect1>
+ </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:
--->
projects / postgresql / blobdiff