2 $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.61 2007/01/25 02:46:33 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>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 Since <application>pg_dumpall</application> reads tables from all
48 databases you will most likely have to connect as a database
49 superuser in order to produce a complete dump. Also you will need
50 superuser privileges to execute the saved script in order to be
51 allowed to add users and groups, and to create databases.
55 The SQL script will be written to the standard output. Shell
56 operators should be used to redirect it into a file.
60 <application>pg_dumpall</application> needs to connect several
61 times to the <productname>PostgreSQL</productname> server (once per
62 database). If you use password authentication it is likely to ask for
63 a password each time. It is convenient to have a
64 <filename>~/.pgpass</> file in such cases. See <xref
65 linkend="libpq-pgpass"> for more information.
71 <title>Options</title>
74 The following command-line options control the content and
79 <term><option>-a</></term>
80 <term><option>--data-only</></term>
83 Dump only the data, not the schema (data definitions).
89 <term><option>-c</option></term>
90 <term><option>--clean</option></term>
93 Include SQL commands to clean (drop) databases before
94 recreating them. <command>DROP</> commands for roles and
95 tablespaces are added as well.
101 <term><option>-d</option></term>
102 <term><option>--inserts</option></term>
105 Dump data as <command>INSERT</command> commands (rather
106 than <command>COPY</command>). This will make restoration very slow;
107 it is mainly useful for making dumps that can be loaded into
108 non-<productname>PostgreSQL</productname> databases. Note that
109 the restore may fail altogether if you have rearranged column order.
110 The <option>-D</option> option is safer, though even slower.
116 <term><option>-D</option></term>
117 <term><option>--column-inserts</option></term>
118 <term><option>--attribute-inserts</option></term>
121 Dump data as <command>INSERT</command> commands with explicit
122 column names (<literal>INSERT INTO
123 <replaceable>table</replaceable>
124 (<replaceable>column</replaceable>, ...) VALUES
125 ...</literal>). This will make restoration very slow; it is mainly
126 useful for making dumps that can be loaded into
127 non-<productname>PostgreSQL</productname> databases.
133 <term><option>-g</option></term>
134 <term><option>--globals-only</option></term>
137 Dump only global objects (roles and tablespaces), no databases.
143 <term><option>-i</></term>
144 <term><option>--ignore-version</></term>
147 Ignore version mismatch between
148 <application>pg_dumpall</application> and the database server.
152 <application>pg_dumpall</application> can handle databases
153 from previous releases of <productname>PostgreSQL</>, but very
154 old versions are not supported anymore (currently prior to
155 7.0). Use this option if you need to override the version
156 check (and if <application>pg_dumpall</application> then
157 fails, don't say you weren't warned).
163 <term><option>-o</></term>
164 <term><option>--oids</></term>
167 Dump object identifiers (<acronym>OID</acronym>s) as part of the
168 data for every table. Use this option if your application references
170 columns in some way (e.g., in a foreign key constraint).
171 Otherwise, this option should not be used.
177 <term><option>-O</></term>
178 <term><option>--no-owner</option></term>
181 Do not output commands to set
182 ownership of objects to match the original database.
183 By default, <application>pg_dumpall</application> issues
184 <command>ALTER OWNER</> or
185 <command>SET SESSION AUTHORIZATION</command>
186 statements to set ownership of created schema elements.
188 will fail when the script is run unless it is started by a superuser
189 (or the same user that owns all of the objects in the script).
190 To make a script that can be restored by any user, but will give
191 that user ownership of all the objects, specify <option>-O</>.
197 <term><option>-r</option></term>
198 <term><option>--roles-only</option></term>
201 Dump only roles, no databases or tablespaces.
207 <term><option>-s</option></term>
208 <term><option>--schema-only</option></term>
211 Dump only the object definitions (schema), not data.
217 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
218 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
221 Specify the superuser user name to use when disabling triggers.
222 This is only relevant if <option>--disable-triggers</> is used.
223 (Usually, it's better to leave this out, and instead start the
224 resulting script as superuser.)
230 <term><option>-t</option></term>
231 <term><option>--tablespaces-only</option></term>
234 Dump only tablespaces, no databases or roles.
240 <term><option>-v</></term>
241 <term><option>--verbose</></term>
244 Specifies verbose mode. This will cause
245 <application>pg_dumpall</application> to output start/stop
246 times to the dump file, and progress messages to standard error.
247 It will also enable verbose output in <application>pg_dump</>.
253 <term><option>-x</></term>
254 <term><option>--no-privileges</></term>
255 <term><option>--no-acl</></term>
258 Prevent dumping of access privileges (grant/revoke commands).
264 <term><option>--disable-dollar-quoting</></term>
267 This option disables the use of dollar quoting for function bodies,
268 and forces them to be quoted using SQL standard string syntax.
274 <term><option>--disable-triggers</></term>
277 This option is only relevant when creating a data-only dump.
278 It instructs <application>pg_dumpall</application> to include commands
279 to temporarily disable triggers on the target tables while
280 the data is reloaded. Use this if you have referential
281 integrity checks or other triggers on the tables that you
282 do not want to invoke during data reload.
286 Presently, the commands emitted for <option>--disable-triggers</>
287 must be done as superuser. So, you should also specify
288 a superuser name with <option>-S</>, or preferably be careful to
289 start the resulting script as a superuser.
295 <term><option>--use-set-session-authorization</></term>
298 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
299 instead of <command>ALTER OWNER</> commands to determine object
300 ownership. This makes the dump more standards compatible, but
301 depending on the history of the objects in the dump, may not restore
311 The following command-line options control the database connection parameters.
315 <term>-h <replaceable>host</replaceable></term>
316 <term>--host=<replaceable>host</replaceable></term>
319 Specifies the host name of the machine on which the database
320 server is running. If the value begins with a slash, it is
321 used as the directory for the Unix domain socket. The default
322 is taken from the <envar>PGHOST</envar> environment variable,
323 if set, else a Unix domain socket connection is attempted.
329 <term>-l <replaceable>dbname</replaceable></term>
330 <term>--database=<replaceable>dbname</replaceable></term>
333 Specifies the name of the database to connect to to dump global
334 objects and discover what other databases should be dumped. If
335 not specified, the <quote>postgres</quote> database will be used,
336 and if that does not exist, <quote>template1</quote> will be used.
342 <term>-p <replaceable>port</replaceable></term>
343 <term>--port=<replaceable>port</replaceable></term>
346 Specifies the TCP port or local Unix domain socket file
347 extension on which the server is listening for connections.
348 Defaults to the <envar>PGPORT</envar> environment variable, if
349 set, or a compiled-in default.
355 <term>-U <replaceable>username</replaceable></term>
356 <term>--username=<replaceable>username</replaceable></term>
359 Connect as the given user.
366 <term>--password</term>
369 Force a password prompt. This should happen automatically if
370 the server requires password authentication.
380 <title>Environment</title>
384 <term><envar>PGHOST</envar></term>
385 <term><envar>PGPORT</envar></term>
386 <term><envar>PGUSER</envar></term>
390 Default connection parameters
402 Since <application>pg_dumpall</application> calls
403 <application>pg_dump</application> internally, some diagnostic
404 messages will refer to <application>pg_dump</application>.
408 Once restored, it is wise to run <command>ANALYZE</> on each
409 database so the optimizer has useful statistics. You
410 can also run <command>vacuumdb -a -z</> to analyze all
415 <application>pg_dumpall</application> requires all needed
416 tablespace directories to exist before the restore or
417 database creation will fail for databases in non-default
424 <refsect1 id="app-pg-dumpall-ex">
425 <title>Examples</title>
427 To dump all databases:
430 <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
435 To reload this database use, for example:
437 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
439 (It is not important to which database you connect here since the
440 script file created by <application>pg_dumpall</application> will
441 contain the appropriate commands to create and connect to the saved
447 <title>See Also</title>
450 <xref linkend="app-pgdump">. Check there for details on possible
451 error conditions. Also see supported environment variables
452 (<xref linkend="libpq-envars">).