PostgreSQL supplies a supplementary wrapper command to the
. The primary use of this application is to allow the entire cluster of PostgreSQL databases
on a system to be dumped at once, rather than having to perform a
for each database, one at a
Here is the syntax for
, displayed from the
pg_dumpall [ -c ] [ -h
] [ -p
] [ -g ]
command accepts the same connection parameters available to
. The following are the
Specifies that SQL statements to drop existing global objects will precede the SQL statements to create those objects.
, - -host=
should be connected to, rather than the localhost, or the host defined
by the PGHOST environment variable. Use this when the target database is on another
, - -port=
Specifies that the database connection should be made on port
, rather than the
default port (usually 5432).
- -g, - -globals-only
Specifies that only global objects will be dumped. This is primarily useful for recreating just users and groups, or
for duplicating them on another machine (by taking the dump to another machine, and executing it). The
flag implicitly causes all users to be deleted from the pg_shadow table prior to the
CREATE statements. Exercise caution with the output from this command!
Do not pass the
, as it will result in passing that
for each database, which may result in quite a few more help screens than you would
expect. If you wish to view
's help, use the
Note that as of PostgreSQL 7.1.x, the use of the
script does have some practical
limitations over the normal use of
. For example, the
cannot be used to provide a different username and password, and the
flag may not be used to
specify a format other than plain text (your backups will be stored as plain text, regardless of chosen format). This means
flag cannot be used with
either, as it requires a format
other than plain-text.
flag can be used to pass a filename through to
so does not create a complete dump, as
's global data is still sent to
. To solve the problem of not having the
flag available to
, the shell re-direction operator (>) can be used to
redirect the output of
to a file.
A simple workaround to the lack of the
flag is to set the
PGUSER environment variable. You can also set the
PGPASSWORD environment variable in this fashion if you are connecting to a system which
requires password authentication, and you do not wish to provide a password for
database that it
connects to and dumps.
Example 9-22 demonstrates a simple bash-shell shorthand for a temporary environment
variable (PGUSER) when invoking any PostgreSQL client. While not usually necessary, it can
be a handy trick to know for exceptions such as the
script. Example 9-22 uses this technique to create one dump file for all databases.
Example 9-22. Using pg_dumpall
[[email protected] ~]$
PGUSER=postgres pg_dumpall > all.sql
The first part of the shell statement demonstrated in Example 9-22 sets a temporary
environment variable named PGUSER to the value of
variable is set for the duration of the
command, and expires when the command has finished.
command generally requires that the user executing the script be a
PostgreSQL superuser. This is because the
command requires access to the PostgreSQL system catalogs,
as it dumps global objects as well as database objects.
You can also run the
command remotely, though be sure to set any environment
variables as needed. These will depend greatly on the remote host configuration in the
You should use the
command if it is especially
inconvenient to backup all your existing databases individually, or if you have any kind of complex user and group system
in place. If you are inhibited by the limitations of
for data output (particularly if your
database makes use of large objects), the simplest solution is to use
flag to keep a backup of all user and group data, and to subsequently use
for each database which needs to be backed up, individually.