| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.58.2.3 2007/03/26 17:23:45 tgl 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> |
| 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 (once per |
| database). If you use password authentication it is likely to 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> |
| The following command-line options 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) 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> |
| <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 may fail altogether if you have rearranged column order. |
| The <option>-D</option> option is safer, though even slower. |
| </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; it is mainly |
| useful for making dumps that can be loaded into |
| non-<productname>PostgreSQL</productname> databases. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-g</option></term> |
| <term><option>--globals-only</option></term> |
| <listitem> |
| <para> |
| Dump only global objects (roles and tablespaces), 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) 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_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>-r</option></term> |
| <term><option>--resource-queues</option></term> |
| <listitem> |
| <para> |
| Dump <literal>resource queues</literal> and <literal>role</literal> |
| to <literal>resource queue</literal> associations. |
| </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> |
| </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>-v</></term> |
| <term><option>--verbose</></term> |
| <listitem> |
| <para> |
| 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>-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>--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, may not restore |
| properly. |
| </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> |
| |
| <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> |
| <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> |
| |
| <para> |
| <application>pg_dumpall</application> requires all needed |
| tablespace directories to exist before the restore or |
| database creation will fail for databases in non-default |
| locations. |
| </para> |
| |
| </refsect1> |
| |
| |
| <refsect1 id="app-pg-dumpall-ex"> |
| <title>Examples</title> |
| <para> |
| To dump all databases: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To reload this database use, for example: |
| <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> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <para> |
| Check <xref linkend="app-pgdump"> for details on possible |
| error conditions. |
| </para> |
| </refsect1> |
| |
| </refentry> |