pg_dumpall.sgml 13.2 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.57 2006/09/16 00:30:19 momjian Exp $
3
PostgreSQL documentation
4 5
-->

6
<refentry id="APP-PG-DUMPALL">
7
 <refmeta>
8 9
  <refentrytitle id="APP-PG-DUMPALL-TITLE"><application>pg_dumpall</application></refentrytitle>
  <manvolnum>1</manvolnum>
10 11
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>
12

13
 <refnamediv>
14
  <refname>pg_dumpall</refname>
15
  <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
16 17
 </refnamediv>

Peter Eisentraut's avatar
Peter Eisentraut committed
18 19 20 21
 <indexterm zone="app-pg-dumpall">
  <primary>pg_dumpall</primary>
 </indexterm>

22 23 24
 <refsynopsisdiv>
  <cmdsynopsis>
   <command>pg_dumpall</command>
25
   <arg rep="repeat"><replaceable>option</replaceable></arg>
26 27
  </cmdsynopsis>
 </refsynopsisdiv>
28

29 30
 <refsect1 id="app-pg-dumpall-description">
  <title>Description</title>
31

32 33
  <para>
   <application>pg_dumpall</application> is a utility for writing out
Bruce Momjian's avatar
Bruce Momjian committed
34 35 36 37 38 39 40
   (<quote>dumping</quote>) all <productname>PostgreSQL</> databases
   of a cluster into one script file.  The script file contains
   <acronym>SQL</acronym> commands that can be used as input to <xref
   linkend="app-psql"> to restore the databases.  It does this by
   calling <xref linkend="app-pgdump"> for each database in a cluster.
   <application>pg_dumpall</application> also dumps global objects
   that are common to all databases.
41
   (<application>pg_dump</application> does not save these objects.)
42 43
   This currently includes information about database users and
   groups, and access permissions that apply to databases as a whole.
44
  </para>
45

46 47 48 49
  <para>
   Since <application>pg_dumpall</application> reads tables from all
   databases you will most likely have to connect as a database
   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 52
   allowed to add users and groups, and to create databases.
  </para>
53

54
  <para>
Bruce Momjian's avatar
Bruce Momjian committed
55
   The SQL script will be written to the standard output.  Shell
56 57
   operators should be used to redirect it into a file.
  </para>
58 59

  <para>
60
  <application>pg_dumpall</application> needs to connect several
61 62
  times to the <productname>PostgreSQL</productname> server (once per
  database).  If you use password authentication it is likely to ask for
63
  a password each time. It is convenient to have a
64 65
  <filename>~/.pgpass</> file in such cases. See <xref
  linkend="libpq-pgpass"> for more information.
66 67
  </para>

Bruce Momjian's avatar
Bruce Momjian committed
68 69 70 71
 </refsect1>

 <refsect1>
  <title>Options</title>
72

73
   <para>
74
    The following command-line options control the content and
75
    format of the output.
Bruce Momjian's avatar
Bruce Momjian committed
76

77
    <variablelist>
78 79 80 81 82
     <varlistentry>
      <term><option>-a</></term>
      <term><option>--data-only</></term>
      <listitem>
       <para>
83
        Dump only the data, not the schema (data definitions).
84 85 86 87
       </para>
      </listitem>
     </varlistentry>

88
     <varlistentry>
89 90
      <term><option>-c</option></term>
      <term><option>--clean</option></term>
91 92
      <listitem>
       <para>
93 94 95
        Include SQL commands to clean (drop) databases before
        recreating them.  <command>DROP</> commands for roles and
        tablespaces are added as well.
96 97 98 99 100 101 102 103 104
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-d</option></term>
      <term><option>--inserts</option></term>
      <listitem>
       <para>
105 106 107 108 109 110
        Dump data as <command>INSERT</command> commands (rather
        than <command>COPY</command>).  This will make restoration very slow;
        it is mainly useful for making dumps that can be loaded into
        non-<productname>PostgreSQL</productname> databases.  Note that
        the restore may fail altogether if you have rearranged column order.
        The <option>-D</option> option is safer, though even slower.
111 112 113 114 115 116 117 118 119 120
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-D</option></term>
      <term><option>--column-inserts</option></term>
      <term><option>--attribute-inserts</option></term>
      <listitem>
       <para>
121 122 123 124 125 126 127
        Dump data as <command>INSERT</command> commands with explicit
        column names (<literal>INSERT INTO
        <replaceable>table</replaceable>
        (<replaceable>column</replaceable>, ...) VALUES
        ...</literal>).  This will make restoration very slow; it is mainly
        useful for making dumps that can be loaded into
        non-<productname>PostgreSQL</productname> databases.
128 129 130 131 132
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
133 134
      <term><option>-g</option></term>
      <term><option>--globals-only</option></term>
135 136
      <listitem>
       <para>
137
        Dump only global objects (users and groups), no databases.
138 139 140 141 142 143 144 145 146 147 148
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-i</></term>
      <term><option>--ignore-version</></term>
      <listitem>
       <para>
        Ignore version mismatch between
        <application>pg_dumpall</application> and the database server.
149 150 151 152
       </para>

       <para>
        <application>pg_dumpall</application> can handle databases
Bruce Momjian's avatar
Bruce Momjian committed
153 154 155 156 157
        from previous releases of <productname>PostgreSQL</>, but very
        old versions are not supported anymore (currently prior to
        7.0).  Use this option if you need to override the version
        check (and if <application>pg_dumpall</application> then
        fails, don't say you weren't warned).
158 159 160 161 162 163 164 165 166
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-o</></term>
      <term><option>--oids</></term>
      <listitem>
       <para>
167 168 169 170 171
        Dump object identifiers (<acronym>OID</acronym>s) as part of the
        data for every table.  Use this option if your application references
        the <acronym>OID</>
        columns in some way (e.g., in a foreign key constraint).
        Otherwise, this option should not be used.
172 173 174 175
       </para>
      </listitem>
     </varlistentry>

176 177 178 179 180 181
     <varlistentry>
      <term><option>-O</></term>
      <term><option>--no-owner</option></term>
      <listitem>
       <para>
        Do not output commands to set
182 183 184 185 186 187 188 189 190 191
        ownership of objects to match the original database.
        By default, <application>pg_dumpall</application> issues
        <command>ALTER OWNER</> or 
        <command>SET SESSION AUTHORIZATION</command>
        statements to set ownership of created schema elements.
        These statements
        will fail when the script is run unless it is started by a superuser
        (or the same user that owns all of the objects in the script).
        To make a script that can be restored by any user, but will give
        that user ownership of all the objects, specify <option>-O</>.
192 193 194 195
       </para>
      </listitem>
     </varlistentry>

196 197 198 199 200
     <varlistentry>
      <term><option>-s</option></term>
      <term><option>--schema-only</option></term>
      <listitem>
       <para>
201
        Dump only the object definitions (schema), not data.
202 203 204 205
       </para>
      </listitem>
     </varlistentry>

206 207 208 209 210 211
     <varlistentry>
      <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
      <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
      <listitem>
       <para>
        Specify the superuser user name to use when disabling triggers.
212 213 214
        This is only relevant if <option>--disable-triggers</> is used.
        (Usually, it's better to leave this out, and instead start the
        resulting script as superuser.)
215 216 217 218
       </para>
      </listitem>
     </varlistentry>

219 220 221 222 223
     <varlistentry>
      <term><option>-v</></term>
      <term><option>--verbose</></term>
      <listitem>
       <para>
224 225
        Specifies verbose mode.  This will cause
        <application>pg_dumpall</application> to output start/stop
226 227
        times to the dump file, and progress messages to standard error.
        It will also enable verbose output in <application>pg_dump</>.
228 229 230
       </para>
      </listitem>
     </varlistentry>
231 232 233 234 235 236 237

     <varlistentry>
      <term><option>-x</></term>
      <term><option>--no-privileges</></term>
      <term><option>--no-acl</></term>
      <listitem>
       <para>
238
        Prevent dumping of access privileges (grant/revoke commands).
239 240 241
       </para>
      </listitem>
     </varlistentry>
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280

     <varlistentry>
      <term><option>-X disable-dollar-quoting</></term>
      <term><option>--disable-dollar-quoting</></term>
      <listitem>
       <para>
        This option disables the use of dollar quoting for function bodies,
        and forces them to be quoted using SQL standard string syntax.
       </para>
     </listitem>
    </varlistentry>

     <varlistentry>
      <term><option>-X disable-triggers</></term>
      <term><option>--disable-triggers</></term>
      <listitem>
       <para>
        This option is only relevant when creating a data-only dump.
        It instructs <application>pg_dumpall</application> to include commands
        to temporarily disable triggers on the target tables while
        the data is reloaded.  Use this if you have referential
        integrity checks or other triggers on the tables that you
        do not want to invoke during data reload.
       </para>

       <para>
        Presently, the commands emitted for <option>--disable-triggers</>
        must be done as superuser.  So, you should also specify
        a superuser name with <option>-S</>, or preferably be careful to
        start the resulting script as a superuser.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-X use-set-session-authorization</></term>
      <term><option>--use-set-session-authorization</></term>
      <listitem>
       <para>
281 282 283 284 285
        Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
        instead of <command>ALTER OWNER</> commands to determine object
        ownership.  This makes the dump more standards compatible, but
        depending on the history of the objects in the dump, may not restore
        properly.
286 287 288
       </para>
      </listitem>
     </varlistentry>
289

290 291 292 293 294 295 296 297 298 299 300
    </variablelist>
   </para>

  <para>
   The following command-line options control the database connection parameters.

   <variablelist>
     <varlistentry>
      <term>-h <replaceable>host</replaceable></term>
      <listitem>
       <para>
301 302 303 304 305
        Specifies the host name of the machine on which the database
        server is running.  If the value begins with a slash, it is
        used as the directory for the Unix domain socket.  The default
        is taken from the <envar>PGHOST</envar> environment variable,
        if set, else a Unix domain socket connection is attempted.
306 307 308 309 310 311 312 313
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-p <replaceable>port</replaceable></term>
      <listitem>
       <para>
314 315 316 317
        Specifies the TCP port or local Unix domain socket file
        extension on which the server is listening for connections.
        Defaults to the <envar>PGPORT</envar> environment variable, if
        set, or a compiled-in default.
318 319 320 321 322 323 324 325
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-U <replaceable>username</replaceable></term>
      <listitem>
       <para>
326
        Connect as the given user.
327 328 329 330 331 332 333 334
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-W</term>
      <listitem>
       <para>
335 336
        Force a password prompt.  This should happen automatically if
        the server requires password authentication.
337 338 339
       </para>
      </listitem>
     </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
340 341
   </variablelist>
  </para>
342
 </refsect1>
343

344 345 346 347 348 349 350 351 352 353 354 355

 <refsect1>
  <title>Environment</title>

  <variablelist>
   <varlistentry>
    <term><envar>PGHOST</envar></term>
    <term><envar>PGPORT</envar></term>
    <term><envar>PGUSER</envar></term>

    <listitem>
     <para>
356
      Default connection parameters
357 358 359 360 361 362 363
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>


364 365 366 367 368 369 370 371 372
 <refsect1>
  <title>Notes</title>

  <para>
   Since <application>pg_dumpall</application> calls
   <application>pg_dump</application> internally, some diagnostic
   messages will refer to <application>pg_dump</application>.
  </para>

373 374 375
  <para>
   Once restored, it is wise to run <command>ANALYZE</> on each
   database so the optimizer has useful statistics. You
376
   can also run <command>vacuumdb -a -z</> to analyze all
377
   databases.
378 379
  </para>

380 381 382 383 384 385 386
  <para>
   <application>pg_dumpall</application> requires all needed
   tablespace directories to exist before the restore or
   database creation will fail for databases in non-default
   locations.
  </para>

387 388 389
 </refsect1>


390 391
 <refsect1 id="app-pg-dumpall-ex">
  <title>Examples</title>
392
  <para>
393 394 395 396 397
   To dump all databases:

<screen>
<prompt>$</prompt> <userinput>pg_dumpall &gt; db.out</userinput>
</screen>
398
  </para>
399

400
  <para>
401 402
   To reload this database use, for example:
<screen>
403
<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
404 405 406 407
</screen>
   (It is not important to which database you connect here since the
   script file created by <application>pg_dumpall</application> will
   contain the appropriate commands to create and connect to the saved
408
   databases.)
409 410
  </para>
 </refsect1>
411

412 413
 <refsect1>
  <title>See Also</title>
414

415
  <para>
416
    <xref linkend="app-pgdump">.  Check there for details on possible
417
    error conditions.  Also see supported environment variables
418
    (<xref linkend="libpq-envars">).
419
  </para>
420 421
 </refsect1>   

422
</refentry>