I386LINUX/util/I386LINUX/doc/postgresql/html/app-pg-dumpall.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >pg_dumpall</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="PostgreSQL Client Applications"
  16 HREF="reference-client.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="pg_dump"
  19 HREF="app-pgdump.html"><LINK
  20 REL="NEXT"
  21 TITLE="pg_restore"
  22 HREF="app-pgrestore.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="REFENTRY"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="app-pgdump.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="app-pgdump.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 ></TD
  68 ><TD
  69 WIDTH="10%"
  70 ALIGN="right"
  71 VALIGN="top"
  72 ><A
  73 HREF="app-pgrestore.html"
  74 >Fast Forward</A
  75 ></TD
  76 ><TD
  77 WIDTH="10%"
  78 ALIGN="right"
  79 VALIGN="top"
  80 ><A
  81 HREF="app-pgrestore.html"
  82 ACCESSKEY="N"
  83 >Next</A
  84 ></TD
  85 ></TR
  86 ></TABLE
  87 ><HR
  88 ALIGN="LEFT"
  89 WIDTH="100%"></DIV
  90 ><H1
  91 ><A
  92 NAME="APP-PG-DUMPALL"
  93 ></A
  94 ><SPAN
  95 CLASS="APPLICATION"
  96 >pg_dumpall</SPAN
  97 ></H1
  98 ><DIV
  99 CLASS="REFNAMEDIV"
 100 ><A
 101 NAME="AEN44810"
 102 ></A
 103 ><H2
 104 >Name</H2
 105 >pg_dumpall&nbsp;--&nbsp;extract a <SPAN
 106 CLASS="PRODUCTNAME"
 107 >PostgreSQL</SPAN
 108 > database cluster into a script file</DIV
 109 ><A
 110 NAME="AEN44814"
 111 ></A
 112 ><DIV
 113 CLASS="REFSYNOPSISDIV"
 114 ><A
 115 NAME="AEN44816"
 116 ></A
 117 ><H2
 118 >Synopsis</H2
 119 ><P
 120 ><TT
 121 CLASS="COMMAND"
 122 >pg_dumpall</TT
 123 > [<VAR
 124 CLASS="REPLACEABLE"
 125 >option</VAR
 126 >...]</P
 127 ></DIV
 128 ><DIV
 129 CLASS="REFSECT1"
 130 ><A
 131 NAME="APP-PG-DUMPALL-DESCRIPTION"
 132 ></A
 133 ><H2
 134 >Description</H2
 135 ><P
 136 >   <SPAN
 137 CLASS="APPLICATION"
 138 >pg_dumpall</SPAN
 139 > is a utility for writing out
 140    (<SPAN
 141 CLASS="QUOTE"
 142 >"dumping"</SPAN
 143 >) all <SPAN
 144 CLASS="PRODUCTNAME"
 145 >PostgreSQL</SPAN
 146 > databases
 147    of a cluster into one script file.  The script file contains
 148    <ACRONYM
 149 CLASS="ACRONYM"
 150 >SQL</ACRONYM
 151 > commands that can be used as input to <A
 152 HREF="app-psql.html"
 153 ><SPAN
 154 CLASS="APPLICATION"
 155 >psql</SPAN
 156 ></A
 157 > to restore the databases.  It does this by
 158    calling <A
 159 HREF="app-pgdump.html"
 160 >pg_dump</A
 161 > for each database in a cluster.
 162    <SPAN
 163 CLASS="APPLICATION"
 164 >pg_dumpall</SPAN
 165 > also dumps global objects
 166    that are common to all databases.
 167    (<SPAN
 168 CLASS="APPLICATION"
 169 >pg_dump</SPAN
 170 > does not save these objects.)
 171    This currently includes information about database users and
 172    groups, and access permissions that apply to databases as a whole.
 173   </P
 174 ><P
 175 >   Thus, <SPAN
 176 CLASS="APPLICATION"
 177 >pg_dumpall</SPAN
 178 > is an integrated
 179    solution for backing up your databases.  But note a limitation:
 180    it cannot dump <SPAN
 181 CLASS="QUOTE"
 182 >"large objects"</SPAN
 183 >, since
 184    <SPAN
 185 CLASS="APPLICATION"
 186 >pg_dump</SPAN
 187 > cannot dump such objects into
 188    text files.  If you have databases containing large objects,
 189    they should be dumped using one of <SPAN
 190 CLASS="APPLICATION"
 191 >pg_dump</SPAN
 192 >'s
 193    non-text output modes.
 194   </P
 195 ><P
 196 >   Since <SPAN
 197 CLASS="APPLICATION"
 198 >pg_dumpall</SPAN
 199 > reads tables from all
 200    databases you will most likely have to connect as a database
 201    superuser in order to produce a complete dump.  Also you will need
 202    superuser privileges to execute the saved script in order to be
 203    allowed to add users and groups, and to create databases.
 204   </P
 205 ><P
 206 >   The SQL script will be written to the standard output.  Shell
 207    operators should be used to redirect it into a file.
 208   </P
 209 ><P
 210 >  <SPAN
 211 CLASS="APPLICATION"
 212 >pg_dumpall</SPAN
 213 > needs to connect several
 214   times to the <SPAN
 215 CLASS="PRODUCTNAME"
 216 >PostgreSQL</SPAN
 217 > server and might be asking for
 218   a password each time. It is convenient to have a
 219   <TT
 220 CLASS="FILENAME"
 221 >$HOME/.pgpass</TT
 222 > file in such cases.
 223   </P
 224 ></DIV
 225 ><DIV
 226 CLASS="REFSECT1"
 227 ><A
 228 NAME="AEN44844"
 229 ></A
 230 ><H2
 231 >Options</H2
 232 ><P
 233 >    The following command-line options are used to control the content and
 234     format of the output.
 235
 236     <P
 237 ></P
 238 ></P><DIV
 239 CLASS="VARIABLELIST"
 240 ><DL
 241 ><DT
 242 ><VAR
 243 CLASS="OPTION"
 244 >-a</VAR
 245 ><BR><VAR
 246 CLASS="OPTION"
 247 >--data-only</VAR
 248 ></DT
 249 ><DD
 250 ><P
 251 >       Dump only the data, not the schema (data definitions).
 252        </P
 253 ></DD
 254 ><DT
 255 ><VAR
 256 CLASS="OPTION"
 257 >-c</VAR
 258 ><BR><VAR
 259 CLASS="OPTION"
 260 >--clean</VAR
 261 ></DT
 262 ><DD
 263 ><P
 264 >       Include SQL commands to clean (drop) the databases before
 265         recreating them.
 266        </P
 267 ></DD
 268 ><DT
 269 ><VAR
 270 CLASS="OPTION"
 271 >-d</VAR
 272 ><BR><VAR
 273 CLASS="OPTION"
 274 >--inserts</VAR
 275 ></DT
 276 ><DD
 277 ><P
 278 >       Dump data as <TT
 279 CLASS="COMMAND"
 280 >INSERT</TT
 281 > commands (rather
 282         than <TT
 283 CLASS="COMMAND"
 284 >COPY</TT
 285 >). This will make restoration very
 286         slow, but it makes the output more portable to other SQL database
 287         management systems.
 288        </P
 289 ></DD
 290 ><DT
 291 ><VAR
 292 CLASS="OPTION"
 293 >-D</VAR
 294 ><BR><VAR
 295 CLASS="OPTION"
 296 >--column-inserts</VAR
 297 ><BR><VAR
 298 CLASS="OPTION"
 299 >--attribute-inserts</VAR
 300 ></DT
 301 ><DD
 302 ><P
 303 >       Dump data as <TT
 304 CLASS="COMMAND"
 305 >INSERT</TT
 306 > commands with explicit
 307         column names (<TT
 308 CLASS="LITERAL"
 309 >INSERT INTO
 310         <VAR
 311 CLASS="REPLACEABLE"
 312 >table</VAR
 313 >
 314         (<VAR
 315 CLASS="REPLACEABLE"
 316 >column</VAR
 317 >, ...) VALUES
 318         ...</TT
 319 >).  This will make restoration very slow,
 320         but it is necessary if you desire to rearrange column ordering.
 321        </P
 322 ></DD
 323 ><DT
 324 ><VAR
 325 CLASS="OPTION"
 326 >-g</VAR
 327 ><BR><VAR
 328 CLASS="OPTION"
 329 >--globals-only</VAR
 330 ></DT
 331 ><DD
 332 ><P
 333 >       Dump only global objects (users and groups), no databases.
 334        </P
 335 ></DD
 336 ><DT
 337 ><VAR
 338 CLASS="OPTION"
 339 >-i</VAR
 340 ><BR><VAR
 341 CLASS="OPTION"
 342 >--ignore-version</VAR
 343 ></DT
 344 ><DD
 345 ><P
 346 >        Ignore version mismatch between
 347         <SPAN
 348 CLASS="APPLICATION"
 349 >pg_dumpall</SPAN
 350 > and the database server.
 351        </P
 352 ><P
 353 >        <SPAN
 354 CLASS="APPLICATION"
 355 >pg_dumpall</SPAN
 356 > can handle databases
 357         from previous releases of <SPAN
 358 CLASS="PRODUCTNAME"
 359 >PostgreSQL</SPAN
 360 >, but very
 361         old versions are not supported anymore (currently prior to
 362         7.0).  Use this option if you need to override the version
 363         check (and if <SPAN
 364 CLASS="APPLICATION"
 365 >pg_dumpall</SPAN
 366 > then
 367         fails, don't say you weren't warned).
 368        </P
 369 ></DD
 370 ><DT
 371 ><VAR
 372 CLASS="OPTION"
 373 >-o</VAR
 374 ><BR><VAR
 375 CLASS="OPTION"
 376 >--oids</VAR
 377 ></DT
 378 ><DD
 379 ><P
 380 >       Dump object identifiers (<ACRONYM
 381 CLASS="ACRONYM"
 382 >OID</ACRONYM
 383 >s) for every
 384         table.  Use this option if your application references the OID
 385         columns in some way (e.g., in a foreign key constraint).
 386         Otherwise, this option should not be used.
 387        </P
 388 ></DD
 389 ><DT
 390 ><VAR
 391 CLASS="OPTION"
 392 >-s</VAR
 393 ><BR><VAR
 394 CLASS="OPTION"
 395 >--schema-only</VAR
 396 ></DT
 397 ><DD
 398 ><P
 399 >       Dump only the schema (data definitions), no data.
 400        </P
 401 ></DD
 402 ><DT
 403 ><VAR
 404 CLASS="OPTION"
 405 >-v</VAR
 406 ><BR><VAR
 407 CLASS="OPTION"
 408 >--verbose</VAR
 409 ></DT
 410 ><DD
 411 ><P
 412 >       Specifies verbose mode.  This will cause
 413         <SPAN
 414 CLASS="APPLICATION"
 415 >pg_dumpall</SPAN
 416 > to print progress
 417         messages to standard error.
 418        </P
 419 ></DD
 420 ><DT
 421 ><VAR
 422 CLASS="OPTION"
 423 >-x</VAR
 424 ><BR><VAR
 425 CLASS="OPTION"
 426 >--no-privileges</VAR
 427 ><BR><VAR
 428 CLASS="OPTION"
 429 >--no-acl</VAR
 430 ></DT
 431 ><DD
 432 ><P
 433 >       Prevent dumping of access privileges (grant/revoke commands).
 434        </P
 435 ></DD
 436 ></DL
 437 ></DIV
 438 ><P>
 439    </P
 440 ><P
 441 >   The following command-line options control the database connection parameters.
 442
 443    <P
 444 ></P
 445 ></P><DIV
 446 CLASS="VARIABLELIST"
 447 ><DL
 448 ><DT
 449 >-h <VAR
 450 CLASS="REPLACEABLE"
 451 >host</VAR
 452 ></DT
 453 ><DD
 454 ><P
 455 >       Specifies the host name of the machine on which the database
 456         server is running.  If the value begins with a slash, it is
 457         used as the directory for the Unix domain socket.  The default
 458         is taken from the <TT
 459 CLASS="ENVAR"
 460 >PGHOST</TT
 461 > environment variable,
 462         if set, else a Unix domain socket connection is attempted.
 463        </P
 464 ></DD
 465 ><DT
 466 >-p <VAR
 467 CLASS="REPLACEABLE"
 468 >port</VAR
 469 ></DT
 470 ><DD
 471 ><P
 472 >       Specifies the TCP port or local Unix domain socket file
 473         extension on which the server is listening for connections.
 474         Defaults to the <TT
 475 CLASS="ENVAR"
 476 >PGPORT</TT
 477 > environment variable, if
 478         set, or a compiled-in default.
 479        </P
 480 ></DD
 481 ><DT
 482 >-U <VAR
 483 CLASS="REPLACEABLE"
 484 >username</VAR
 485 ></DT
 486 ><DD
 487 ><P
 488 >        Connect as the given user.
 489        </P
 490 ></DD
 491 ><DT
 492 >-W</DT
 493 ><DD
 494 ><P
 495 >        Force a password prompt.  This should happen automatically if
 496         the server requires password authentication.
 497        </P
 498 ></DD
 499 ></DL
 500 ></DIV
 501 ><P>
 502   </P
 503 ></DIV
 504 ><DIV
 505 CLASS="REFSECT1"
 506 ><A
 507 NAME="AEN44958"
 508 ></A
 509 ><H2
 510 >Environment</H2
 511 ><P
 512 ></P
 513 ><DIV
 514 CLASS="VARIABLELIST"
 515 ><DL
 516 ><DT
 517 ><TT
 518 CLASS="ENVAR"
 519 >PGHOST</TT
 520 ><BR><TT
 521 CLASS="ENVAR"
 522 >PGPORT</TT
 523 ><BR><TT
 524 CLASS="ENVAR"
 525 >PGUSER</TT
 526 ></DT
 527 ><DD
 528 ><P
 529 >      Default connection parameters
 530      </P
 531 ></DD
 532 ></DL
 533 ></DIV
 534 ></DIV
 535 ><DIV
 536 CLASS="REFSECT1"
 537 ><A
 538 NAME="AEN44970"
 539 ></A
 540 ><H2
 541 >Notes</H2
 542 ><P
 543 >   Since <SPAN
 544 CLASS="APPLICATION"
 545 >pg_dumpall</SPAN
 546 > calls
 547    <SPAN
 548 CLASS="APPLICATION"
 549 >pg_dump</SPAN
 550 > internally, some diagnostic
 551    messages will refer to <SPAN
 552 CLASS="APPLICATION"
 553 >pg_dump</SPAN
 554 >.
 555   </P
 556 ><P
 557 >   Once restored, it is wise to run <TT
 558 CLASS="COMMAND"
 559 >ANALYZE</TT
 560 > on each
 561    database so the optimizer has useful statistics. You
 562    can also run <TT
 563 CLASS="COMMAND"
 564 >vacuumdb -a -z</TT
 565 > to analyze all
 566    databases.
 567   </P
 568 ></DIV
 569 ><DIV
 570 CLASS="REFSECT1"
 571 ><A
 572 NAME="APP-PG-DUMPALL-EX"
 573 ></A
 574 ><H2
 575 >Examples</H2
 576 ><P
 577 >   To dump all databases:
 578
 579 </P><PRE
 580 CLASS="SCREEN"
 581 ><SAMP
 582 CLASS="PROMPT"
 583 >$</SAMP
 584 > <KBD
 585 CLASS="USERINPUT"
 586 >pg_dumpall &gt; db.out</KBD
 587 ></PRE
 588 ><P>
 589   </P
 590 ><P
 591 >   To reload this database use, for example:
 592 </P><PRE
 593 CLASS="SCREEN"
 594 ><SAMP
 595 CLASS="PROMPT"
 596 >$</SAMP
 597 > <KBD
 598 CLASS="USERINPUT"
 599 >psql -f db.out template1</KBD
 600 ></PRE
 601 ><P>
 602    (It is not important to which database you connect here since the
 603    script file created by <SPAN
 604 CLASS="APPLICATION"
 605 >pg_dumpall</SPAN
 606 > will
 607    contain the appropriate commands to create and connect to the saved
 608    databases.)
 609   </P
 610 ></DIV
 611 ><DIV
 612 CLASS="REFSECT1"
 613 ><A
 614 NAME="AEN44990"
 615 ></A
 616 ><H2
 617 >See Also</H2
 618 ><P
 619 >    <A
 620 HREF="app-pgdump.html"
 621 >pg_dump</A
 622 >.  Check there for details on possible
 623     error conditions.
 624   </P
 625 ></DIV
 626 ><DIV
 627 CLASS="NAVFOOTER"
 628 ><HR
 629 ALIGN="LEFT"
 630 WIDTH="100%"><TABLE
 631 SUMMARY="Footer navigation table"
 632 WIDTH="100%"
 633 BORDER="0"
 634 CELLPADDING="0"
 635 CELLSPACING="0"
 636 ><TR
 637 ><TD
 638 WIDTH="33%"
 639 ALIGN="left"
 640 VALIGN="top"
 641 ><A
 642 HREF="app-pgdump.html"
 643 ACCESSKEY="P"
 644 >Prev</A
 645 ></TD
 646 ><TD
 647 WIDTH="34%"
 648 ALIGN="center"
 649 VALIGN="top"
 650 ><A
 651 HREF="index.html"
 652 ACCESSKEY="H"
 653 >Home</A
 654 ></TD
 655 ><TD
 656 WIDTH="33%"
 657 ALIGN="right"
 658 VALIGN="top"
 659 ><A
 660 HREF="app-pgrestore.html"
 661 ACCESSKEY="N"
 662 >Next</A
 663 ></TD
 664 ></TR
 665 ><TR
 666 ><TD
 667 WIDTH="33%"
 668 ALIGN="left"
 669 VALIGN="top"
 670 >pg_dump</TD
 671 ><TD
 672 WIDTH="34%"
 673 ALIGN="center"
 674 VALIGN="top"
 675 ><A
 676 HREF="reference-client.html"
 677 ACCESSKEY="U"
 678 >Up</A
 679 ></TD
 680 ><TD
 681 WIDTH="33%"
 682 ALIGN="right"
 683 VALIGN="top"
 684 >pg_restore</TD
 685 ></TR
 686 ></TABLE
 687 ></DIV
 688 ></BODY
 689 ></HTML
 690 >