doc/src/sgml/ref/pg_dumpall.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="APP-PG-DUMPALL">
   7  <refmeta>
   8   <refentrytitle id="APP-PG-DUMPALL-TITLE"><application>pg_dumpall</application></refentrytitle>
   9   <manvolnum>1</manvolnum>
  10   <refmiscinfo>Application</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>pg_dumpall</refname>
  15   <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="app-pg-dumpall">
  19   <primary>pg_dumpall</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23   <cmdsynopsis>
  24    <command>pg_dumpall</command>
  25    <arg rep="repeat"><replaceable>option</replaceable></arg>
  26   </cmdsynopsis>
  27  </refsynopsisdiv>
  28
  29  <refsect1 id="app-pg-dumpall-description">
  30   <title>Description</title>
  31
  32   <para>
  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, tablespaces, and properties such as access permissions
  44    that apply to databases as a whole.
  45   </para>
  46
  47   <para>
  48    Since <application>pg_dumpall</application> reads tables from all
  49    databases you will most likely have to connect as a database
  50    superuser in order to produce a complete dump.  Also you will need
  51    superuser privileges to execute the saved script in order to be
  52    allowed to add users and groups, and to create databases.
  53   </para>
  54
  55   <para>
  56    The SQL script will be written to the standard output.  Shell
  57    operators should be used to redirect it into a file.
  58   </para>
  59
  60   <para>
  61   <application>pg_dumpall</application> needs to connect several
  62   times to the <productname>PostgreSQL</productname> server (once per
  63   database).  If you use password authentication it will ask for
  64   a password each time. It is convenient to have a
  65   <filename>~/.pgpass</> file in such cases. See <xref
  66   linkend="libpq-pgpass"> for more information.
  67   </para>
  68
  69  </refsect1>
  70
  71  <refsect1>
  72   <title>Options</title>
  73
  74    <para>
  75     The following command-line options control the content and
  76     format of the output.
  77
  78     <variablelist>
  79      <varlistentry>
  80       <term><option>-a</></term>
  81       <term><option>--data-only</></term>
  82       <listitem>
  83        <para>
  84         Dump only the data, not the schema (data definitions).
  85        </para>
  86       </listitem>
  87      </varlistentry>
  88
  89      <varlistentry>
  90       <term><option>-c</option></term>
  91       <term><option>--clean</option></term>
  92       <listitem>
  93        <para>
  94         Include SQL commands to clean (drop) databases before
  95         recreating them.  <command>DROP</> commands for roles and
  96         tablespaces are added as well.
  97        </para>
  98       </listitem>
  99      </varlistentry>
 100
 101      <varlistentry>
 102       <term><option>-d</option></term>
 103       <term><option>--inserts</option></term>
 104       <listitem>
 105        <para>
 106         Dump data as <command>INSERT</command> commands (rather
 107         than <command>COPY</command>).  This will make restoration very slow;
 108         it is mainly useful for making dumps that can be loaded into
 109         non-<productname>PostgreSQL</productname> databases.  Note that
 110         the restore might fail altogether if you have rearranged column order.
 111         The <option>-D</option> option is safer, though even slower.
 112        </para>
 113       </listitem>
 114      </varlistentry>
 115
 116      <varlistentry>
 117       <term><option>-D</option></term>
 118       <term><option>--column-inserts</option></term>
 119       <term><option>--attribute-inserts</option></term>
 120       <listitem>
 121        <para>
 122         Dump data as <command>INSERT</command> commands with explicit
 123         column names (<literal>INSERT INTO
 124         <replaceable>table</replaceable>
 125         (<replaceable>column</replaceable>, ...) VALUES
 126         ...</literal>).  This will make restoration very slow; it is mainly
 127         useful for making dumps that can be loaded into
 128         non-<productname>PostgreSQL</productname> databases.
 129        </para>
 130       </listitem>
 131      </varlistentry>
 132
 133      <varlistentry>
 134       <term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
 135       <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
 136       <listitem>
 137        <para>
 138         Send output to the specified file.  If this is omitted, the
 139         standard output is used.
 140        </para>
 141       </listitem>
 142      </varlistentry>
 143
 144      <varlistentry>
 145       <term><option>-g</option></term>
 146       <term><option>--globals-only</option></term>
 147       <listitem>
 148        <para>
 149         Dump only global objects (roles and tablespaces), no databases.
 150        </para>
 151       </listitem>
 152      </varlistentry>
 153
 154      <varlistentry>
 155       <term><option>-i</></term>
 156       <term><option>--ignore-version</></term>
 157       <listitem>
 158        <para>
 159         A deprecated option that is now ignored.
 160        </para>
 161       </listitem>
 162      </varlistentry>
 163
 164      <varlistentry>
 165       <term><option>-o</></term>
 166       <term><option>--oids</></term>
 167       <listitem>
 168        <para>
 169         Dump object identifiers (<acronym>OID</acronym>s) as part of the
 170         data for every table.  Use this option if your application references
 171         the <acronym>OID</>
 172         columns in some way (e.g., in a foreign key constraint).
 173         Otherwise, this option should not be used.
 174        </para>
 175       </listitem>
 176      </varlistentry>
 177
 178      <varlistentry>
 179       <term><option>-O</></term>
 180       <term><option>--no-owner</option></term>
 181       <listitem>
 182        <para>
 183         Do not output commands to set
 184         ownership of objects to match the original database.
 185         By default, <application>pg_dumpall</application> issues
 186         <command>ALTER OWNER</> or
 187         <command>SET SESSION AUTHORIZATION</command>
 188         statements to set ownership of created schema elements.
 189         These statements
 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</>.
 194        </para>
 195       </listitem>
 196      </varlistentry>
 197
 198      <varlistentry>
 199       <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
 200       <listitem>
 201        <para>
 202         Do not wait forever to acquire shared table locks at the beginning of
 203         the dump. Instead fail if unable to lock a table within the specified
 204         <replaceable class="parameter">timeout</>. The timeout may be
 205         specified in any of the formats accepted by <command>SET
 206         statement_timeout</>.  (Allowed values vary depending on the server
 207         version you are dumping from, but an integer number of milliseconds
 208         is accepted by all versions since 7.3.  This option is ignored when
 209         dumping from a pre-7.3 server.)
 210        </para>
 211       </listitem>
 212      </varlistentry>
 213
 214      <varlistentry>
 215       <term><option>--no-tablespaces</option></term>
 216       <listitem>
 217        <para>
 218         Do not output commands to create tablespaces nor select tablespaces
 219         for objects.
 220         With this option, all objects will be created in whichever
 221         tablespace is the default during restore.
 222        </para>
 223       </listitem>
 224      </varlistentry>
 225
 226      <varlistentry>
 227       <term><option>-r</option></term>
 228       <term><option>--roles-only</option></term>
 229       <listitem>
 230        <para>
 231         Dump only roles, no databases or tablespaces.
 232        </para>
 233       </listitem>
 234      </varlistentry>
 235
 236      <varlistentry>
 237       <term><option>-s</option></term>
 238       <term><option>--schema-only</option></term>
 239       <listitem>
 240        <para>
 241         Dump only the object definitions (schema), not data.
 242        </para>
 243       </listitem>
 244      </varlistentry>
 245
 246      <varlistentry>
 247       <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
 248       <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
 249       <listitem>
 250        <para>
 251         Specify the superuser user name to use when disabling triggers.
 252         This is only relevant if <option>--disable-triggers</> is used.
 253         (Usually, it's better to leave this out, and instead start the
 254         resulting script as superuser.)
 255        </para>
 256       </listitem>
 257      </varlistentry>
 258
 259      <varlistentry>
 260       <term><option>-t</option></term>
 261       <term><option>--tablespaces-only</option></term>
 262       <listitem>
 263        <para>
 264         Dump only tablespaces, no databases or roles.
 265        </para>
 266       </listitem>
 267      </varlistentry>
 268
 269      <varlistentry>
 270       <term><option>-v</></term>
 271       <term><option>--verbose</></term>
 272       <listitem>
 273        <para>
 274         Specifies verbose mode.  This will cause
 275         <application>pg_dumpall</application> to output start/stop
 276         times to the dump file, and progress messages to standard error.
 277         It will also enable verbose output in <application>pg_dump</>.
 278        </para>
 279       </listitem>
 280      </varlistentry>
 281
 282      <varlistentry>
 283       <term><option>-x</></term>
 284       <term><option>--no-privileges</></term>
 285       <term><option>--no-acl</></term>
 286       <listitem>
 287        <para>
 288         Prevent dumping of access privileges (grant/revoke commands).
 289        </para>
 290       </listitem>
 291      </varlistentry>
 292
 293      <varlistentry>
 294       <term><option>--disable-dollar-quoting</></term>
 295       <listitem>
 296        <para>
 297         This option disables the use of dollar quoting for function bodies,
 298         and forces them to be quoted using SQL standard string syntax.
 299        </para>
 300      </listitem>
 301     </varlistentry>
 302
 303      <varlistentry>
 304       <term><option>--disable-triggers</></term>
 305       <listitem>
 306        <para>
 307         This option is only relevant when creating a data-only dump.
 308         It instructs <application>pg_dumpall</application> to include commands
 309         to temporarily disable triggers on the target tables while
 310         the data is reloaded.  Use this if you have referential
 311         integrity checks or other triggers on the tables that you
 312         do not want to invoke during data reload.
 313        </para>
 314
 315        <para>
 316         Presently, the commands emitted for <option>--disable-triggers</>
 317         must be done as superuser.  So, you should also specify
 318         a superuser name with <option>-S</>, or preferably be careful to
 319         start the resulting script as a superuser.
 320        </para>
 321       </listitem>
 322      </varlistentry>
 323
 324      <varlistentry>
 325       <term><option>--use-set-session-authorization</></term>
 326       <listitem>
 327        <para>
 328         Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
 329         instead of <command>ALTER OWNER</> commands to determine object
 330         ownership.  This makes the dump more standards compatible, but
 331         depending on the history of the objects in the dump, might not restore
 332         properly.
 333        </para>
 334       </listitem>
 335      </varlistentry>
 336
 337     </variablelist>
 338    </para>
 339
 340   <para>
 341    The following command-line options control the database connection parameters.
 342
 343    <variablelist>
 344      <varlistentry>
 345       <term>-h <replaceable>host</replaceable></term>
 346       <term>--host=<replaceable>host</replaceable></term>
 347       <listitem>
 348        <para>
 349         Specifies the host name of the machine on which the database
 350         server is running.  If the value begins with a slash, it is
 351         used as the directory for the Unix domain socket.  The default
 352         is taken from the <envar>PGHOST</envar> environment variable,
 353         if set, else a Unix domain socket connection is attempted.
 354        </para>
 355       </listitem>
 356      </varlistentry>
 357
 358      <varlistentry>
 359       <term>-l <replaceable>dbname</replaceable></term>
 360       <term>--database=<replaceable>dbname</replaceable></term>
 361       <listitem>
 362        <para>
 363          Specifies the name of the database to connect to to dump global
 364          objects and discover what other databases should be dumped. If
 365          not specified, the <quote>postgres</quote> database will be used,
 366          and if that does not exist, <quote>template1</quote> will be used.
 367        </para>
 368       </listitem>
 369      </varlistentry>
 370
 371      <varlistentry>
 372       <term>-p <replaceable>port</replaceable></term>
 373       <term>--port=<replaceable>port</replaceable></term>
 374       <listitem>
 375        <para>
 376         Specifies the TCP port or local Unix domain socket file
 377         extension on which the server is listening for connections.
 378         Defaults to the <envar>PGPORT</envar> environment variable, if
 379         set, or a compiled-in default.
 380        </para>
 381       </listitem>
 382      </varlistentry>
 383
 384      <varlistentry>
 385       <term>-U <replaceable>username</replaceable></term>
 386       <term>--username=<replaceable>username</replaceable></term>
 387       <listitem>
 388        <para>
 389         User name to connect as.
 390        </para>
 391       </listitem>
 392      </varlistentry>
 393
 394      <varlistentry>
 395       <term>-W</term>
 396       <term>--password</term>
 397       <listitem>
 398        <para>
 399         Force <application>pg_dumpall</application> to prompt for a
 400         password before connecting to a database.
 401        </para>
 402
 403        <para>
 404         This option is never essential, since
 405         <application>pg_dumpall</application> will automatically prompt
 406         for a password if the server demands password authentication.
 407         However, <application>pg_dumpall</application> will waste a
 408         connection attempt finding out that the server wants a password.
 409         In some cases it is worth typing <option>-W</> to avoid the extra
 410         connection attempt.
 411        </para>
 412
 413        <para>
 414         Note that the password prompt will occur again for each database
 415         to be dumped.  Usually, it's better to set up a
 416         <filename>~/.pgpass</> file than to rely on manual password entry.
 417        </para>
 418       </listitem>
 419      </varlistentry>
 420    </variablelist>
 421   </para>
 422  </refsect1>
 423
 424
 425  <refsect1>
 426   <title>Environment</title>
 427
 428   <variablelist>
 429    <varlistentry>
 430     <term><envar>PGHOST</envar></term>
 431     <term><envar>PGPORT</envar></term>
 432     <term><envar>PGUSER</envar></term>
 433
 434     <listitem>
 435      <para>
 436       Default connection parameters
 437      </para>
 438     </listitem>
 439    </varlistentry>
 440   </variablelist>
 441
 442   <para>
 443    This utility, like most other <productname>PostgreSQL</> utilities,
 444    also uses the environment variables supported by <application>libpq</>
 445    (see <xref linkend="libpq-envars">).
 446   </para>
 447
 448  </refsect1>
 449
 450
 451  <refsect1>
 452   <title>Notes</title>
 453
 454   <para>
 455    Since <application>pg_dumpall</application> calls
 456    <application>pg_dump</application> internally, some diagnostic
 457    messages will refer to <application>pg_dump</application>.
 458   </para>
 459
 460   <para>
 461    Once restored, it is wise to run <command>ANALYZE</> on each
 462    database so the optimizer has useful statistics. You
 463    can also run <command>vacuumdb -a -z</> to analyze all
 464    databases.
 465   </para>
 466
 467   <para>
 468    <application>pg_dumpall</application> requires all needed
 469    tablespace directories to exist before the restore or
 470    database creation will fail for databases in non-default
 471    locations.
 472   </para>
 473
 474  </refsect1>
 475
 476
 477  <refsect1 id="app-pg-dumpall-ex">
 478   <title>Examples</title>
 479   <para>
 480    To dump all databases:
 481
 482 <screen>
 483 <prompt>$</prompt> <userinput>pg_dumpall &gt; db.out</userinput>
 484 </screen>
 485   </para>
 486
 487   <para>
 488    To reload this database use, for example:
 489 <screen>
 490 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
 491 </screen>
 492    (It is not important to which database you connect here since the
 493    script file created by <application>pg_dumpall</application> will
 494    contain the appropriate commands to create and connect to the saved
 495    databases.)
 496   </para>
 497  </refsect1>
 498
 499  <refsect1>
 500   <title>See Also</title>
 501
 502   <para>
 503     Check <xref linkend="app-pgdump"> for details on possible
 504     error conditions.
 505   </para>
 506  </refsect1>
 507
 508 </refentry>