diff options
Diffstat (limited to 'doc/src/sgml/man1/pg_dumpall.1')
| -rw-r--r-- | doc/src/sgml/man1/pg_dumpall.1 | 587 |
1 files changed, 587 insertions, 0 deletions
diff --git a/doc/src/sgml/man1/pg_dumpall.1 b/doc/src/sgml/man1/pg_dumpall.1 new file mode 100644 index 0000000..45e6fc3 --- /dev/null +++ b/doc/src/sgml/man1/pg_dumpall.1 @@ -0,0 +1,587 @@ +'\" t +.\" Title: pg_dumpall +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "PG_DUMPALL" "1" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 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\&. |