2 $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.80 2010/02/19 03:50:03 momjian 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>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>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
171 Do not wait forever to acquire shared table locks at the beginning of
172 the dump. Instead, fail if unable to lock a table within the specified
173 <replaceable class="parameter">timeout</>. The timeout may be
174 specified in any of the formats accepted by <command>SET
175 statement_timeout</>. Allowed values vary depending on the server
176 version you are dumping from, but an integer number of milliseconds
177 is accepted by all versions since 7.3. This option is ignored when
178 dumping from a pre-7.3 server.
184 <term><option>--no-tablespaces</option></term>
187 Do not output commands to create tablespaces nor select tablespaces
189 With this option, all objects will be created in whichever
190 tablespace is the default during restore.
196 <term><option>-r</option></term>
197 <term><option>--roles-only</option></term>
200 Dump only roles, no databases or tablespaces.
206 <term><option>-s</option></term>
207 <term><option>--schema-only</option></term>
210 Dump only the object definitions (schema), not data.
216 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
217 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
220 Specify the superuser user name to use when disabling triggers.
221 This is only relevant if <option>--disable-triggers</> is used.
222 (Usually, it's better to leave this out, and instead start the
223 resulting script as superuser.)
229 <term><option>-t</option></term>
230 <term><option>--tablespaces-only</option></term>
233 Dump only tablespaces, no databases or roles.
239 <term><option>-v</></term>
240 <term><option>--verbose</></term>
243 Specifies verbose mode. This will cause
244 <application>pg_dumpall</application> to output start/stop
245 times to the dump file, and progress messages to standard error.
246 It will also enable verbose output in <application>pg_dump</>.
252 <term><option>-x</></term>
253 <term><option>--no-privileges</></term>
254 <term><option>--no-acl</></term>
257 Prevent dumping of access privileges (grant/revoke commands).
263 <term><option>--binary-upgrade</option></term>
266 This option is for use by in-place upgrade utilities. Its use
267 for other purposes is not recommended or supported. The
268 behavior of the option may change in future releases without
275 <term><option>--inserts</option></term>
278 Dump data as <command>INSERT</command> commands (rather
279 than <command>COPY</command>). This will make restoration very slow;
280 it is mainly useful for making dumps that can be loaded into
281 non-<productname>PostgreSQL</productname> databases. Note that
282 the restore might fail altogether if you have rearranged column order.
283 The <option>--column-inserts</option> option is safer, though even
290 <term><option>--column-inserts</option></term>
291 <term><option>--attribute-inserts</option></term>
294 Dump data as <command>INSERT</command> commands with explicit
295 column names (<literal>INSERT INTO
296 <replaceable>table</replaceable>
297 (<replaceable>column</replaceable>, ...) VALUES
298 ...</literal>). This will make restoration very slow; it is mainly
299 useful for making dumps that can be loaded into
300 non-<productname>PostgreSQL</productname> databases.
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; otherwise,
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 database(s) from this file, you can use:
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