2 doc/src/sgml/ref/pg_dumpall.sgml
3 PostgreSQL documentation
6 <refentry id="APP-PG-DUMPALL">
8 <refentrytitle><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>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>-i</></term>
125 <term><option>--ignore-version</></term>
128 A deprecated option that is now ignored.
134 <term><option>-o</></term>
135 <term><option>--oids</></term>
138 Dump object identifiers (<acronym>OID</acronym>s) as part of the
139 data for every table. Use this option if your application references
141 columns in some way (e.g., in a foreign key constraint).
142 Otherwise, this option should not be used.
148 <term><option>-O</></term>
149 <term><option>--no-owner</option></term>
152 Do not output commands to set
153 ownership of objects to match the original database.
154 By default, <application>pg_dumpall</application> issues
155 <command>ALTER OWNER</> or
156 <command>SET SESSION AUTHORIZATION</command>
157 statements to set ownership of created schema elements.
159 will fail when the script is run unless it is started by a superuser
160 (or the same user that owns all of the objects in the script).
161 To make a script that can be restored by any user, but will give
162 that user ownership of all the objects, specify <option>-O</>.
168 <term><option>-r</option></term>
169 <term><option>--roles-only</option></term>
172 Dump only roles, no databases or tablespaces.
178 <term><option>-s</option></term>
179 <term><option>--schema-only</option></term>
182 Dump only the object definitions (schema), not data.
188 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
189 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
192 Specify the superuser user name to use when disabling triggers.
193 This is only relevant if <option>--disable-triggers</> is used.
194 (Usually, it's better to leave this out, and instead start the
195 resulting script as superuser.)
201 <term><option>-t</option></term>
202 <term><option>--tablespaces-only</option></term>
205 Dump only tablespaces, no databases or roles.
211 <term><option>-v</></term>
212 <term><option>--verbose</></term>
215 Specifies verbose mode. This will cause
216 <application>pg_dumpall</application> to output start/stop
217 times to the dump file, and progress messages to standard error.
218 It will also enable verbose output in <application>pg_dump</>.
224 <term><option>-V</></term>
225 <term><option>--version</></term>
228 Print the <application>pg_dumpall</application> version and exit.
234 <term><option>-x</></term>
235 <term><option>--no-privileges</></term>
236 <term><option>--no-acl</></term>
239 Prevent dumping of access privileges (grant/revoke commands).
245 <term><option>--binary-upgrade</option></term>
248 This option is for use by in-place upgrade utilities. Its use
249 for other purposes is not recommended or supported. The
250 behavior of the option may change in future releases without
257 <term><option>--column-inserts</option></term>
258 <term><option>--attribute-inserts</option></term>
261 Dump data as <command>INSERT</command> commands with explicit
262 column names (<literal>INSERT INTO
263 <replaceable>table</replaceable>
264 (<replaceable>column</replaceable>, ...) VALUES
265 ...</literal>). This will make restoration very slow; it is mainly
266 useful for making dumps that can be loaded into
267 non-<productname>PostgreSQL</productname> databases.
273 <term><option>--disable-dollar-quoting</></term>
276 This option disables the use of dollar quoting for function bodies,
277 and forces them to be quoted using SQL standard string syntax.
283 <term><option>--disable-triggers</></term>
286 This option is only relevant when creating a data-only dump.
287 It instructs <application>pg_dumpall</application> to include commands
288 to temporarily disable triggers on the target tables while
289 the data is reloaded. Use this if you have referential
290 integrity checks or other triggers on the tables that you
291 do not want to invoke during data reload.
295 Presently, the commands emitted for <option>--disable-triggers</>
296 must be done as superuser. So, you should also specify
297 a superuser name with <option>-S</>, or preferably be careful to
298 start the resulting script as a superuser.
304 <term><option>--inserts</option></term>
307 Dump data as <command>INSERT</command> commands (rather
308 than <command>COPY</command>). This will make restoration very slow;
309 it is mainly useful for making dumps that can be loaded into
310 non-<productname>PostgreSQL</productname> databases. Note that
311 the restore might fail altogether if you have rearranged column order.
312 The <option>--column-inserts</option> option is safer, though even
319 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
322 Do not wait forever to acquire shared table locks at the beginning of
323 the dump. Instead, fail if unable to lock a table within the specified
324 <replaceable class="parameter">timeout</>. The timeout may be
325 specified in any of the formats accepted by <command>SET
326 statement_timeout</>. Allowed values vary depending on the server
327 version you are dumping from, but an integer number of milliseconds
328 is accepted by all versions since 7.3. This option is ignored when
329 dumping from a pre-7.3 server.
335 <term><option>--no-security-labels</option></term>
338 Do not dump security labels.
344 <term><option>--no-tablespaces</option></term>
347 Do not output commands to create tablespaces nor select tablespaces
349 With this option, all objects will be created in whichever
350 tablespace is the default during restore.
356 <term><option>--no-unlogged-table-data</option></term>
359 Do not dump the contents of unlogged tables. This option has no
360 effect on whether or not the table definitions (schema) are dumped;
361 it only suppresses dumping the table data.
367 <term><option>--quote-all-identifiers</></term>
370 Force quoting of all identifiers. This may be useful when dumping a
371 database for migration to a future version that may have introduced
378 <term><option>--use-set-session-authorization</></term>
381 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
382 instead of <command>ALTER OWNER</> commands to determine object
383 ownership. This makes the dump more standards compatible, but
384 depending on the history of the objects in the dump, might not restore
391 <term><option>-?</></term>
392 <term><option>--help</></term>
395 Show help about <application>pg_dumpall</application> command line
405 The following command-line options control the database connection parameters.
409 <term><option>-h <replaceable>host</replaceable></option></term>
410 <term><option>--host=<replaceable>host</replaceable></option></term>
413 Specifies the host name of the machine on which the database
414 server is running. If the value begins with a slash, it is
415 used as the directory for the Unix domain socket. The default
416 is taken from the <envar>PGHOST</envar> environment variable,
417 if set, else a Unix domain socket connection is attempted.
423 <term><option>-l <replaceable>dbname</replaceable></option></term>
424 <term><option>--database=<replaceable>dbname</replaceable></option></term>
427 Specifies the name of the database to connect to to dump global
428 objects and discover what other databases should be dumped. If
429 not specified, the <literal>postgres</literal> database will be used,
430 and if that does not exist, <literal>template1</literal> will be used.
436 <term><option>-p <replaceable>port</replaceable></option></term>
437 <term><option>--port=<replaceable>port</replaceable></option></term>
440 Specifies the TCP port or local Unix domain socket file
441 extension on which the server is listening for connections.
442 Defaults to the <envar>PGPORT</envar> environment variable, if
443 set, or a compiled-in default.
449 <term><option>-U <replaceable>username</replaceable></option></term>
450 <term><option>--username=<replaceable>username</replaceable></option></term>
453 User name to connect as.
459 <term><option>-w</></term>
460 <term><option>--no-password</></term>
463 Never issue a password prompt. If the server requires
464 password authentication and a password is not available by
465 other means such as a <filename>.pgpass</filename> file, the
466 connection attempt will fail. This option can be useful in
467 batch jobs and scripts where no user is present to enter a
474 <term><option>-W</option></term>
475 <term><option>--password</option></term>
478 Force <application>pg_dumpall</application> to prompt for a
479 password before connecting to a database.
483 This option is never essential, since
484 <application>pg_dumpall</application> will automatically prompt
485 for a password if the server demands password authentication.
486 However, <application>pg_dumpall</application> will waste a
487 connection attempt finding out that the server wants a password.
488 In some cases it is worth typing <option>-W</> to avoid the extra
493 Note that the password prompt will occur again for each database
494 to be dumped. Usually, it's better to set up a
495 <filename>~/.pgpass</> file than to rely on manual password entry.
501 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
504 Specifies a role name to be used to create the dump.
505 This option causes <application>pg_dumpall</> to issue a
506 <command>SET ROLE</> <replaceable class="parameter">rolename</>
507 command after connecting to the database. It is useful when the
508 authenticated user (specified by <option>-U</>) lacks privileges
509 needed by <application>pg_dumpall</>, but can switch to a role with
510 the required rights. Some installations have a policy against
511 logging in directly as a superuser, and use of this option allows
512 dumps to be made without violating the policy.
522 <title>Environment</title>
526 <term><envar>PGHOST</envar></term>
527 <term><envar>PGOPTIONS</envar></term>
528 <term><envar>PGPORT</envar></term>
529 <term><envar>PGUSER</envar></term>
533 Default connection parameters
540 This utility, like most other <productname>PostgreSQL</> utilities,
541 also uses the environment variables supported by <application>libpq</>
542 (see <xref linkend="libpq-envars">).
552 Since <application>pg_dumpall</application> calls
553 <application>pg_dump</application> internally, some diagnostic
554 messages will refer to <application>pg_dump</application>.
558 Once restored, it is wise to run <command>ANALYZE</> on each
559 database so the optimizer has useful statistics. You
560 can also run <command>vacuumdb -a -z</> to analyze all
565 <application>pg_dumpall</application> requires all needed
566 tablespace directories to exist before the restore; otherwise,
567 database creation will fail for databases in non-default
573 <refsect1 id="app-pg-dumpall-ex">
574 <title>Examples</title>
576 To dump all databases:
579 <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
584 To reload database(s) from this file, you can use:
586 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
588 (It is not important to which database you connect here since the
589 script file created by <application>pg_dumpall</application> will
590 contain the appropriate commands to create and connect to the saved
596 <title>See Also</title>
599 Check <xref linkend="app-pgdump"> for details on possible