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>-f <replaceable class="parameter">filename</replaceable></option></term>
 103       <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
 104       <listitem>
 105        <para>
 106         Send output to the specified file.  If this is omitted, the
 107         standard output is used.
 108        </para>
 109       </listitem>
 110      </varlistentry>
 111
 112      <varlistentry>
 113       <term><option>-g</option></term>
 114       <term><option>--globals-only</option></term>
 115       <listitem>
 116        <para>
 117         Dump only global objects (roles and tablespaces), no databases.
 118        </para>
 119       </listitem>
 120      </varlistentry>
 121
 122      <varlistentry>
 123       <term><option>-i</></term>
 124       <term><option>--ignore-version</></term>
 125       <listitem>
 126        <para>
 127         A deprecated option that is now ignored.
 128        </para>
 129       </listitem>
 130      </varlistentry>
 131
 132      <varlistentry>
 133       <term><option>-o</></term>
 134       <term><option>--oids</></term>
 135       <listitem>
 136        <para>
 137         Dump object identifiers (<acronym>OID</acronym>s) as part of the
 138         data for every table.  Use this option if your application references
 139         the <acronym>OID</>
 140         columns in some way (e.g., in a foreign key constraint).
 141         Otherwise, this option should not be used.
 142        </para>
 143       </listitem>
 144      </varlistentry>
 145
 146      <varlistentry>
 147       <term><option>-O</></term>
 148       <term><option>--no-owner</option></term>
 149       <listitem>
 150        <para>
 151         Do not output commands to set
 152         ownership of objects to match the original database.
 153         By default, <application>pg_dumpall</application> issues
 154         <command>ALTER OWNER</> or
 155         <command>SET SESSION AUTHORIZATION</command>
 156         statements to set ownership of created schema elements.
 157         These statements
 158         will fail when the script is run unless it is started by a superuser
 159         (or the same user that owns all of the objects in the script).
 160         To make a script that can be restored by any user, but will give
 161         that user ownership of all the objects, specify <option>-O</>.
 162        </para>
 163       </listitem>
 164      </varlistentry>
 165
 166      <varlistentry>
 167       <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
 168       <listitem>
 169        <para>
 170         Do not wait forever to acquire shared table locks at the beginning of
 171         the dump. Instead fail if unable to lock a table within the specified
 172         <replaceable class="parameter">timeout</>. The timeout may be
 173         specified in any of the formats accepted by <command>SET
 174         statement_timeout</>.  (Allowed values vary depending on the server
 175         version you are dumping from, but an integer number of milliseconds
 176         is accepted by all versions since 7.3.  This option is ignored when
 177         dumping from a pre-7.3 server.)
 178        </para>
 179       </listitem>
 180      </varlistentry>
 181
 182      <varlistentry>
 183       <term><option>--no-tablespaces</option></term>
 184       <listitem>
 185        <para>
 186         Do not output commands to create tablespaces nor select tablespaces
 187         for objects.
 188         With this option, all objects will be created in whichever
 189         tablespace is the default during restore.
 190        </para>
 191       </listitem>
 192      </varlistentry>
 193
 194      <varlistentry>
 195       <term><option>-r</option></term>
 196       <term><option>--roles-only</option></term>
 197       <listitem>
 198        <para>
 199         Dump only roles, no databases or tablespaces.
 200        </para>
 201       </listitem>
 202      </varlistentry>
 203
 204      <varlistentry>
 205       <term><option>-s</option></term>
 206       <term><option>--schema-only</option></term>
 207       <listitem>
 208        <para>
 209         Dump only the object definitions (schema), not data.
 210        </para>
 211       </listitem>
 212      </varlistentry>
 213
 214      <varlistentry>
 215       <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
 216       <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
 217       <listitem>
 218        <para>
 219         Specify the superuser user name to use when disabling triggers.
 220         This is only relevant if <option>--disable-triggers</> is used.
 221         (Usually, it's better to leave this out, and instead start the
 222         resulting script as superuser.)
 223        </para>
 224       </listitem>
 225      </varlistentry>
 226
 227      <varlistentry>
 228       <term><option>-t</option></term>
 229       <term><option>--tablespaces-only</option></term>
 230       <listitem>
 231        <para>
 232         Dump only tablespaces, no databases or roles.
 233        </para>
 234       </listitem>
 235      </varlistentry>
 236
 237      <varlistentry>
 238       <term><option>-v</></term>
 239       <term><option>--verbose</></term>
 240       <listitem>
 241        <para>
 242         Specifies verbose mode.  This will cause
 243         <application>pg_dumpall</application> to output start/stop
 244         times to the dump file, and progress messages to standard error.
 245         It will also enable verbose output in <application>pg_dump</>.
 246        </para>
 247       </listitem>
 248      </varlistentry>
 249
 250      <varlistentry>
 251       <term><option>-x</></term>
 252       <term><option>--no-privileges</></term>
 253       <term><option>--no-acl</></term>
 254       <listitem>
 255        <para>
 256         Prevent dumping of access privileges (grant/revoke commands).
 257        </para>
 258       </listitem>
 259      </varlistentry>
 260
 261      <varlistentry>
 262       <term><option>--binary-upgrade</option></term>
 263       <listitem>
 264        <para>
 265         This option is for use by in-place upgrade utilities.  Its use
 266         for other purposes is not recommended or supported.  The
 267         behavior of the option may change in future releases without
 268         notice.
 269        </para>
 270       </listitem>
 271      </varlistentry>
 272
 273      <varlistentry>
 274       <term><option>--inserts</option></term>
 275       <listitem>
 276        <para>
 277         Dump data as <command>INSERT</command> commands (rather
 278         than <command>COPY</command>).  This will make restoration very slow;
 279         it is mainly useful for making dumps that can be loaded into
 280         non-<productname>PostgreSQL</productname> databases.  Note that
 281         the restore might fail altogether if you have rearranged column order.
 282         The <option>--column-inserts</option> option is safer, though even
 283         slower.
 284        </para>
 285       </listitem>
 286      </varlistentry>
 287
 288      <varlistentry>
 289       <term><option>--column-inserts</option></term>
 290       <term><option>--attribute-inserts</option></term>
 291       <listitem>
 292        <para>
 293         Dump data as <command>INSERT</command> commands with explicit
 294         column names (<literal>INSERT INTO
 295         <replaceable>table</replaceable>
 296         (<replaceable>column</replaceable>, ...) VALUES
 297         ...</literal>).  This will make restoration very slow; it is mainly
 298         useful for making dumps that can be loaded into
 299         non-<productname>PostgreSQL</productname> databases.
 300        </para>
 301       </listitem>
 302      </varlistentry>
 303
 304      <varlistentry>
 305       <term><option>--disable-dollar-quoting</></term>
 306       <listitem>
 307        <para>
 308         This option disables the use of dollar quoting for function bodies,
 309         and forces them to be quoted using SQL standard string syntax.
 310        </para>
 311      </listitem>
 312     </varlistentry>
 313
 314      <varlistentry>
 315       <term><option>--disable-triggers</></term>
 316       <listitem>
 317        <para>
 318         This option is only relevant when creating a data-only dump.
 319         It instructs <application>pg_dumpall</application> to include commands
 320         to temporarily disable triggers on the target tables while
 321         the data is reloaded.  Use this if you have referential
 322         integrity checks or other triggers on the tables that you
 323         do not want to invoke during data reload.
 324        </para>
 325
 326        <para>
 327         Presently, the commands emitted for <option>--disable-triggers</>
 328         must be done as superuser.  So, you should also specify
 329         a superuser name with <option>-S</>, or preferably be careful to
 330         start the resulting script as a superuser.
 331        </para>
 332       </listitem>
 333      </varlistentry>
 334
 335      <varlistentry>
 336       <term><option>--use-set-session-authorization</></term>
 337       <listitem>
 338        <para>
 339         Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
 340         instead of <command>ALTER OWNER</> commands to determine object
 341         ownership.  This makes the dump more standards compatible, but
 342         depending on the history of the objects in the dump, might not restore
 343         properly.
 344        </para>
 345       </listitem>
 346      </varlistentry>
 347
 348     </variablelist>
 349    </para>
 350
 351   <para>
 352    The following command-line options control the database connection parameters.
 353
 354    <variablelist>
 355      <varlistentry>
 356       <term><option>-h <replaceable>host</replaceable></option></term>
 357       <term><option>--host=<replaceable>host</replaceable></option></term>
 358       <listitem>
 359        <para>
 360         Specifies the host name of the machine on which the database
 361         server is running.  If the value begins with a slash, it is
 362         used as the directory for the Unix domain socket.  The default
 363         is taken from the <envar>PGHOST</envar> environment variable,
 364         if set, else a Unix domain socket connection is attempted.
 365        </para>
 366       </listitem>
 367      </varlistentry>
 368
 369      <varlistentry>
 370       <term><option>-l <replaceable>dbname</replaceable></option></term>
 371       <term><option>--database=<replaceable>dbname</replaceable></option></term>
 372       <listitem>
 373        <para>
 374          Specifies the name of the database to connect to to dump global
 375          objects and discover what other databases should be dumped. If
 376          not specified, the <quote>postgres</quote> database will be used,
 377          and if that does not exist, <quote>template1</quote> will be used.
 378        </para>
 379       </listitem>
 380      </varlistentry>
 381
 382      <varlistentry>
 383       <term><option>-p <replaceable>port</replaceable></option></term>
 384       <term><option>--port=<replaceable>port</replaceable></option></term>
 385       <listitem>
 386        <para>
 387         Specifies the TCP port or local Unix domain socket file
 388         extension on which the server is listening for connections.
 389         Defaults to the <envar>PGPORT</envar> environment variable, if
 390         set, or a compiled-in default.
 391        </para>
 392       </listitem>
 393      </varlistentry>
 394
 395      <varlistentry>
 396       <term><option>-U <replaceable>username</replaceable></option></term>
 397       <term><option>--username=<replaceable>username</replaceable></option></term>
 398       <listitem>
 399        <para>
 400         User name to connect as.
 401        </para>
 402       </listitem>
 403      </varlistentry>
 404
 405      <varlistentry>
 406       <term><option>-w</></term>
 407       <term><option>--no-password</></term>
 408       <listitem>
 409        <para>
 410         Never issue a password prompt.  If the server requires
 411         password authentication and a password is not available by
 412         other means such as a <filename>.pgpass</filename> file, the
 413         connection attempt will fail.  This option can be useful in
 414         batch jobs and scripts where no user is present to enter a
 415         password.
 416        </para>
 417       </listitem>
 418      </varlistentry>
 419
 420      <varlistentry>
 421       <term><option>-W</option></term>
 422       <term><option>--password</option></term>
 423       <listitem>
 424        <para>
 425         Force <application>pg_dumpall</application> to prompt for a
 426         password before connecting to a database.
 427        </para>
 428
 429        <para>
 430         This option is never essential, since
 431         <application>pg_dumpall</application> will automatically prompt
 432         for a password if the server demands password authentication.
 433         However, <application>pg_dumpall</application> will waste a
 434         connection attempt finding out that the server wants a password.
 435         In some cases it is worth typing <option>-W</> to avoid the extra
 436         connection attempt.
 437        </para>
 438
 439        <para>
 440         Note that the password prompt will occur again for each database
 441         to be dumped.  Usually, it's better to set up a
 442         <filename>~/.pgpass</> file than to rely on manual password entry.
 443        </para>
 444       </listitem>
 445      </varlistentry>
 446
 447      <varlistentry>
 448       <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
 449       <listitem>
 450        <para>
 451         Specifies a role name to be used to create the dump.
 452         This option causes <application>pg_dumpall</> to issue a
 453         <command>SET ROLE</> <replaceable class="parameter">rolename</>
 454         command after connecting to the database. It is useful when the
 455         authenticated user (specified by <option>-U</>) lacks privileges
 456         needed by <application>pg_dumpall</>, but can switch to a role with
 457         the required rights.  Some installations have a policy against
 458         logging in directly as a superuser, and use of this option allows
 459         dumps to be made without violating the policy.
 460        </para>
 461       </listitem>
 462      </varlistentry>
 463    </variablelist>
 464   </para>
 465  </refsect1>
 466
 467
 468  <refsect1>
 469   <title>Environment</title>
 470
 471   <variablelist>
 472    <varlistentry>
 473     <term><envar>PGHOST</envar></term>
 474     <term><envar>PGOPTIONS</envar></term>
 475     <term><envar>PGPORT</envar></term>
 476     <term><envar>PGUSER</envar></term>
 477
 478     <listitem>
 479      <para>
 480       Default connection parameters
 481      </para>
 482     </listitem>
 483    </varlistentry>
 484   </variablelist>
 485
 486   <para>
 487    This utility, like most other <productname>PostgreSQL</> utilities,
 488    also uses the environment variables supported by <application>libpq</>
 489    (see <xref linkend="libpq-envars">).
 490   </para>
 491
 492  </refsect1>
 493
 494
 495  <refsect1>
 496   <title>Notes</title>
 497
 498   <para>
 499    Since <application>pg_dumpall</application> calls
 500    <application>pg_dump</application> internally, some diagnostic
 501    messages will refer to <application>pg_dump</application>.
 502   </para>
 503
 504   <para>
 505    Once restored, it is wise to run <command>ANALYZE</> on each
 506    database so the optimizer has useful statistics. You
 507    can also run <command>vacuumdb -a -z</> to analyze all
 508    databases.
 509   </para>
 510
 511   <para>
 512    <application>pg_dumpall</application> requires all needed
 513    tablespace directories to exist before the restore, else
 514    database creation will fail for databases in non-default
 515    locations.
 516   </para>
 517  </refsect1>
 518
 519
 520  <refsect1 id="app-pg-dumpall-ex">
 521   <title>Examples</title>
 522   <para>
 523    To dump all databases:
 524
 525 <screen>
 526 <prompt>$</prompt> <userinput>pg_dumpall &gt; db.out</userinput>
 527 </screen>
 528   </para>
 529
 530   <para>
 531    To reload this database use, for example:
 532 <screen>
 533 <prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
 534 </screen>
 535    (It is not important to which database you connect here since the
 536    script file created by <application>pg_dumpall</application> will
 537    contain the appropriate commands to create and connect to the saved
 538    databases.)
 539   </para>
 540  </refsect1>
 541
 542  <refsect1>
 543   <title>See Also</title>
 544
 545   <para>
 546     Check <xref linkend="app-pgdump"> for details on possible
 547     error conditions.
 548   </para>
 549  </refsect1>
 550
 551 </refentry>