pg_dump.sgml 30.7 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.94 2007/02/01 04:39:33 neilc Exp $
3
PostgreSQL documentation
4 5 6
-->

<refentry id="APP-PGDUMP">
7
 <refmeta>
8
  <refentrytitle>pg_dump</refentrytitle>
9
  <manvolnum>1</manvolnum>
10 11
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>
12

13
 <refnamediv>
14 15
  <refname>pg_dump</refname>

16
  <refpurpose>
17
   extract a <productname>PostgreSQL</productname> database into a script file or other archive file
18 19
  </refpurpose>
 </refnamediv>
20

Peter Eisentraut's avatar
Peter Eisentraut committed
21 22 23 24
 <indexterm zone="app-pgdump">
  <primary>pg_dump</primary>
 </indexterm>

25
 <refsynopsisdiv>
26 27
  <cmdsynopsis>
   <command>pg_dump</command>
28
   <arg rep="repeat"><replaceable>option</replaceable></arg>
29
   <arg><replaceable>dbname</replaceable></arg>
30 31 32 33 34 35 36 37 38 39
  </cmdsynopsis>
 </refsynopsisdiv>


 <refsect1 id="pg-dump-description">
  <title>
   Description
  </title>

  <para>
40 41 42 43 44
   <application>pg_dump</application> is a utility for backing up a
   <productname>PostgreSQL</productname> database. It makes consistent
   backups even if the database is being used concurrently.
   <application>pg_dump</application> does not block other users
   accessing the database (readers or writers).
45 46 47
  </para>

  <para>
48 49
   Dumps can be output in script or archive file formats. Script
   dumps are plain-text files containing the SQL commands required
50
   to reconstruct the database to the state it was in at the time it was
51 52
   saved. To restore from such a script, feed it to <xref
   linkend="app-psql">. Script files
53
   can be used to reconstruct the database even on other machines and
54
   other architectures; with some modifications even on other SQL
55 56 57 58
   database products.
  </para>

  <para>
59 60
   The alternative archive file formats must be used with
   <xref linkend="app-pgrestore"> to rebuild the database.  They
61 62
   allow <application>pg_restore</application> to be selective about
   what is restored, or even to reorder the items prior to being
63 64
   restored.
   The archive file formats are designed to be portable across
65
   architectures.
66 67 68
  </para>

  <para>
69
   When used with one of the archive file formats and combined with
70 71
   <application>pg_restore</application>,
   <application>pg_dump</application> provides a flexible archival and
72
   transfer mechanism. <application>pg_dump</application> can be used to
73 74 75 76 77 78 79 80
   backup an entire database, then <application>pg_restore</application>
   can be used to examine the archive and/or select which parts of the
   database are to be restored. The most flexible output file format is
   the <quote>custom</quote> format (<option>-Fc</option>). It allows
   for selection and reordering of all archived items, and is compressed
   by default. The <application>tar</application> format
   (<option>-Ft</option>) is not compressed and it is not possible to
   reorder data when loading, but it is otherwise quite flexible;
81
   moreover, it can be manipulated with standard Unix tools such as
82
   <command>tar</command>.
83 84 85
  </para>

  <para>
86
   While running <application>pg_dump</application>, one should examine the
87 88 89 90
   output for any warnings (printed on standard error), especially in
   light of the limitations listed below.
  </para>

91
 </refsect1>
92

93 94
 <refsect1 id="pg-dump-options">
  <title>Options</title>
95

96
  <para>
97 98
    The following command-line options control the content and
    format of the output.
99 100 101 102 103 104

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">dbname</replaceable></term>
      <listitem>
       <para>
105 106 107 108
        Specifies the name of the database to be dumped.  If this is
        not specified, the environment variable
        <envar>PGDATABASE</envar> is used.  If that is not set, the
        user name specified for the connection is used.
109 110 111 112 113
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
114 115
      <term><option>-a</></term>
      <term><option>--data-only</></term>
116 117
      <listitem>
       <para>
118
        Dump only the data, not the schema (data definitions).
119
       </para>
120 121

       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
122
        This option is only meaningful for the plain-text format.  For
123
        the archive formats, you can specify the option when you
124 125
        call <command>pg_restore</command>.
       </para>
126 127 128
      </listitem>
     </varlistentry>

129 130 131 132 133 134 135 136 137 138 139 140 141
     <varlistentry>
      <term><option>-b</></term>
      <term><option>--blobs</></term>
      <listitem>
       <para>
        Include large objects in the dump.  This is the default behavior
        except when <option>--schema</>, <option>--table</>, or
        <option>--schema-only</> is specified, so the <option>-b</>
        switch is only useful to add large objects to selective dumps.
       </para>
      </listitem>
     </varlistentry>

142
     <varlistentry>
143 144
      <term><option>-c</option></term>
      <term><option>--clean</option></term>
145 146
      <listitem>
       <para>
147
        Output commands to clean (drop)
148
        database objects prior to (the commands for) creating them.
149 150 151
       </para>

       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
152
        This option is only meaningful for the plain-text format.  For
153
        the archive formats, you can specify the option when you
154
        call <command>pg_restore</command>.
155 156 157 158 159
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
160 161
      <term><option>-C</></term>
      <term><option>--create</></term>
162 163
      <listitem>
       <para>
164 165 166 167
        Begin the output with a command to create the
        database itself and reconnect to the created database.  (With a
        script of this form, it doesn't matter which database you connect
        to before running the script.)
168 169 170
       </para>

       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
171
        This option is only meaningful for the plain-text format.  For
172
        the archive formats, you can specify the option when you
173
        call <command>pg_restore</command>.
174 175 176 177
       </para>
      </listitem>
     </varlistentry>

178
     <varlistentry>
179 180
      <term><option>-d</option></term>
      <term><option>--inserts</option></term>
181 182
      <listitem>
       <para>
183 184 185
        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
186 187 188 189 190
        non-<productname>PostgreSQL</productname> databases.
        Also, since this option generates a separate command for each row,
        an error in reloading a row causes only that row to be lost rather
        than the entire table contents.
        Note that
191
        the restore might fail altogether if you have rearranged column order.
192 193
        The <option>-D</option> option is safe against column order changes,
        though even slower.
194 195 196 197 198
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
199 200 201
      <term><option>-D</option></term>
      <term><option>--column-inserts</option></term>
      <term><option>--attribute-inserts</option></term>
202 203
      <listitem>
       <para>
204 205 206 207 208 209 210
        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.
211 212 213
        Also, since this option generates a separate command for each row,
        an error in reloading a row causes only that row to be lost rather
        than the entire table contents.
214 215 216
       </para>
      </listitem>
     </varlistentry>
217

218 219 220 221 222 223 224 225 226 227 228
     <varlistentry>
      <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
      <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
      <listitem>
       <para>
        Create the dump in the specified character set encoding. By default,
        the dump is created in the database encoding.  (Another way to get the
        same result is to set the <envar>PGCLIENTENCODING</envar> environment
        variable to the desired dump encoding.)
       </para>
      </listitem>
Bruce Momjian's avatar
Bruce Momjian committed
229 230
     </varlistentry>

231
     <varlistentry>
232 233
      <term><option>-f <replaceable class="parameter">file</replaceable></option></term>
      <term><option>--file=<replaceable class="parameter">file</replaceable></option></term>
234 235
      <listitem>
       <para>
236 237
        Send output to the specified file.  If this is omitted, the
        standard output is used.
238 239 240 241 242
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
243 244
      <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
      <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
245 246
      <listitem>
       <para>
247
        Selects the format of the output.
248
        <replaceable>format</replaceable> can be one of the following:
249 250 251

       <variablelist>
        <varlistentry>
252
         <term><literal>p</></term>
253
         <term><literal>plain</></term>
254 255
         <listitem>
          <para>
256
           Output a plain-text <acronym>SQL</acronym> script file (the default).
257 258 259 260 261
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
262 263
         <term><literal>c</></term>
         <term><literal>custom</></term>
264 265
         <listitem>
          <para>
266 267 268 269
           Output a custom archive suitable for input into
           <application>pg_restore</application>. This is the most flexible
           format in that it allows reordering of loading data as well
           as object definitions. This format is also compressed by default.
270 271 272 273 274
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
275 276
         <term><literal>t</></term>
         <term><literal>tar</></term>
277 278
         <listitem>
          <para>
279 280 281 282 283
           Output a <command>tar</command> archive suitable for input into
           <application>pg_restore</application>. Using this archive format
           allows reordering and/or exclusion of database objects
           at the time the database is restored. It is also possible to limit
           which data is reloaded at restore time.
284 285 286 287 288
          </para>
         </listitem>
        </varlistentry>

       </variablelist>
289
       </para>
290 291 292

      </listitem>
     </varlistentry>
293

294
     <varlistentry>
295 296
      <term><option>-i</></term>
      <term><option>--ignore-version</></term>
297 298
      <listitem>
       <para>
299 300 301 302 303
        Ignore version mismatch between
        <application>pg_dump</application> and the database server.
       </para>

       <para>
304
        <application>pg_dump</application> can dump from servers running
Bruce Momjian's avatar
Bruce Momjian committed
305
        previous releases of <productname>PostgreSQL</>, but very old
306 307 308
        versions are not supported anymore (currently, those prior to 7.0).
        Dumping from a server newer than <application>pg_dump</application>
        is likely not to work at all.
Bruce Momjian's avatar
Bruce Momjian committed
309 310 311
        Use this option if you need to override the version check (and
        if <application>pg_dump</application> then fails, don't say
        you weren't warned).
312 313 314 315
       </para>
      </listitem>
     </varlistentry>

Bruce Momjian's avatar
Bruce Momjian committed
316
     <varlistentry>
317
      <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
Bruce Momjian's avatar
Bruce Momjian committed
318 319 320
      <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
      <listitem>
       <para>
321 322 323 324 325 326 327 328 329 330 331 332 333
        Dump only schemas matching <replaceable
        class="parameter">schema</replaceable>; this selects both the
        schema itself, and all its contained objects.  When this option is
        not specified, all non-system schemas in the target database will be
        dumped.  Multiple schemas can be
        selected by writing multiple <option>-n</> switches.  Also, the
        <replaceable class="parameter">schema</replaceable> parameter is
        interpreted as a pattern according to the same rules used by
        <application>psql</>'s <literal>\d</> commands (see <xref
        linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
        so multiple schemas can also be selected by writing wildcard characters
        in the pattern.  When using wildcards, be careful to quote the pattern
        if needed to prevent the shell from expanding the wildcards.
Bruce Momjian's avatar
Bruce Momjian committed
334 335 336 337
       </para>

       <note>
        <para>
338 339
         When <option>-n</> is specified, <application>pg_dump</application>
         makes no attempt to dump any other database objects that the selected
340
         schema(s) might depend upon. Therefore, there is no guarantee
341 342 343 344 345 346 347 348 349 350
         that the results of a specific-schema dump can be successfully
         restored by themselves into a clean database.
        </para>
       </note>

       <note>
        <para>
         Non-schema objects such as blobs are not dumped when <option>-n</> is
         specified.  You can add blobs back to the dump with the
         <option>--blobs</> switch.
Bruce Momjian's avatar
Bruce Momjian committed
351 352
        </para>
       </note>
353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375

      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
      <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
      <listitem>
       <para>
        Do not dump any schemas matching the <replaceable
        class="parameter">schema</replaceable> pattern.  The pattern is
        interpreted according to the same rules as for <option>-n</>.
        <option>-N</> can be given more than once to exclude schemas
        matching any of several patterns.
       </para>

       <para>
        When both <option>-n</> and <option>-N</> are given, the behavior
        is to dump just the schemas that match at least one <option>-n</>
        switch but no <option>-N</> switches.  If <option>-N</> appears
        without <option>-n</>, then schemas matching <option>-N</> are
        excluded from what is otherwise a normal dump.
       </para>
Bruce Momjian's avatar
Bruce Momjian committed
376 377 378
      </listitem>
     </varlistentry>

379
     <varlistentry>
380 381
      <term><option>-o</></term>
      <term><option>--oids</></term>
382 383
      <listitem>
       <para>
384 385 386 387 388
        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.
389 390 391 392 393
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
394 395
      <term><option>-O</></term>
      <term><option>--no-owner</option></term>
396 397
      <listitem>
       <para>
398
        Do not output commands to set
399 400
        ownership of objects to match the original database.
        By default, <application>pg_dump</application> issues
401
        <command>ALTER OWNER</> or
402 403 404 405 406 407 408
        <command>SET SESSION AUTHORIZATION</command>
        statements to set ownership of created database objects.
        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</>.
409 410 411
       </para>

       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
412
        This option is only meaningful for the plain-text format.  For
413
        the archive formats, you can specify the option when you
414
        call <command>pg_restore</command>.
415 416 417 418 419
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
420 421
      <term><option>-R</option></term>
      <term><option>--no-reconnect</option></term>
422 423
      <listitem>
       <para>
424
        This option is obsolete but still accepted for backwards
425
        compatibility.
426 427 428 429
       </para>
      </listitem>
     </varlistentry>

430
     <varlistentry>
431 432
      <term><option>-s</option></term>
      <term><option>--schema-only</option></term>
433 434
      <listitem>
       <para>
435
        Dump only the object definitions (schema), not data.
436 437 438 439
       </para>
      </listitem>
     </varlistentry>

440
     <varlistentry>
441 442
      <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
      <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
443 444
      <listitem>
       <para>
445
        Specify the superuser user name to use when disabling triggers.
446 447 448
        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.)
449 450 451 452 453
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
454 455
      <term><option>-t <replaceable class="parameter">table</replaceable></option></term>
      <term><option>--table=<replaceable class="parameter">table</replaceable></option></term>
456 457
      <listitem>
       <para>
458 459 460 461 462 463 464 465 466 467
        Dump only tables (or views or sequences) matching <replaceable
        class="parameter">table</replaceable>.  Multiple tables can be
        selected by writing multiple <option>-t</> switches.  Also, the
        <replaceable class="parameter">table</replaceable> parameter is
        interpreted as a pattern according to the same rules used by
        <application>psql</>'s <literal>\d</> commands (see <xref
        linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
        so multiple tables can also be selected by writing wildcard characters
        in the pattern.  When using wildcards, be careful to quote the pattern
        if needed to prevent the shell from expanding the wildcards.
Bruce Momjian's avatar
Bruce Momjian committed
468 469
       </para>

470
       <para>
471 472 473 474
        The <option>-n</> and <option>-N</> switches have no effect when
        <option>-t</> is used, because tables selected by <option>-t</> will
        be dumped regardless of those switches, and non-table objects will not
        be dumped.
475 476
       </para>

Bruce Momjian's avatar
Bruce Momjian committed
477 478
       <note>
        <para>
479 480
         When <option>-t</> is specified, <application>pg_dump</application>
         makes no attempt to dump any other database objects that the selected
481
         table(s) might depend upon. Therefore, there is no guarantee
482
         that the results of a specific-table dump can be successfully
Bruce Momjian's avatar
Bruce Momjian committed
483 484 485
         restored by themselves into a clean database.
        </para>
       </note>
486 487 488 489 490 491 492 493 494 495 496 497 498

       <note>
        <para>
         The behavior of the <option>-t</> switch is not entirely upward
         compatible with pre-8.2 <productname>PostgreSQL</productname>
         versions.  Formerly, writing <literal>-t tab</> would dump all
         tables named <literal>tab</>, but now it just dumps whichever one
         is visible in your default search path.  To get the old behavior
         you can write <literal>-t '*.tab'</>.  Also, you must write something
         like <literal>-t sch.tab</> to select a table in a particular schema,
         rather than the old locution of <literal>-n sch -t tab</>.
        </para>
       </note>
499 500 501
      </listitem>
     </varlistentry>

502 503 504 505 506
     <varlistentry>
      <term><option>-T <replaceable class="parameter">table</replaceable></option></term>
      <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term>
      <listitem>
       <para>
507 508 509 510 511
        Do not dump any tables matching the <replaceable
        class="parameter">table</replaceable> pattern.  The pattern is
        interpreted according to the same rules as for <option>-t</>.
        <option>-T</> can be given more than once to exclude tables
        matching any of several patterns.
512 513 514
       </para>

       <para>
515 516 517 518 519
        When both <option>-t</> and <option>-T</> are given, the behavior
        is to dump just the tables that match at least one <option>-t</>
        switch but no <option>-T</> switches.  If <option>-T</> appears
        without <option>-t</>, then tables matching <option>-T</> are
        excluded from what is otherwise a normal dump.
520 521 522 523
       </para>
      </listitem>
     </varlistentry>

524
     <varlistentry>
525 526
      <term><option>-v</></term>
      <term><option>--verbose</></term>
527 528
      <listitem>
       <para>
529 530
        Specifies verbose mode.  This will cause
        <application>pg_dump</application> to output detailed object
531
        comments and start/stop times to the dump file, and progress
532
        messages to standard error.
533 534 535 536 537
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
538 539 540
      <term><option>-x</></term>
      <term><option>--no-privileges</></term>
      <term><option>--no-acl</></term>
541 542
      <listitem>
       <para>
543
        Prevent dumping of access privileges (grant/revoke commands).
544 545 546 547 548
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
549
      <term><option>--disable-dollar-quoting</></term>
550 551
      <listitem>
       <para>
552 553
        This option disables the use of dollar quoting for function bodies,
        and forces them to be quoted using SQL standard string syntax.
554
       </para>
555 556
     </listitem>
    </varlistentry>
557

558 559 560 561 562
     <varlistentry>
      <term><option>--disable-triggers</></term>
      <listitem>
       <para>
        This option is only relevant when creating a data-only dump.
563 564 565 566 567
        It instructs <application>pg_dump</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.
568 569 570 571
       </para>

       <para>
        Presently, the commands emitted for <option>--disable-triggers</>
572 573 574
        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.
575 576 577 578
       </para>

       <para>
        This option is only meaningful for the plain-text format.  For
579
        the archive formats, you can specify the option when you
580 581 582 583 584
        call <command>pg_restore</command>.
       </para>
      </listitem>
     </varlistentry>

585 586 587 588
     <varlistentry>
      <term><option>--use-set-session-authorization</></term>
      <listitem>
       <para>
589 590 591
        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
592
        depending on the history of the objects in the dump, might not restore
593 594 595
        properly.  Also, a dump using <command>SET SESSION AUTHORIZATION</>
        will certainly require superuser privileges to restore correctly,
        whereas <command>ALTER OWNER</> requires lesser privileges.
596 597 598 599
       </para>
      </listitem>
     </varlistentry>

600
     <varlistentry>
601 602
      <term><option>-Z <replaceable class="parameter">0..9</replaceable></option></term>
      <term><option>--compress=<replaceable class="parameter">0..9</replaceable></option></term>
603 604
      <listitem>
       <para>
605 606 607
        Specify the compression level to use in archive formats that
        support compression.  (Currently only the custom archive
        format supports compression.)
608 609 610
       </para>
      </listitem>
     </varlistentry>
611 612
    </variablelist>
   </para>
613

614
   <para>
615
    The following command-line options control the database connection parameters.
616 617 618

    <variablelist>
     <varlistentry>
619 620
      <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
      <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
621 622
      <listitem>
       <para>
623 624 625 626 627
        Specifies the host name of the machine on which the 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.
628 629 630 631 632
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
633 634
      <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
      <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
635 636
      <listitem>
       <para>
637 638 639 640
        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.
641 642 643 644 645
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
646
      <term><option>-U <replaceable>username</replaceable></option></term>
647
      <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
648 649
      <listitem>
       <para>
650
        Connect as the given user.
651 652 653 654 655
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
656
      <term><option>-W</option></term>
657
      <term><option>--password</option></term>
658 659 660 661
      <listitem>
       <para>
        Force a password prompt.  This should happen automatically if
        the server requires password authentication.
662 663 664 665 666
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
667
 </refsect1>
668

669 670 671 672 673 674 675 676 677 678 679 680
 <refsect1>
  <title>Environment</title>

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

    <listitem>
     <para>
Bruce Momjian's avatar
Bruce Momjian committed
681
      Default connection parameters.
682 683 684 685 686 687
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

688 689 690
 <refsect1 id="app-pgdump-diagnostics">
  <title>Diagnostics</title>

691 692 693 694 695 696 697
  <para>
   <application>pg_dump</application> internally executes
   <command>SELECT</command> statements. If you have problems running
   <application>pg_dump</application>, make sure you are able to
   select information from the database using, for example, <xref
   linkend="app-psql">.
  </para>
698 699
 </refsect1>

700 701 702

 <refsect1 id="pg-dump-notes">
  <title>Notes</title>
703 704

  <para>
705
   If your database cluster has any local additions to the <literal>template1</> database,
706
   be careful to restore the output of <application>pg_dump</application> into a
707 708
   truly empty database; otherwise you are likely to get errors due to
   duplicate definitions of the added objects.  To make an empty database
709
   without any local additions, copy from <literal>template0</> not <literal>template1</>,
710 711
   for example:
<programlisting>
712
CREATE DATABASE foo WITH TEMPLATE template0;
713 714 715
</programlisting>
  </para>

716
  <para>
717
   <application>pg_dump</application> has a few limitations:
718 719

   <itemizedlist>
720 721
    <listitem>
     <para>
722 723 724 725 726
      When a data-only dump is chosen and the option
      <option>--disable-triggers</> is used,
      <application>pg_dump</application> emits commands to disable
      triggers on user tables before inserting the data and commands
      to re-enable them after the data has been inserted.  If the
727
      restore is stopped in the middle, the system catalogs might be
728
      left in the wrong state.
729 730 731
     </para>
    </listitem>

732 733
   </itemizedlist>
  </para>
734 735 736 737

  <para>
   Members of tar archives are limited to a size less than 8 GB.
   (This is an inherent limitation of the tar file format.)  Therefore
738
   this format cannot be used if the textual representation of any one table
739 740 741 742
   exceeds that size.  The total size of a tar archive and any of the
   other output formats is not limited, except possibly by the
   operating system.
  </para>
743 744

  <para>
745 746 747 748 749
   The dump file produced by <application>pg_dump</application> does
   not contain the statistics used by the optimizer to make query
   planning decisions.  Therefore, it is wise to run
   <command>ANALYZE</command> after restoring from a dump file to
   ensure good performance.
750 751
  </para>

752
  <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
753
   Because <application>pg_dump</application> is used to transfer data
754 755
   to newer versions of <productname>PostgreSQL</>, the output of
   <application>pg_dump</application> can be loaded into
756
   newer <productname>PostgreSQL</> databases.  It also can read older
757 758 759 760 761 762
   <productname>PostgreSQL</> databases.  However, it usually cannot
   read newer <productname>PostgreSQL</> databases or produce dump output
   that can be loaded into older database versions.  To do this, manual
   editing of the dump file might be required.
  </para>

763 764
 </refsect1>

765 766
 <refsect1 id="pg-dump-examples">
  <title>Examples</title>
767

768
  <para>
769 770 771 772 773 774 775 776 777 778
   To dump a database called <literal>mydb</> into a SQL-script file:
<screen>
<prompt>$</prompt> <userinput>pg_dump mydb &gt; db.sql</userinput>
</screen>
  </para>

  <para>
   To reload such a script into a (freshly created) database named
   <literal>newdb</>:

779
<screen>
780
<prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
781
</screen>
782 783 784
  </para>

  <para>
785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823
   To dump a database into a custom-format archive file:

<screen>
<prompt>$</prompt> <userinput>pg_dump -Fc mydb &gt; db.dump</userinput>
</screen>
  </para>

  <para>
   To reload an archive file into a (freshly created) database named
   <literal>newdb</>:

<screen>
<prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput>
</screen>
  </para>

  <para>
   To dump a single table named <literal>mytab</>:

<screen>
<prompt>$</prompt> <userinput>pg_dump -t mytab mydb &gt; db.sql</userinput>
</screen>
  </para>

  <para>
   To dump all tables whose names start with <literal>emp</> in the
   <literal>detroit</> schema, except for the table named
   <literal>employee_log</literal>:

<screen>
<prompt>$</prompt> <userinput>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb &gt; db.sql</userinput>
</screen>
  </para>

  <para>
   To dump all schemas whose names start with <literal>east</> or
   <literal>west</> and end in <literal>gsm</>, excluding any schemas whose
   names contain the word <literal>test</>:

824
<screen>
825
<prompt>$</prompt> <userinput>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb &gt; db.sql</userinput>
826
</screen>
827
  </para>
828 829

  <para>
830
   The same, using regular expression notation to consolidate the switches:
831

832
<screen>
833
<prompt>$</prompt> <userinput>pg_dump -n '(east|west)*gsm' -N '*test*' mydb &gt; db.sql</userinput>
834
</screen>
835 836 837
  </para>

  <para>
838 839
   To dump all database objects except for tables whose names begin with
   <literal>ts_</literal>:
840

841
<screen>
842
<prompt>$</prompt> <userinput>pg_dump -T 'ts_*' mydb &gt; db.sql</userinput>
843 844 845 846 847 848 849 850 851 852 853 854 855 856
</screen>
  </para>

  <para>
   To specify an upper-case or mixed-case name in <option>-t</> and related
   switches, you need to double-quote the name; else it will be folded to
   lower case (see <xref
   linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">).  But
   double quotes are special to the shell, so in turn they must be quoted.
   Thus, to dump a single table with a mixed-case name, you need something
   like

<screen>
<prompt>$</prompt> <userinput>pg_dump -t '"MixedCaseName"' mydb &gt; mytab.sql</userinput>
857
</screen>
858 859
  </para>

860
 </refsect1>
861

862 863 864 865
 <refsect1>
  <title>History</title>

  <para>
866
   The <application>pg_dump</application> utility first appeared in
867
   <application>Postgres95</application> release 0.02.  The
868
   non-plain-text output formats were introduced in
869
   <productname>PostgreSQL</productname> release 7.1.
870 871 872
  </para>
 </refsect1>

873 874 875 876 877 878 879
 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="app-pg-dumpall"></member>
   <member><xref linkend="app-pgrestore"></member>
   <member><xref linkend="app-psql"></member>
880
   <member>Environment Variables (<xref linkend="libpq-envars">)</member>
881 882 883
  </simplelist>
 </refsect1>

884
</refentry>