2
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.48 2005-01-06 18:29:08 tgl 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, and access permissions that apply to databases as a whole.
47
Thus, <application>pg_dumpall</application> is an integrated
48
solution for backing up your databases. But note a limitation:
49
it cannot dump <quote>large objects</quote>, since
50
<application>pg_dump</application> cannot dump such objects into
51
text files. If you have databases containing large objects,
52
they should be dumped using one of <application>pg_dump</application>'s
53
non-text output modes.
57
Since <application>pg_dumpall</application> reads tables from all
58
databases you will most likely have to connect as a database
59
superuser in order to produce a complete dump. Also you will need
60
superuser privileges to execute the saved script in order to be
61
allowed to add users and groups, and to create databases.
65
The SQL script will be written to the standard output. Shell
66
operators should be used to redirect it into a file.
70
<application>pg_dumpall</application> needs to connect several
71
times to the <productname>PostgreSQL</productname> server (once per
72
database). If you use password authentication it is likely to ask for
73
a password each time. It is convenient to have a
74
<filename>~/.pgpass</> file in such cases. See <xref
75
linkend="libpq-pgpass"> for more information.
81
<title>Options</title>
84
The following command-line options control the content and
89
<term><option>-a</></term>
90
<term><option>--data-only</></term>
93
Dump only the data, not the schema (data definitions).
99
<term><option>-c</option></term>
100
<term><option>--clean</option></term>
103
Include SQL commands to clean (drop) the databases before
110
<term><option>-d</option></term>
111
<term><option>--inserts</option></term>
114
Dump data as <command>INSERT</command> commands (rather
115
than <command>COPY</command>). This will make restoration very slow;
116
it is mainly useful for making dumps that can be loaded into
117
non-<productname>PostgreSQL</productname> databases. Note that
118
the restore may fail altogether if you have rearranged column order.
119
The <option>-D</option> option is safer, though even slower.
125
<term><option>-D</option></term>
126
<term><option>--column-inserts</option></term>
127
<term><option>--attribute-inserts</option></term>
130
Dump data as <command>INSERT</command> commands with explicit
131
column names (<literal>INSERT INTO
132
<replaceable>table</replaceable>
133
(<replaceable>column</replaceable>, ...) VALUES
134
...</literal>). This will make restoration very slow; it is mainly
135
useful for making dumps that can be loaded into
136
non-<productname>PostgreSQL</productname> databases.
142
<term><option>-g</option></term>
143
<term><option>--globals-only</option></term>
146
Dump only global objects (users and groups), no databases.
152
<term><option>-i</></term>
153
<term><option>--ignore-version</></term>
156
Ignore version mismatch between
157
<application>pg_dumpall</application> and the database server.
161
<application>pg_dumpall</application> can handle databases
162
from previous releases of <productname>PostgreSQL</>, but very
163
old versions are not supported anymore (currently prior to
164
7.0). Use this option if you need to override the version
165
check (and if <application>pg_dumpall</application> then
166
fails, don't say you weren't warned).
172
<term><option>-o</></term>
173
<term><option>--oids</></term>
176
Dump object identifiers (<acronym>OID</acronym>s) as part of the
177
data for every table. Use this option if your application references
179
columns in some way (e.g., in a foreign key constraint).
180
Otherwise, this option should not be used.
186
<term><option>-O</></term>
187
<term><option>--no-owner</option></term>
190
Do not output commands to set
191
ownership of objects to match the original database.
192
By default, <application>pg_dumpall</application> issues
193
<command>ALTER OWNER</> or
194
<command>SET SESSION AUTHORIZATION</command>
195
statements to set ownership of created schema elements.
197
will fail when the script is run unless it is started by a superuser
198
(or the same user that owns all of the objects in the script).
199
To make a script that can be restored by any user, but will give
200
that user ownership of all the objects, specify <option>-O</>.
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>-v</></term>
230
<term><option>--verbose</></term>
233
Specifies verbose mode. This will cause
234
<application>pg_dumpall</application> to output start/stop
235
times to the dump file, and progress messages to standard error.
236
It will also enable verbose output in <application>pg_dump</>.
242
<term><option>-x</></term>
243
<term><option>--no-privileges</></term>
244
<term><option>--no-acl</></term>
247
Prevent dumping of access privileges (grant/revoke commands).
253
<term><option>-X disable-dollar-quoting</></term>
254
<term><option>--disable-dollar-quoting</></term>
257
This option disables the use of dollar quoting for function bodies,
258
and forces them to be quoted using SQL standard string syntax.
264
<term><option>-X disable-triggers</></term>
265
<term><option>--disable-triggers</></term>
268
This option is only relevant when creating a data-only dump.
269
It instructs <application>pg_dumpall</application> to include commands
270
to temporarily disable triggers on the target tables while
271
the data is reloaded. Use this if you have referential
272
integrity checks or other triggers on the tables that you
273
do not want to invoke during data reload.
277
Presently, the commands emitted for <option>--disable-triggers</>
278
must be done as superuser. So, you should also specify
279
a superuser name with <option>-S</>, or preferably be careful to
280
start the resulting script as a superuser.
286
<term><option>-X use-set-session-authorization</></term>
287
<term><option>--use-set-session-authorization</></term>
290
Output SQL standard SET SESSION AUTHORIZATION commands instead
291
of OWNER TO commands. This makes the dump more standards compatible,
292
but depending on the history of the objects in the dump, may not
302
The following command-line options control the database connection parameters.
306
<term>-h <replaceable>host</replaceable></term>
309
Specifies the host name of the machine on which the database
310
server is running. If the value begins with a slash, it is
311
used as the directory for the Unix domain socket. The default
312
is taken from the <envar>PGHOST</envar> environment variable,
313
if set, else a Unix domain socket connection is attempted.
319
<term>-p <replaceable>port</replaceable></term>
322
Specifies the TCP port or local Unix domain socket file
323
extension on which the server is listening for connections.
324
Defaults to the <envar>PGPORT</envar> environment variable, if
325
set, or a compiled-in default.
331
<term>-U <replaceable>username</replaceable></term>
334
Connect as the given user.
343
Force a password prompt. This should happen automatically if
344
the server requires password authentication.
354
<title>Environment</title>
358
<term><envar>PGHOST</envar></term>
359
<term><envar>PGPORT</envar></term>
360
<term><envar>PGUSER</envar></term>
364
Default connection parameters
376
Since <application>pg_dumpall</application> calls
377
<application>pg_dump</application> internally, some diagnostic
378
messages will refer to <application>pg_dump</application>.
382
Once restored, it is wise to run <command>ANALYZE</> on each
383
database so the optimizer has useful statistics. You
384
can also run <command>vacuumdb -a -z</> to analyze all
391
<refsect1 id="app-pg-dumpall-ex">
392
<title>Examples</title>
394
To dump all databases:
397
<prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
402
To reload this database use, for example:
404
<prompt>$</prompt> <userinput>psql -f db.out template1</userinput>
406
(It is not important to which database you connect here since the
407
script file created by <application>pg_dumpall</application> will
408
contain the appropriate commands to create and connect to the saved
414
<title>See Also</title>
417
<xref linkend="app-pgdump">. Check there for details on possible
424
<!-- Keep this comment at the end of the file
429
sgml-minimize-attributes:nil
430
sgml-always-quote-attributes:t
433
sgml-parent-document:nil
434
sgml-default-dtd-file:"../reference.ced"
435
sgml-exposed-tags:nil
436
sgml-local-catalogs:"/usr/lib/sgml/catalog"
437
sgml-local-ecat-files:nil