PostgreSQL supplies a supplementary wrapper command to the
pg_dump
application called
pg_dumpall
. 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
pg_dump
for each database, one at a
time.
Here is the syntax for
pg_dumpall
, displayed from the
- -help
flag:
pg_dumpall [ -c ] [ -h
HOSTNAME
] [ -p
PORT
] [ -g ]
The
pg_dumpall
command accepts the same connection parameters available to
pg_dump
. The following are the
pg_dumpall
-specific parameters:
- -c
-
Specifies that SQL statements to drop existing global objects will precede the SQL statements to create those objects.
- -h
HOSTNAME
, - -host=
HOSTNAME
-
Specifies that
HOSTNAME
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
server.
- -p
PORT
, - -port=
PORT
-
Specifies that the database connection should be made on port
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
-g
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!
|
Warning
|
|
Do not pass the
-?
flag to
pg_dumpall
, as it will result in passing that
flag to
pg_dump
for each database, which may result in quite a few more help screens than you would
expect. If you wish to view
pg_dumpall
's help, use the
- -help
flag.
|
Note that as of PostgreSQL 7.1.x, the use of the
pg_dumpall
script does have some practical
limitations over the normal use of
pg_dump
. For example, the
-u
flag
cannot be used to provide a different username and password, and the
-F
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
that the
-b
flag cannot be used with
pg_dumpall
either, as it requires a format
other than plain-text.
While the
-f
flag can be used to pass a filename through to
pg_dump
, doing
so does not create a complete dump, as
pg_dumpall
's global data is still sent to
stdout
. To solve the problem of not having the
-f
flag available to
pg_dumpall
, the shell re-direction operator (>) can be used to
redirect the output of
pg_dumpall
to a file.
A simple workaround to the lack of the
-u
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
each
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
pg_dumpall
script. Example 9-22 uses this technique to create one dump file for all databases.
Example 9-22. Using pg_dumpall
[jworsley@booktown ~]$
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
postgres
. This
variable is set for the duration of the
pg_dumpall
command, and expires when the command has finished.
Note: The
pg_dumpall
command generally requires that the user executing the script be a
PostgreSQL superuser. This is because the
pg_dumpall
command requires access to the PostgreSQL system catalogs,
as it dumps global objects as well as database objects.
You can also run the
pg_dumpall
command remotely, though be sure to set any environment
variables as needed. These will depend greatly on the remote host configuration in the
pg_hba.conf
file.
You should use the
pg_dumpall
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
pg_dumpall
for data output (particularly if your
database makes use of large objects), the simplest solution is to use
pg_dumpall
with the
-g
flag to keep a backup of all user and group data, and to subsequently use
pg_dump
for each database which needs to be backed up, individually.
