path: root/doc/src/sgml/man1/pg_dumpall.1

'\" t
.\"     Title: pg_dumpall
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2024
.\"    Manual: PostgreSQL 15.7 Documentation
.\"    Source: PostgreSQL 15.7
.\"  Language: English
.\"
.TH "PG_DUMPALL" "1" "2024" "PostgreSQL 15.7" "PostgreSQL 15.7 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
pg_dumpall \- extract a PostgreSQL database cluster into a script file
.SH "SYNOPSIS"
.HP \w'\fBpg_dumpall\fR\ 'u
\fBpg_dumpall\fR [\fIconnection\-option\fR...] [\fIoption\fR...]
.SH "DESCRIPTION"
.PP
pg_dumpall
is a utility for writing out (\(lqdumping\(rq) all
PostgreSQL
databases of a cluster into one script file\&. The script file contains
SQL
commands that can be used as input to
\fBpsql\fR(1)
to restore the databases\&. It does this by calling
\fBpg_dump\fR(1)
for each database in the cluster\&.
pg_dumpall
also dumps global objects that are common to all databases, namely database roles, tablespaces, and privilege grants for configuration parameters\&. (pg_dump
does not save these objects\&.)
.PP
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 roles and create databases\&.
.PP
The SQL script will be written to the standard output\&. Use the
\fB\-f\fR/\fB\-\-file\fR
option or shell operators to redirect it into a file\&.
.PP
pg_dumpall
needs to connect several times to the
PostgreSQL
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\&. See
Section\ \&34.16
for more information\&.
.SH "OPTIONS"
.PP
The following command\-line options control the content and format of the output\&.
.PP
\fB\-a\fR
.br
\fB\-\-data\-only\fR
.RS 4
Dump only the data, not the schema (data definitions)\&.
.RE
.PP
\fB\-c\fR
.br
\fB\-\-clean\fR
.RS 4
Emit SQL commands to
\fBDROP\fR
all the dumped databases, roles, and tablespaces before recreating them\&. This option is useful when the restore is to overwrite an existing cluster\&. If any of the objects do not exist in the destination cluster, ignorable error messages will be reported during restore, unless
\fB\-\-if\-exists\fR
is also specified\&.
.RE
.PP
\fB\-E \fR\fB\fIencoding\fR\fR
.br
\fB\-\-encoding=\fR\fB\fIencoding\fR\fR
.RS 4
Create the dump in the specified character set encoding\&. By default, the dump is created in the database encoding\&. (Another way to get the same result is to set the
\fBPGCLIENTENCODING\fR
environment variable to the desired dump encoding\&.)
.RE
.PP
\fB\-f \fR\fB\fIfilename\fR\fR
.br
\fB\-\-file=\fR\fB\fIfilename\fR\fR
.RS 4
Send output to the specified file\&. If this is omitted, the standard output is used\&.
.RE
.PP
\fB\-g\fR
.br
\fB\-\-globals\-only\fR
.RS 4
Dump only global objects (roles and tablespaces), no databases\&.
.RE
.PP
\fB\-O\fR
.br
\fB\-\-no\-owner\fR
.RS 4
Do not output commands to set ownership of objects to match the original database\&. By default,
pg_dumpall
issues
\fBALTER OWNER\fR
or
\fBSET SESSION AUTHORIZATION\fR
statements to set ownership of created schema elements\&. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script)\&. To make a script that can be restored by any user, but will give that user ownership of all the objects, specify
\fB\-O\fR\&.
.RE
.PP
\fB\-r\fR
.br
\fB\-\-roles\-only\fR
.RS 4
Dump only roles, no databases or tablespaces\&.
.RE
.PP
\fB\-s\fR
.br
\fB\-\-schema\-only\fR
.RS 4
Dump only the object definitions (schema), not data\&.
.RE
.PP
\fB\-S \fR\fB\fIusername\fR\fR
.br
\fB\-\-superuser=\fR\fB\fIusername\fR\fR
.RS 4
Specify the superuser user name to use when disabling triggers\&. This is relevant only if
\fB\-\-disable\-triggers\fR
is used\&. (Usually, it\*(Aqs better to leave this out, and instead start the resulting script as superuser\&.)
.RE
.PP
\fB\-t\fR
.br
\fB\-\-tablespaces\-only\fR
.RS 4
Dump only tablespaces, no databases or roles\&.
.RE
.PP
\fB\-v\fR
.br
\fB\-\-verbose\fR
.RS 4
Specifies verbose mode\&. This will cause
pg_dumpall
to output start/stop times to the dump file, and progress messages to standard error\&. Repeating the option causes additional debug\-level messages to appear on standard error\&. The option is also passed down to
pg_dump\&.
.RE
.PP
\fB\-V\fR
.br
\fB\-\-version\fR
.RS 4
Print the
pg_dumpall
version and exit\&.
.RE
.PP
\fB\-x\fR
.br
\fB\-\-no\-privileges\fR
.br
\fB\-\-no\-acl\fR
.RS 4
Prevent dumping of access privileges (grant/revoke commands)\&.
.RE
.PP
\fB\-\-binary\-upgrade\fR
.RS 4
This option is for use by in\-place upgrade utilities\&. Its use for other purposes is not recommended or supported\&. The behavior of the option may change in future releases without notice\&.
.RE
.PP
\fB\-\-column\-inserts\fR
.br
\fB\-\-attribute\-inserts\fR
.RS 4
Dump data as
\fBINSERT\fR
commands with explicit column names (INSERT INTO \fItable\fR (\fIcolumn\fR, \&.\&.\&.) VALUES \&.\&.\&.)\&. This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non\-PostgreSQL
databases\&.
.RE
.PP
\fB\-\-disable\-dollar\-quoting\fR
.RS 4
This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax\&.
.RE
.PP
\fB\-\-disable\-triggers\fR
.RS 4
This option is relevant only when creating a data\-only dump\&. It instructs
pg_dumpall
to include commands to temporarily disable triggers on the target tables while the data is restored\&. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data restore\&.
.sp
Presently, the commands emitted for
\fB\-\-disable\-triggers\fR
must be done as superuser\&. So, you should also specify a superuser name with
\fB\-S\fR, or preferably be careful to start the resulting script as a superuser\&.
.RE
.PP
\fB\-\-exclude\-database=\fR\fB\fIpattern\fR\fR
.RS 4
Do not dump databases whose name matches
\fIpattern\fR\&. Multiple patterns can be excluded by writing multiple
\fB\-\-exclude\-database\fR
switches\&. The
\fIpattern\fR
parameter is interpreted as a pattern according to the same rules used by
psql\*(Aqs
\ed
commands (see
Patterns
below), so multiple databases can also be excluded by writing wildcard characters in the pattern\&. When using wildcards, be careful to quote the pattern if needed to prevent shell wildcard expansion\&.
.RE
.PP
\fB\-\-extra\-float\-digits=\fR\fB\fIndigits\fR\fR
.RS 4
Use the specified value of extra_float_digits when dumping floating\-point data, instead of the maximum available precision\&. Routine dumps made for backup purposes should not use this option\&.
.RE
.PP
\fB\-\-if\-exists\fR
.RS 4
Use
DROP \&.\&.\&. IF EXISTS
commands to drop objects in
\fB\-\-clean\fR
mode\&. This suppresses
\(lqdoes not exist\(rq
errors that might otherwise be reported\&. This option is not valid unless
\fB\-\-clean\fR
is also specified\&.
.RE
.PP
\fB\-\-inserts\fR
.RS 4
Dump data as
\fBINSERT\fR
commands (rather than
\fBCOPY\fR)\&. This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non\-PostgreSQL
databases\&. Note that the restore might fail altogether if you have rearranged column order\&. The
\fB\-\-column\-inserts\fR
option is safer, though even slower\&.
.RE
.PP
\fB\-\-load\-via\-partition\-root\fR
.RS 4
When dumping data for a table partition, make the
\fBCOPY\fR
or
\fBINSERT\fR
statements target the root of the partitioning hierarchy that contains it, rather than the partition itself\&. This causes the appropriate partition to be re\-determined for each row when the data is loaded\&. This may be useful when restoring data on a server where rows do not always fall into the same partitions as they did on the original server\&. That could happen, for example, if the partitioning column is of type text and the two systems have different definitions of the collation used to sort the partitioning column\&.
.RE
.PP
\fB\-\-lock\-wait\-timeout=\fR\fB\fItimeout\fR\fR
.RS 4
Do not wait forever to acquire shared table locks at the beginning of the dump\&. Instead, fail if unable to lock a table within the specified
\fItimeout\fR\&. The timeout may be specified in any of the formats accepted by
\fBSET statement_timeout\fR\&.
.RE
.PP
\fB\-\-no\-comments\fR
.RS 4
Do not dump comments\&.
.RE
.PP
\fB\-\-no\-publications\fR
.RS 4
Do not dump publications\&.
.RE
.PP
\fB\-\-no\-role\-passwords\fR
.RS 4
Do not dump passwords for roles\&. When restored, roles will have a null password, and password authentication will always fail until the password is set\&. Since password values aren\*(Aqt needed when this option is specified, the role information is read from the catalog view
pg_roles
instead of
pg_authid\&. Therefore, this option also helps if access to
pg_authid
is restricted by some security policy\&.
.RE
.PP
\fB\-\-no\-security\-labels\fR
.RS 4
Do not dump security labels\&.
.RE
.PP
\fB\-\-no\-subscriptions\fR
.RS 4
Do not dump subscriptions\&.
.RE
.PP
\fB\-\-no\-sync\fR
.RS 4
By default,
\fBpg_dumpall\fR
will wait for all files to be written safely to disk\&. This option causes
\fBpg_dumpall\fR
to return without waiting, which is faster, but means that a subsequent operating system crash can leave the dump corrupt\&. Generally, this option is useful for testing but should not be used when dumping data from production installation\&.
.RE
.PP
\fB\-\-no\-table\-access\-method\fR
.RS 4
Do not output commands to select table access methods\&. With this option, all objects will be created with whichever table access method is the default during restore\&.
.RE
.PP
\fB\-\-no\-tablespaces\fR
.RS 4
Do not output commands to create tablespaces nor select tablespaces for objects\&. With this option, all objects will be created in whichever tablespace is the default during restore\&.
.RE
.PP
\fB\-\-no\-toast\-compression\fR
.RS 4
Do not output commands to set
TOAST
compression methods\&. With this option, all columns will be restored with the default compression setting\&.
.RE
.PP
\fB\-\-no\-unlogged\-table\-data\fR
.RS 4
Do not dump the contents of unlogged tables\&. This option has no effect on whether or not the table definitions (schema) are dumped; it only suppresses dumping the table data\&.
.RE
.PP
\fB\-\-on\-conflict\-do\-nothing\fR
.RS 4
Add
ON CONFLICT DO NOTHING
to
\fBINSERT\fR
commands\&. This option is not valid unless
\fB\-\-inserts\fR
or
\fB\-\-column\-inserts\fR
is also specified\&.
.RE
.PP
\fB\-\-quote\-all\-identifiers\fR
.RS 4
Force quoting of all identifiers\&. This option is recommended when dumping a database from a server whose
PostgreSQL
major version is different from
pg_dumpall\*(Aqs, or when the output is intended to be loaded into a server of a different major version\&. By default,
pg_dumpall
quotes only identifiers that are reserved words in its own major version\&. This sometimes results in compatibility issues when dealing with servers of other versions that may have slightly different sets of reserved words\&. Using
\fB\-\-quote\-all\-identifiers\fR
prevents such issues, at the price of a harder\-to\-read dump script\&.
.RE
.PP
\fB\-\-rows\-per\-insert=\fR\fB\fInrows\fR\fR
.RS 4
Dump data as
\fBINSERT\fR
commands (rather than
\fBCOPY\fR)\&. Controls the maximum number of rows per
\fBINSERT\fR
command\&. The value specified must be a number greater than zero\&. Any error during restoring will cause only rows that are part of the problematic
\fBINSERT\fR
to be lost, rather than the entire table contents\&.
.RE
.PP
\fB\-\-use\-set\-session\-authorization\fR
.RS 4
Output SQL\-standard
\fBSET SESSION AUTHORIZATION\fR
commands instead of
\fBALTER OWNER\fR
commands to determine object ownership\&. This makes the dump more standards compatible, but depending on the history of the objects in the dump, might not restore properly\&.
.RE
.PP
\fB\-?\fR
.br
\fB\-\-help\fR
.RS 4
Show help about
pg_dumpall
command line arguments, and exit\&.
.RE
.PP
The following command\-line options control the database connection parameters\&.
.PP
\fB\-d \fR\fB\fIconnstr\fR\fR
.br
\fB\-\-dbname=\fR\fB\fIconnstr\fR\fR
.RS 4
Specifies parameters used to connect to the server, as a
connection string; these will override any conflicting command line options\&.
.sp
The option is called
\-\-dbname
for consistency with other client applications, but because
pg_dumpall
needs to connect to many databases, the database name in the connection string will be ignored\&. Use the
\-l
option to specify the name of the database used for the initial connection, which will dump global objects and discover what other databases should be dumped\&.
.RE
.PP
\fB\-h \fR\fB\fIhost\fR\fR
.br
\fB\-\-host=\fR\fB\fIhost\fR\fR
.RS 4
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
\fBPGHOST\fR
environment variable, if set, else a Unix domain socket connection is attempted\&.
.RE
.PP
\fB\-l \fR\fB\fIdbname\fR\fR
.br
\fB\-\-database=\fR\fB\fIdbname\fR\fR
.RS 4
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\&.
.RE
.PP
\fB\-p \fR\fB\fIport\fR\fR
.br
\fB\-\-port=\fR\fB\fIport\fR\fR
.RS 4
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections\&. Defaults to the
\fBPGPORT\fR
environment variable, if set, or a compiled\-in default\&.
.RE
.PP
\fB\-U \fR\fB\fIusername\fR\fR
.br
\fB\-\-username=\fR\fB\fIusername\fR\fR
.RS 4
User name to connect as\&.
.RE
.PP
\fB\-w\fR
.br
\fB\-\-no\-password\fR
.RS 4
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\&.
.RE
.PP
\fB\-W\fR
.br
\fB\-\-password\fR
.RS 4
Force
pg_dumpall
to prompt for a password before connecting to a database\&.
.sp
This option is never essential, since
pg_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
\fB\-W\fR
to avoid the extra connection attempt\&.
.sp
Note that the password prompt will occur again for each database to be dumped\&. Usually, it\*(Aqs better to set up a
~/\&.pgpass
file than to rely on manual password entry\&.
.RE
.PP
\fB\-\-role=\fR\fB\fIrolename\fR\fR
.RS 4
Specifies a role name to be used to create the dump\&. This option causes
pg_dumpall
to issue a
\fBSET ROLE\fR
\fIrolename\fR
command after connecting to the database\&. It is useful when the authenticated user (specified by
\fB\-U\fR) lacks privileges needed by
pg_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\&.
.RE
.SH "ENVIRONMENT"
.PP
\fBPGHOST\fR
.br
\fBPGOPTIONS\fR
.br
\fBPGPORT\fR
.br
\fBPGUSER\fR
.RS 4
Default connection parameters
.RE
.PP
\fBPG_COLOR\fR
.RS 4
Specifies whether to use color in diagnostic messages\&. Possible values are
always,
auto
and
never\&.
.RE
.PP
This utility, like most other
PostgreSQL
utilities, also uses the environment variables supported by
libpq
(see
Section\ \&34.15)\&.
.SH "NOTES"
.PP
Since
pg_dumpall
calls
pg_dump
internally, some diagnostic messages will refer to
pg_dump\&.
.PP
The
\fB\-\-clean\fR
option can be useful even when your intention is to restore the dump script into a fresh cluster\&. Use of
\fB\-\-clean\fR
authorizes the script to drop and re\-create the built\-in
postgres
and
template1
databases, ensuring that those databases will retain the same properties (for instance, locale and encoding) that they had in the source cluster\&. Without the option, those databases will retain their existing database\-level properties, as well as any pre\-existing contents\&.
.PP
Once restored, it is wise to run
\fBANALYZE\fR
on each database so the optimizer has useful statistics\&. You can also run
\fBvacuumdb \-a \-z\fR
to analyze all databases\&.
.PP
The dump script should not be expected to run completely without errors\&. In particular, because the script will issue
\fBCREATE ROLE\fR
for every role existing in the source cluster, it is certain to get a
\(lqrole already exists\(rq
error for the bootstrap superuser, unless the destination cluster was initialized with a different bootstrap superuser name\&. This error is harmless and should be ignored\&. Use of the
\fB\-\-clean\fR
option is likely to produce additional harmless error messages about non\-existent objects, although you can minimize those by adding
\fB\-\-if\-exists\fR\&.
.PP
pg_dumpall
requires all needed tablespace directories to exist before the restore; otherwise, database creation will fail for databases in non\-default locations\&.
.SH "EXAMPLES"
.PP
To dump all databases:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBpg_dumpall > db\&.out\fR
.fi
.if n \{\
.RE
.\}
.PP
To restore database(s) from this file, you can use:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBpsql \-f db\&.out postgres\fR
.fi
.if n \{\
.RE
.\}
.sp
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\&. An exception is that if you specified
\fB\-\-clean\fR, you must connect to the
postgres
database initially; the script will attempt to drop other databases immediately, and that will fail for the database you are connected to\&.
.SH "SEE ALSO"
.PP
Check
\fBpg_dump\fR(1)
for details on possible error conditions\&.