pg_dumpall
pg_dumpall — extract aPostgres Pro database cluster into a script file
Synopsis
pg_dumpall
[connection-option
...] [option
...]
Description
pg_dumpall is a utility for writing out (“dumping”) allPostgres Pro databases of a cluster into one script file. The script file containsSQL commands that can be used as input topsql to restore the databases. It does this by callingpg_dump for each database in a cluster.pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.
Sincepg_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. Use the-f
/--file
option or shell operators to redirect it into a file.
pg_dumpall needs to connect several times to thePostgres Pro server (once per database). If you use password authentication it will ask for a password each time. It is convenient to have a~/.pgpass
file in such cases. SeeSection 31.15 for more information.
Options
The following command-line options 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) databases before recreating them.
DROP
commands for roles and tablespaces are added as well.-f
filename
--file=
filename
Send output to the specified file. If this is omitted, the standard output is used.
-g
--globals-only
Dump only global objects (roles and tablespaces), no databases.
-o
--oids
Dump object identifiers (OIDs) as part of the data for every table. Use this option if your application references theOID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used.
-O
--no-owner
-r
--roles-only
-s
--schema-only
-S
username
--superuser=
username
-t
--tablespaces-only
-v
--verbose
-V
--version
-x
--no-privileges
--no-acl
Prevent dumping of access privileges (grant/revoke commands).
--binary-upgrade
--column-inserts
--attribute-inserts
--disable-dollar-quoting
--disable-triggers
--if-exists
--inserts
--lock-wait-timeout=
timeout
--no-security-labels
--no-tablespaces
--no-unlogged-table-data
--quote-all-identifiers
--use-set-session-authorization
-?
--help
The following command-line options control the database connection parameters.
-d
connstr
--dbname=
connstr
Specifies parameters used to connect to the server, as a connection string. SeeSection 31.1.1 for more information.
The option is called
--dbname
for consistency with other client applications, but becausepg_dumpall needs to connect to many databases, database name in the connection string will be ignored. Use-l
option to specify the name of the database used to dump global objects and to discover what other databases should be dumped.-h
host
--host=
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.-l
dbname
--database=
dbname
Specifies the name of the database to connect to for dumping global objects and discovering what other databases should be dumped. If not specified, the
postgres
database will be used, and if that does not exist,template1
will be used.-p
port
--port=
port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the
PGPORT
environment variable, if set, or a compiled-in default.-U
username
--username=
username
User name to connect as.
-w
--no-password
Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a
.pgpass
file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.-W
--password
Forcepg_dumpall to prompt for a password before connecting to a database.
This option is never essential, sincepg_dumpall will automatically prompt for a password if the server demands password authentication. However,pg_dumpall will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing
-W
to avoid the extra connection attempt.Note that the password prompt will occur again for each database to be dumped. Usually, it's better to set up a
~/.pgpass
file than to rely on manual password entry.--role=
rolename
Specifies a role name to be used to create the dump. This option causespg_dumpall to issue a
SET ROLE
rolename
command after connecting to the database. It is useful when the authenticated user (specified by-U
) lacks privileges needed bypg_dumpall, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows dumps to be made without violating the policy.
Environment
PGHOST
PGOPTIONS
PGPORT
PGUSER
Default connection parameters
This utility, like most otherPostgres Pro utilities, also uses the environment variables supported bylibpq (seeSection 31.14).
Notes
Sincepg_dumpall callspg_dump internally, some diagnostic messages will refer topg_dump.
Once restored, it is wise to runANALYZE
on each database so the optimizer has useful statistics. You can also runvacuumdb -a -z
to analyze all databases.
pg_dumpall requires all needed tablespace directories to exist before the restore; otherwise, database creation will fail for databases in non-default locations.
Examples
To dump all databases:
$
pg_dumpall > db.out
To reload database(s) from this file, you can use:
$
psql -f db.out postgres
(It is not important to which database you connect here since the script file created bypg_dumpall will contain the appropriate commands to create and connect to the saved databases.)
See Also
Checkpg_dump for details on possible error conditions.