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>--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>--no-security-label</option></term>
199
Do not dump security labels.
205
<term><option>--no-unlogged-table-data</option></term>
208
Do not dump the contents of unlogged tables. This option has no
209
effect on whether or not the table definitions (schema) are dumped;
210
it only suppresses dumping the table data.
216
<term><option>-r</option></term>
217
<term><option>--roles-only</option></term>
220
Dump only roles, no databases or tablespaces.
226
<term><option>-s</option></term>
227
<term><option>--schema-only</option></term>
230
Dump only the object definitions (schema), not data.
236
<term><option>-S <replaceable class="parameter">username</replaceable></option></term>
237
<term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
240
Specify the superuser user name to use when disabling triggers.
241
This is only relevant if <option>--disable-triggers</> is used.
242
(Usually, it's better to leave this out, and instead start the
243
resulting script as superuser.)
249
<term><option>-t</option></term>
250
<term><option>--tablespaces-only</option></term>
253
Dump only tablespaces, no databases or roles.
259
<term><option>-v</></term>
260
<term><option>--verbose</></term>
263
Specifies verbose mode. This will cause
264
<application>pg_dumpall</application> to output start/stop
265
times to the dump file, and progress messages to standard error.
266
It will also enable verbose output in <application>pg_dump</>.
272
<term><option>-V</></term>
273
<term><option>--version</></term>
276
Print the <application>pg_dumpall</application> version and exit.
282
<term><option>-x</></term>
283
<term><option>--no-privileges</></term>
284
<term><option>--no-acl</></term>
287
Prevent dumping of access privileges (grant/revoke commands).
293
<term><option>--binary-upgrade</option></term>
296
This option is for use by in-place upgrade utilities. Its use
297
for other purposes is not recommended or supported. The
298
behavior of the option may change in future releases without
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>--column-inserts</option></term>
321
<term><option>--attribute-inserts</option></term>
324
Dump data as <command>INSERT</command> commands with explicit
325
column names (<literal>INSERT INTO
326
<replaceable>table</replaceable>
327
(<replaceable>column</replaceable>, ...) VALUES
328
...</literal>). This will make restoration very slow; it is mainly
329
useful for making dumps that can be loaded into
330
non-<productname>PostgreSQL</productname> databases.
336
<term><option>--disable-dollar-quoting</></term>
339
This option disables the use of dollar quoting for function bodies,
340
and forces them to be quoted using SQL standard string syntax.
346
<term><option>--disable-triggers</></term>
349
This option is only relevant when creating a data-only dump.
350
It instructs <application>pg_dumpall</application> to include commands
351
to temporarily disable triggers on the target tables while
352
the data is reloaded. Use this if you have referential
353
integrity checks or other triggers on the tables that you
354
do not want to invoke during data reload.
358
Presently, the commands emitted for <option>--disable-triggers</>
359
must be done as superuser. So, you should also specify
360
a superuser name with <option>-S</>, or preferably be careful to
361
start the resulting script as a superuser.
367
<term><option>--use-set-session-authorization</></term>
370
Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
371
instead of <command>ALTER OWNER</> commands to determine object
372
ownership. This makes the dump more standards compatible, but
373
depending on the history of the objects in the dump, might not restore
380
<term><option>--quote-all-identifiers</></term>
383
Force quoting of all identifiers. This may be useful when dumping a
384
database for migration to a future version that may have introduced
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