2 $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.78 2009/03/04 11:57:00 petere Exp $
3 PostgreSQL documentation
6 <refentry id="APP-PG-DUMPALL">
8 <refentrytitle id="APP-PG-DUMPALL-TITLE"><application>pg_dumpall</application></refentrytitle>
9 <manvolnum>1</manvolnum>
10 <refmiscinfo>Application</refmiscinfo>
14 <refname>pg_dumpall</refname>
15 <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
18 <indexterm zone="app-pg-dumpall">
19 <primary>pg_dumpall</primary>
24 <command>pg_dumpall</command>
25 <arg rep="repeat"><replaceable>option</replaceable></arg>
29 <refsect1 id="app-pg-dumpall-description">
30 <title>Description</title>
33 <application>pg_dumpall</application> is a utility for writing out
34 (<quote>dumping</quote>) all <productname>PostgreSQL</> databases
35 of a cluster into one script file. The script file contains
36 <acronym>SQL</acronym> commands that can be used as input to <xref
37 linkend="app-psql"> to restore the databases. It does this by
38 calling <xref linkend="app-pgdump"> for each database in a cluster.
39 <application>pg_dumpall</application> also dumps global objects
40 that are common to all databases.
41 (<application>pg_dump</application> does not save these objects.)
42 This currently includes information about database users and
43 groups, tablespaces, and properties such as access permissions
44 that apply to databases as a whole.
48 Since <application>pg_dumpall</application> reads tables from all
49 databases you will most likely have to connect as a database
50 superuser in order to produce a complete dump. Also you will need
51 superuser privileges to execute the saved script in order to be
52 allowed to add users and groups, and to create databases.
56 The SQL script will be written to the standard output. Shell
57 operators should be used to redirect it into a file.
61 <application>pg_dumpall</application> needs to connect several
62 times to the <productname>PostgreSQL</productname> server (once per
63 database). If you use password authentication it will ask for
64 a password each time. It is convenient to have a
65 <filename>~/.pgpass</> file in such cases. See <xref
66 linkend="libpq-pgpass"> for more information.
72 <title>Options</title>
75 The following command-line options control the content and
80 <term><option>-a</></term>
81 <term><option>--data-only</></term>
84 Dump only the data, not the schema (data definitions).
90 <term><option>-c</option></term>
91 <term><option>--clean</option></term>
94 Include SQL commands to clean (drop) databases before
95 recreating them. <command>DROP</> commands for roles and
96 tablespaces are added as well.
102 <term><option>-d</option></term>
103 <term><option>--inserts</option></term>
106 Dump data as <command>INSERT</command> commands (rather
107 than <command>COPY</command>). This will make restoration very slow;
108 it is mainly useful for making dumps that can be loaded into
109 non-<productname>PostgreSQL</productname> databases. Note that
110 the restore might fail altogether if you have rearranged column order.
111 The <option>-D</option> option is safer, though even slower.
117 <term><option>-D</option></term>
118 <term><option>--column-inserts</option></term>
119 <term><option>--attribute-inserts</option></term>
122 Dump data as <command>INSERT</command> commands with explicit
123 column names (<literal>INSERT INTO
124 <replaceable>table</replaceable>
125 (<replaceable>column</replaceable>, ...) VALUES
126 ...</literal>). This will make restoration very slow; it is mainly
127 useful for making dumps that can be loaded into
128 non-<productname>PostgreSQL</productname> databases.
134 <term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
135 <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
138 Send output to the specified file. If this is omitted, the
139 standard output is used.
145 <term><option>-g</option></term>
146 <term><option>--globals-only</option></term>
149 Dump only global objects (roles and tablespaces), no databases.
155 <term><option>-i</></term>
156 <term><option>--ignore-version</></term>
159 A deprecated option that is now ignored.
165 <term><option>-o</></term>
166 <term><option>--oids</></term>
169 Dump object identifiers (<acronym>OID</acronym>s) as part of the
170 data for every table. Use this option if your application references
172 columns in some way (e.g., in a foreign key constraint).
173 Otherwise, this option should not be used.
179 <term><option>-O</></term>
180 <term><option>--no-owner</option></term>
183 Do not output commands to set
184 ownership of objects to match the original database.
185 By default, <application>pg_dumpall</application> issues
186 <command>ALTER OWNER</> or
187 <command>SET SESSION AUTHORIZATION</command>
188 statements to set ownership of created schema elements.
190 will fail when the script is run unless it is started by a superuser
191 (or the same user that owns all of the objects in the script).
192 To make a script that can be restored by any user, but will give
193 that user ownership of all the objects, specify <option>-O</>.
199 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
202 Do not wait forever to acquire shared table locks at the beginning of
203 the dump. Instead fail if unable to lock a table within the specified
204 <replaceable class="parameter">timeout</>. The timeout may be
205 specified in any of the formats accepted by <command>SET
206 statement_timeout</>. (Allowed values vary depending on the server
207 version you are dumping from, but an integer number of milliseconds
208 is accepted by all versions since 7.3. This option is ignored when
209 dumping from a pre-7.3 server.)
215 <term><option>--no-tablespaces</option></term>
218 Do not output commands to create tablespaces nor select tablespaces
220 With this option, all objects will be created in whichever
221 tablespace is the default during restore.
227 <term><option>-r</option></term>
228 <term><option>--roles-only</option></term>
231 Dump only roles, no databases or tablespaces.
237 <term><option>-s</option></term>
238 <term><option>--schema-only</option></term>
241 Dump only the object definitions (schema), not data.
247 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
248 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
251 Specify the superuser user name to use when disabling triggers.
252 This is only relevant if <option>--disable-triggers</> is used.
253 (Usually, it's better to leave this out, and instead start the
254 resulting script as superuser.)
260 <term><option>-t</option></term>
261 <term><option>--tablespaces-only</option></term>
264 Dump only tablespaces, no databases or roles.
270 <term><option>-v</></term>
271 <term><option>--verbose</></term>
274 Specifies verbose mode. This will cause
275 <application>pg_dumpall</application> to output start/stop
276 times to the dump file, and progress messages to standard error.
277 It will also enable verbose output in <application>pg_dump</>.
283 <term><option>-x</></term>
284 <term><option>--no-privileges</></term>
285 <term><option>--no-acl</></term>
288 Prevent dumping of access privileges (grant/revoke commands).
294 <term><option>--binary-upgrade</option></term>
297 This option is for use by in-place upgrade utilities. Its use
298 for other purposes is not recommended or supported. The
299 behavior of the option may change in future releases without
306 <term><option>--disable-dollar-quoting</></term>
309 This option disables the use of dollar quoting for function bodies,
310 and forces them to be quoted using SQL standard string syntax.
316 <term><option>--disable-triggers</></term>
319 This option is only relevant when creating a data-only dump.
320 It instructs <application>pg_dumpall</application> to include commands
321 to temporarily disable triggers on the target tables while
322 the data is reloaded. Use this if you have referential
323 integrity checks or other triggers on the tables that you
324 do not want to invoke during data reload.
328 Presently, the commands emitted for <option>--disable-triggers</>
329 must be done as superuser. So, you should also specify
330 a superuser name with <option>-S</>, or preferably be careful to
331 start the resulting script as a superuser.
337 <term><option>--use-set-session-authorization</></term>
340 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
341 instead of <command>ALTER OWNER</> commands to determine object
342 ownership. This makes the dump more standards compatible, but
343 depending on the history of the objects in the dump, might not restore
353 The following command-line options control the database connection parameters.
357 <term><option>-h <replaceable>host</replaceable></option></term>
358 <term><option>--host=<replaceable>host</replaceable></option></term>
361 Specifies the host name of the machine on which the database
362 server is running. If the value begins with a slash, it is
363 used as the directory for the Unix domain socket. The default
364 is taken from the <envar>PGHOST</envar> environment variable,
365 if set, else a Unix domain socket connection is attempted.
371 <term><option>-l <replaceable>dbname</replaceable></option></term>
372 <term><option>--database=<replaceable>dbname</replaceable></option></term>
375 Specifies the name of the database to connect to to dump global
376 objects and discover what other databases should be dumped. If
377 not specified, the <quote>postgres</quote> database will be used,
378 and if that does not exist, <quote>template1</quote> will be used.
384 <term><option>-p <replaceable>port</replaceable></option></term>
385 <term><option>--port=<replaceable>port</replaceable></option></term>
388 Specifies the TCP port or local Unix domain socket file
389 extension on which the server is listening for connections.
390 Defaults to the <envar>PGPORT</envar> environment variable, if
391 set, or a compiled-in default.
397 <term><option>-U <replaceable>username</replaceable></option></term>
398 <term><option>--username=<replaceable>username</replaceable></option></term>
401 User name to connect as.
407 <term><option>-w</></term>
408 <term><option>--no-password</></term>
411 Never issue a password prompt. If the server requires
412 password authentication and a password is not available by
413 other means such as a <filename>.pgpass</filename> file, the
414 connection attempt will fail. This option can be useful in
415 batch jobs and scripts where no user is present to enter a
422 <term><option>-W</option></term>
423 <term><option>--password</option></term>
426 Force <application>pg_dumpall</application> to prompt for a
427 password before connecting to a database.
431 This option is never essential, since
432 <application>pg_dumpall</application> will automatically prompt
433 for a password if the server demands password authentication.
434 However, <application>pg_dumpall</application> will waste a
435 connection attempt finding out that the server wants a password.
436 In some cases it is worth typing <option>-W</> to avoid the extra
441 Note that the password prompt will occur again for each database
442 to be dumped. Usually, it's better to set up a
443 <filename>~/.pgpass</> file than to rely on manual password entry.
449 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
452 Specifies a role name to be used to create the dump.
453 This option causes <application>pg_dumpall</> to issue a
454 <command>SET ROLE</> <replaceable class="parameter">rolename</>
455 command after connecting to the database. It is useful when the
456 authenticated user (specified by <option>-U</>) lacks privileges
457 needed by <application>pg_dumpall</>, but can switch to a role with
458 the required rights. Some installations have a policy against
459 logging in directly as a superuser, and use of this option allows
460 dumps to be made without violating the policy.
470 <title>Environment</title>
474 <term><envar>PGHOST</envar></term>
475 <term><envar>PGOPTIONS</envar></term>
476 <term><envar>PGPORT</envar></term>
477 <term><envar>PGUSER</envar></term>
481 Default connection parameters
488 This utility, like most other <productname>PostgreSQL</> utilities,
489 also uses the environment variables supported by <application>libpq</>
490 (see <xref linkend="libpq-envars">).
500 Since <application>pg_dumpall</application> calls
501 <application>pg_dump</application> internally, some diagnostic
502 messages will refer to <application>pg_dump</application>.
506 Once restored, it is wise to run <command>ANALYZE</> on each
507 database so the optimizer has useful statistics. You
508 can also run <command>vacuumdb -a -z</> to analyze all
513 <application>pg_dumpall</application> requires all needed
514 tablespace directories to exist before the restore or
515 database creation will fail for databases in non-default
521 <refsect1 id="app-pg-dumpall-ex">
522 <title>Examples</title>
524 To dump all databases:
527 <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
532 To reload this database use, for example:
534 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
536 (It is not important to which database you connect here since the
537 script file created by <application>pg_dumpall</application> will
538 contain the appropriate commands to create and connect to the saved
544 <title>See Also</title>
547 Check <xref linkend="app-pgdump"> for details on possible