2 doc/src/sgml/ref/pg_dumpall.sgml
3 PostgreSQL documentation
6 <refentry id="APP-PG-DUMPALL">
7 <indexterm zone="app-pg-dumpall">
8 <primary>pg_dumpall</primary>
12 <refentrytitle><application>pg_dumpall</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
18 <refname>pg_dumpall</refname>
19 <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
24 <command>pg_dumpall</command>
25 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
30 <refsect1 id="app-pg-dumpall-description">
31 <title>Description</title>
34 <application>pg_dumpall</application> is a utility for writing out
35 (<quote>dumping</quote>) all <productname>PostgreSQL</> databases
36 of a cluster into one script file. The script file contains
37 <acronym>SQL</acronym> commands that can be used as input to <xref
38 linkend="app-psql"> to restore the databases. It does this by
39 calling <xref linkend="app-pgdump"> for each database in a cluster.
40 <application>pg_dumpall</application> also dumps global objects
41 that are common to all databases.
42 (<application>pg_dump</application> does not save these objects.)
43 This currently includes information about database users and
44 groups, tablespaces, and properties such as access permissions
45 that apply to databases as a whole.
49 Since <application>pg_dumpall</application> reads tables from all
50 databases you will most likely have to connect as a database
51 superuser in order to produce a complete dump. Also you will need
52 superuser privileges to execute the saved script in order to be
53 allowed to add users and groups, and to create databases.
57 The SQL script will be written to the standard output. Use the
58 [-f|file] option or shell operators to redirect it into a file.
62 <application>pg_dumpall</application> needs to connect several
63 times to the <productname>PostgreSQL</productname> server (once per
64 database). If you use password authentication it will ask for
65 a password each time. It is convenient to have a
66 <filename>~/.pgpass</> file in such cases. See <xref
67 linkend="libpq-pgpass"> for more information.
73 <title>Options</title>
76 The following command-line options control the content and
81 <term><option>-a</></term>
82 <term><option>--data-only</></term>
85 Dump only the data, not the schema (data definitions).
91 <term><option>-c</option></term>
92 <term><option>--clean</option></term>
95 Include SQL commands to clean (drop) databases before
96 recreating them. <command>DROP</> commands for roles and
97 tablespaces are added as well.
103 <term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
104 <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
107 Send output to the specified file. If this is omitted, the
108 standard output is used.
114 <term><option>-g</option></term>
115 <term><option>--globals-only</option></term>
118 Dump only global objects (roles and tablespaces), no databases.
124 <term><option>-o</></term>
125 <term><option>--oids</></term>
128 Dump object identifiers (<acronym>OID</acronym>s) as part of the
129 data for every table. Use this option if your application references
131 columns in some way (e.g., in a foreign key constraint).
132 Otherwise, this option should not be used.
138 <term><option>-O</></term>
139 <term><option>--no-owner</option></term>
142 Do not output commands to set
143 ownership of objects to match the original database.
144 By default, <application>pg_dumpall</application> issues
145 <command>ALTER OWNER</> or
146 <command>SET SESSION AUTHORIZATION</command>
147 statements to set ownership of created schema elements.
149 will fail when the script is run unless it is started by a superuser
150 (or the same user that owns all of the objects in the script).
151 To make a script that can be restored by any user, but will give
152 that user ownership of all the objects, specify <option>-O</>.
158 <term><option>-r</option></term>
159 <term><option>--roles-only</option></term>
162 Dump only roles, no databases or tablespaces.
168 <term><option>-s</option></term>
169 <term><option>--schema-only</option></term>
172 Dump only the object definitions (schema), not data.
178 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
179 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
182 Specify the superuser user name to use when disabling triggers.
183 This is relevant only if <option>--disable-triggers</> is used.
184 (Usually, it's better to leave this out, and instead start the
185 resulting script as superuser.)
191 <term><option>-t</option></term>
192 <term><option>--tablespaces-only</option></term>
195 Dump only tablespaces, no databases or roles.
201 <term><option>-v</></term>
202 <term><option>--verbose</></term>
205 Specifies verbose mode. This will cause
206 <application>pg_dumpall</application> to output start/stop
207 times to the dump file, and progress messages to standard error.
208 It will also enable verbose output in <application>pg_dump</>.
214 <term><option>-V</></term>
215 <term><option>--version</></term>
218 Print the <application>pg_dumpall</application> version and exit.
224 <term><option>-x</></term>
225 <term><option>--no-privileges</></term>
226 <term><option>--no-acl</></term>
229 Prevent dumping of access privileges (grant/revoke commands).
235 <term><option>--binary-upgrade</option></term>
238 This option is for use by in-place upgrade utilities. Its use
239 for other purposes is not recommended or supported. The
240 behavior of the option may change in future releases without
247 <term><option>--column-inserts</option></term>
248 <term><option>--attribute-inserts</option></term>
251 Dump data as <command>INSERT</command> commands with explicit
252 column names (<literal>INSERT INTO
253 <replaceable>table</replaceable>
254 (<replaceable>column</replaceable>, ...) VALUES
255 ...</literal>). This will make restoration very slow; it is mainly
256 useful for making dumps that can be loaded into
257 non-<productname>PostgreSQL</productname> databases.
263 <term><option>--disable-dollar-quoting</></term>
266 This option disables the use of dollar quoting for function bodies,
267 and forces them to be quoted using SQL standard string syntax.
273 <term><option>--disable-triggers</></term>
276 This option is relevant only when creating a data-only dump.
277 It instructs <application>pg_dumpall</application> to include commands
278 to temporarily disable triggers on the target tables while
279 the data is reloaded. Use this if you have referential
280 integrity checks or other triggers on the tables that you
281 do not want to invoke during data reload.
285 Presently, the commands emitted for <option>--disable-triggers</>
286 must be done as superuser. So, you should also specify
287 a superuser name with <option>-S</>, or preferably be careful to
288 start the resulting script as a superuser.
294 <term><option>--if-exists</option></term>
297 Use conditional commands (i.e. add an <literal>IF EXISTS</literal>
298 clause) to clean databases and other objects. This option is not valid
299 unless <option>--clean</> is also specified.
305 <term><option>--inserts</option></term>
308 Dump data as <command>INSERT</command> commands (rather
309 than <command>COPY</command>). This will make restoration very slow;
310 it is mainly useful for making dumps that can be loaded into
311 non-<productname>PostgreSQL</productname> databases. Note that
312 the restore might fail altogether if you have rearranged column order.
313 The <option>--column-inserts</option> option is safer, though even
320 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
323 Do not wait forever to acquire shared table locks at the beginning of
324 the dump. Instead, fail if unable to lock a table within the specified
325 <replaceable class="parameter">timeout</>. The timeout may be
326 specified in any of the formats accepted by <command>SET
327 statement_timeout</>. Allowed values vary depending on the server
328 version you are dumping from, but an integer number of milliseconds
329 is accepted by all versions since 7.3. This option is ignored when
330 dumping from a pre-7.3 server.
336 <term><option>--no-publications</option></term>
339 Do not dump publications.
345 <term><option>--no-role-passwords</option></term>
348 Do not dump passwords for roles. When restored, roles will have a NULL
349 password and authentication will always fail until the password is reset.
350 Since password values aren't needed when this option is specified we
351 use the catalog view pg_roles in preference to pg_authid, since access
352 to pg_authid may be restricted by security policy.
358 <term><option>--no-security-labels</option></term>
361 Do not dump security labels.
367 <term><option>--no-subscriptions</option></term>
370 Do not dump subscriptions.
376 <term><option>--no-sync</option></term>
379 By default, <command>pg_dumpall</command> will wait for all files
380 to be written safely to disk. This option causes
381 <command>pg_dumpall</command> to return without waiting, which is
382 faster, but means that a subsequent operating system crash can leave
383 the dump corrupt. Generally, this option is useful for testing
384 but should not be used when dumping data from production installation.
390 <term><option>--no-tablespaces</option></term>
393 Do not output commands to create tablespaces nor select tablespaces
395 With this option, all objects will be created in whichever
396 tablespace is the default during restore.
402 <term><option>--no-unlogged-table-data</option></term>
405 Do not dump the contents of unlogged tables. This option has no
406 effect on whether or not the table definitions (schema) are dumped;
407 it only suppresses dumping the table data.
413 <term><option>--quote-all-identifiers</></term>
416 Force quoting of all identifiers. This option is recommended when
417 dumping a database from a server whose <productname>PostgreSQL</>
418 major version is different from <application>pg_dumpall</>'s, or when
419 the output is intended to be loaded into a server of a different
420 major version. By default, <application>pg_dumpall</> quotes only
421 identifiers that are reserved words in its own major version.
422 This sometimes results in compatibility issues when dealing with
423 servers of other versions that may have slightly different sets
424 of reserved words. Using <option>--quote-all-identifiers</> prevents
425 such issues, at the price of a harder-to-read dump script.
431 <term><option>--use-set-session-authorization</></term>
434 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
435 instead of <command>ALTER OWNER</> commands to determine object
436 ownership. This makes the dump more standards compatible, but
437 depending on the history of the objects in the dump, might not restore
444 <term><option>-?</></term>
445 <term><option>--help</></term>
448 Show help about <application>pg_dumpall</application> command line
458 The following command-line options control the database connection parameters.
462 <term><option>-d <replaceable class="parameter">connstr</replaceable></option></term>
463 <term><option>--dbname=<replaceable class="parameter">connstr</replaceable></option></term>
466 Specifies parameters used to connect to the server, as a connection
467 string. See <xref linkend="libpq-connstring"> for more information.
470 The option is called <literal>--dbname</> for consistency with other
471 client applications, but because <application>pg_dumpall</application>
472 needs to connect to many databases, database name in the connection
473 string will be ignored. Use <literal>-l</literal> option to specify
474 the name of the database used to dump global objects and to discover
475 what other databases should be dumped.
481 <term><option>-h <replaceable>host</replaceable></option></term>
482 <term><option>--host=<replaceable>host</replaceable></option></term>
485 Specifies the host name of the machine on which the database
486 server is running. If the value begins with a slash, it is
487 used as the directory for the Unix domain socket. The default
488 is taken from the <envar>PGHOST</envar> environment variable,
489 if set, else a Unix domain socket connection is attempted.
495 <term><option>-l <replaceable>dbname</replaceable></option></term>
496 <term><option>--database=<replaceable>dbname</replaceable></option></term>
499 Specifies the name of the database to connect to for dumping global
500 objects and discovering what other databases should be dumped. If
501 not specified, the <literal>postgres</literal> database will be used,
502 and if that does not exist, <literal>template1</literal> will be used.
508 <term><option>-p <replaceable>port</replaceable></option></term>
509 <term><option>--port=<replaceable>port</replaceable></option></term>
512 Specifies the TCP port or local Unix domain socket file
513 extension on which the server is listening for connections.
514 Defaults to the <envar>PGPORT</envar> environment variable, if
515 set, or a compiled-in default.
521 <term><option>-U <replaceable>username</replaceable></option></term>
522 <term><option>--username=<replaceable>username</replaceable></option></term>
525 User name to connect as.
531 <term><option>-w</></term>
532 <term><option>--no-password</></term>
535 Never issue a password prompt. If the server requires
536 password authentication and a password is not available by
537 other means such as a <filename>.pgpass</filename> file, the
538 connection attempt will fail. This option can be useful in
539 batch jobs and scripts where no user is present to enter a
546 <term><option>-W</option></term>
547 <term><option>--password</option></term>
550 Force <application>pg_dumpall</application> to prompt for a
551 password before connecting to a database.
555 This option is never essential, since
556 <application>pg_dumpall</application> will automatically prompt
557 for a password if the server demands password authentication.
558 However, <application>pg_dumpall</application> will waste a
559 connection attempt finding out that the server wants a password.
560 In some cases it is worth typing <option>-W</> to avoid the extra
565 Note that the password prompt will occur again for each database
566 to be dumped. Usually, it's better to set up a
567 <filename>~/.pgpass</> file than to rely on manual password entry.
573 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
576 Specifies a role name to be used to create the dump.
577 This option causes <application>pg_dumpall</> to issue a
578 <command>SET ROLE</> <replaceable class="parameter">rolename</>
579 command after connecting to the database. It is useful when the
580 authenticated user (specified by <option>-U</>) lacks privileges
581 needed by <application>pg_dumpall</>, but can switch to a role with
582 the required rights. Some installations have a policy against
583 logging in directly as a superuser, and use of this option allows
584 dumps to be made without violating the policy.
594 <title>Environment</title>
598 <term><envar>PGHOST</envar></term>
599 <term><envar>PGOPTIONS</envar></term>
600 <term><envar>PGPORT</envar></term>
601 <term><envar>PGUSER</envar></term>
605 Default connection parameters
612 This utility, like most other <productname>PostgreSQL</> utilities,
613 also uses the environment variables supported by <application>libpq</>
614 (see <xref linkend="libpq-envars">).
624 Since <application>pg_dumpall</application> calls
625 <application>pg_dump</application> internally, some diagnostic
626 messages will refer to <application>pg_dump</application>.
630 Once restored, it is wise to run <command>ANALYZE</> on each
631 database so the optimizer has useful statistics. You
632 can also run <command>vacuumdb -a -z</> to analyze all
637 <application>pg_dumpall</application> requires all needed
638 tablespace directories to exist before the restore; otherwise,
639 database creation will fail for databases in non-default
645 <refsect1 id="app-pg-dumpall-ex">
646 <title>Examples</title>
648 To dump all databases:
651 <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
656 To reload database(s) from this file, you can use:
658 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
660 (It is not important to which database you connect here since the
661 script file created by <application>pg_dumpall</application> will
662 contain the appropriate commands to create and connect to the saved
668 <title>See Also</title>
671 Check <xref linkend="app-pgdump"> for details on possible