'\" t .\" Title: pg_dumpall .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2023 .\" Manual: PostgreSQL 13.10 Documentation .\" Source: PostgreSQL 13.10 .\" Language: English .\" .TH "PG_DUMPALL" "1" "2023" "PostgreSQL 13.10" "PostgreSQL 13.10 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, that is, database roles and tablespaces\&. (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\ \&33.15 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 Include SQL commands to clean (drop) databases before recreating them\&. \fBDROP\fR commands for roles and tablespaces are added as well\&. .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\&. It will also enable verbose output in 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 conditional commands (i\&.e\&., add an IF EXISTS clause) to drop databases and other objects\&. 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\&. Allowed values vary depending on the server version you are dumping from, but an integer number of milliseconds is accepted by all versions since 7\&.3\&. This option is ignored when dumping from a pre\-7\&.3 server\&. .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\-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\-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\ \&33.14)\&. .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\&.