pg_dumpall(1)

NAME

       pg_dumpall - extract a PostgreSQL database cluster into a script file

SYNOPSIS

       pg_dumpall [ option... ]

DESCRIPTION

       pg_dumpall  is  a  utility for writing out (``dumping'') all PostgreSQL
       databases of a cluster into one script file. The script  file  contains
       SQL  commands that can be used as input to psql(1) to restore the data-
       bases. It does this by calling pg_dump(1) for each database in a  clus-
       ter.  pg_dumpall also dumps global objects that are common to all data-
       bases.  (pg_dump does not save these objects.)  This currently includes
       information  about  database  users  and groups, and access permissions
       that apply to databases as a whole.

       Thus, pg_dumpall is an integrated solution for backing  up  your  data-
       bases.  But  note a limitation: it cannot dump ``large objects'', since
       pg_dump cannot dump such objects into text files. If you have databases
       containing  large objects, they should be dumped using one of pg_dump's
       non-text output modes.

       Since pg_dumpall 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 superuser  privileges  to  execute  the  saved
       script  in  order  to be allowed to add users and groups, and to create
       databases.

       The SQL script will be written to the standard output. Shell  operators
       should be used to redirect it into a file.

       pg_dumpall  needs to connect several times to the PostgreSQL server and
       might be asking for a password each time. It is convenient  to  have  a
       $HOME/.pgpass file in such cases.

OPTIONS

       The  following command-line options are used to control the content and
       format of the output.

       -a

       --data-only
              Dump only the data, not the schema (data definitions).

       -c

       --clean
              Include SQL commands to clean (drop) the databases before recre-
              ating them.

       -d

       --inserts
              Dump  data as INSERT commands (rather than COPY). This will make
              restoration very slow, but it makes the output more portable  to
              other SQL database packages.

       -D

       --column-inserts

       --attribute-inserts
              Dump  data as INSERT commands with explicit column names (INSERT
              INTO table (column, ...) VALUES ...). This will make restoration
              very slow, but it is necessary if you desire to rearrange column
              ordering.

       -g

       --globals-only
              Dump only global objects (users and groups), no databases.

       -i

       --ignore-version
              Ignore version mismatch  between  pg_dumpall  and  the  database
              server.

              pg_dumpall  can handle databases from previous releases of Post-
              greSQL, but very old versions are not  supported  anymore  (cur-
              rently  prior  to  7.0). Use this option if you need to override
              the version check (and if pg_dumpall then fails, don't  say  you
              weren't warned).

       -o

       --oids Dump  object identifiers (OIDs) for every table. Use this option
              if your application references  the  OID  columns  in  some  way
              (e.g.,  in  a  foreign  key constraint).  Otherwise, this option
              should not be used.

       -s

       --schema-only
              Dump only the schema (data definitions), no data.

       -v

       --verbose
              Specifies verbose mode. This  will  cause  pg_dumpall  to  print
              progress messages to standard error.

       -x

       --no-privileges

       --no-acl
              Prevent dumping of access privileges (grant/revoke commands).

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

       -h host
              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 PGHOST environment variable, if set, else a Unix
              domain socket connection is attempted.

       -p port
              Specifies the TCP port or local Unix domain socket  file  exten-
              sion on which the server is listening for connections.  Defaults
              to the PGPORT environment variable, if  set,  or  a  compiled-in
              default.

       -U username
              Connect as the given user.

       -W     Force a password prompt. This should happen automatically if the
              server requires password authentication.

ENVIRONMENT

       PGHOST

       PGPORT

       PGUSER Default connection parameters

NOTES

       Since pg_dumpall calls pg_dump  internally,  some  diagnostic  messages
       will refer to pg_dump.

       Once  restored, it is wise to run ANALYZE on each database so the opti-
       mizer has useful statistics. You can also run vacuumdb -a -z to analyze
       all databases.

EXAMPLES

       To dump all databases:

       $ pg_dumpall > db.out

       To reload this database use, for example:

       $ psql -f db.out template1

       (It  is  not  important  to  which  database you connect here since the
       script file created by pg_dumpall will contain the appropriate commands
       to create and connect to the saved databases.)

SEE ALSO

       pg_dump(1). Check there for details on possible error conditions.

Application                       2003-11-02                     PG_DUMPALL(1)