2 $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.46 2004/07/19 21:39:46 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 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 and might be asking for
72 a password each time. It is convenient to have a
73 <filename>$HOME/.pgpass</> file in such cases.
79 <title>Options</title>
82 The following command-line options are used to control the content and
87 <term><option>-a</></term>
88 <term><option>--data-only</></term>
91 Dump only the data, not the schema (data definitions).
97 <term><option>-c</option></term>
98 <term><option>--clean</option></term>
101 Include SQL commands to clean (drop) the databases before
108 <term><option>-d</option></term>
109 <term><option>--inserts</option></term>
112 Dump data as <command>INSERT</command> commands (rather
113 than <command>COPY</command>). This will make restoration very
114 slow, but it makes the output more portable to other SQL database
121 <term><option>-D</option></term>
122 <term><option>--column-inserts</option></term>
123 <term><option>--attribute-inserts</option></term>
126 Dump data as <command>INSERT</command> commands with explicit
127 column names (<literal>INSERT INTO
128 <replaceable>table</replaceable>
129 (<replaceable>column</replaceable>, ...) VALUES
130 ...</literal>). This will make restoration very slow,
131 but it is necessary if you desire to rearrange column ordering.
137 <term><option>-g</option></term>
138 <term><option>--globals-only</option></term>
141 Dump only global objects (users and groups), no databases.
147 <term><option>-i</></term>
148 <term><option>--ignore-version</></term>
151 Ignore version mismatch between
152 <application>pg_dumpall</application> and the database server.
156 <application>pg_dumpall</application> can handle databases
157 from previous releases of <productname>PostgreSQL</>, but very
158 old versions are not supported anymore (currently prior to
159 7.0). Use this option if you need to override the version
160 check (and if <application>pg_dumpall</application> then
161 fails, don't say you weren't warned).
167 <term><option>-o</></term>
168 <term><option>--oids</></term>
171 Dump object identifiers (<acronym>OID</acronym>s) for every
172 table. Use this option if your application references the OID
173 columns in some way (e.g., in a foreign key constraint).
174 Otherwise, this option should not be used.
180 <term><option>-O</></term>
181 <term><option>--no-owner</option></term>
184 Do not output commands to set
185 ownership of objects to match the original database.
186 By default, <application>pg_dumpall</application> issues
187 <command>SET SESSION AUTHORIZATION</command>
188 statements to set ownership of created schema elements.
190 will fail when the script is run unless it is started by a superuser
191 (or the same user that owns all of the objects in the script).
192 To make a script that can be restored by any user, but will give
193 that user ownership of all the objects, specify <option>-O</>.
199 <term><option>-s</option></term>
200 <term><option>--schema-only</option></term>
203 Dump only the schema (data definitions), no data.
209 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
210 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
213 Specify the superuser user name to use when disabling triggers.
214 This is only relevant if <option>--disable-triggers</> is used.
215 (Usually, it's better to leave this out, and instead start the
216 resulting script as superuser.)
222 <term><option>-v</></term>
223 <term><option>--verbose</></term>
226 Specifies verbose mode. This will cause
227 <application>pg_dumpall</application> to output start/stop
228 times to the dump file, and progress messages to standard error.
229 It will also enable verbose output in <application>pg_dump</>.
235 <term><option>-x</></term>
236 <term><option>--no-privileges</></term>
237 <term><option>--no-acl</></term>
240 Prevent dumping of access privileges (grant/revoke commands).
246 <term><option>-X disable-dollar-quoting</></term>
247 <term><option>--disable-dollar-quoting</></term>
250 This option disables the use of dollar quoting for function bodies,
251 and forces them to be quoted using SQL standard string syntax.
257 <term><option>-X disable-triggers</></term>
258 <term><option>--disable-triggers</></term>
261 This option is only relevant when creating a data-only dump.
262 It instructs <application>pg_dumpall</application> to include commands
263 to temporarily disable triggers on the target tables while
264 the data is reloaded. Use this if you have referential
265 integrity checks or other triggers on the tables that you
266 do not want to invoke during data reload.
270 Presently, the commands emitted for <option>--disable-triggers</>
271 must be done as superuser. So, you should also specify
272 a superuser name with <option>-S</>, or preferably be careful to
273 start the resulting script as a superuser.
279 <term><option>-X use-set-session-authorization</></term>
280 <term><option>--use-set-session-authorization</></term>
283 Output SQL standard SET SESSION AUTHORIZATION commands instead
284 of OWNER TO commands. This makes the dump more standards compatible,
285 but depending on the history of the objects in the dump, may not
295 The following command-line options control the database connection parameters.
299 <term>-h <replaceable>host</replaceable></term>
302 Specifies the host name of the machine on which the database
303 server is running. If the value begins with a slash, it is
304 used as the directory for the Unix domain socket. The default
305 is taken from the <envar>PGHOST</envar> environment variable,
306 if set, else a Unix domain socket connection is attempted.
312 <term>-p <replaceable>port</replaceable></term>
315 Specifies the TCP port or local Unix domain socket file
316 extension on which the server is listening for connections.
317 Defaults to the <envar>PGPORT</envar> environment variable, if
318 set, or a compiled-in default.
324 <term>-U <replaceable>username</replaceable></term>
327 Connect as the given user.
336 Force a password prompt. This should happen automatically if
337 the server requires password authentication.
347 <title>Environment</title>
351 <term><envar>PGHOST</envar></term>
352 <term><envar>PGPORT</envar></term>
353 <term><envar>PGUSER</envar></term>
357 Default connection parameters
369 Since <application>pg_dumpall</application> calls
370 <application>pg_dump</application> internally, some diagnostic
371 messages will refer to <application>pg_dump</application>.
375 Once restored, it is wise to run <command>ANALYZE</> on each
376 database so the optimizer has useful statistics. You
377 can also run <command>vacuumdb -a -z</> to analyze all
384 <refsect1 id="app-pg-dumpall-ex">
385 <title>Examples</title>
387 To dump all databases:
390 <prompt>$</prompt> <userinput>pg_dumpall > db.out</userinput>
395 To reload this database use, for example:
397 <prompt>$</prompt> <userinput>psql -f db.out template1</userinput>
399 (It is not important to which database you connect here since the
400 script file created by <application>pg_dumpall</application> will
401 contain the appropriate commands to create and connect to the saved
407 <title>See Also</title>
410 <xref linkend="app-pgdump">. Check there for details on possible
417 <!-- Keep this comment at the end of the file
422 sgml-minimize-attributes:nil
423 sgml-always-quote-attributes:t
426 sgml-parent-document:nil
427 sgml-default-dtd-file:"../reference.ced"
428 sgml-exposed-tags:nil
429 sgml-local-catalogs:"/usr/lib/sgml/catalog"
430 sgml-local-ecat-files:nil