D7net
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
opt
/
td-agent
/
embedded
/
share
/
man
/
man1
/
Filename :
pg_dumpall.1
back
Copy
'\" t .\" Title: pg_dumpall .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.76.1 <http://docbook.sf.net/> .\" Date: 2014 .\" Manual: PostgreSQL 9.3.5 Documentation .\" Source: PostgreSQL 9.3.5 .\" Language: English .\" .TH "PG_DUMPALL" "1" "2014" "PostgreSQL 9.3.5" "PostgreSQL 9.3.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 .\" pg_dumpall .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 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\&. .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 users and groups, and to create databases\&. .PP The SQL script will be written to the standard output\&. Use the [\-f|file] 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 31.15, \(lqThe Password File\(rq, in the documentation for more information\&. .SH "OPTIONS" .PP The following command\-line options control the content and format of the output\&. .PP \fB\-a\fR, \fB\-\-data\-only\fR .RS 4 Dump only the data, not the schema (data definitions)\&. .RE .PP \fB\-c\fR, \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\-f \fR\fB\fIfilename\fR\fR, \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, \fB\-\-globals\-only\fR .RS 4 Dump only global objects (roles and tablespaces), no databases\&. .RE .PP \fB\-i\fR, \fB\-\-ignore\-version\fR .RS 4 A deprecated option that is now ignored\&. .RE .PP \fB\-o\fR, \fB\-\-oids\fR .RS 4 Dump object identifiers (OIDs) as part of the data for every table\&. Use this option if your application references the OID columns in some way (e\&.g\&., in a foreign key constraint)\&. Otherwise, this option should not be used\&. .RE .PP \fB\-O\fR, \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, \fB\-\-roles\-only\fR .RS 4 Dump only roles, no databases or tablespaces\&. .RE .PP \fB\-s\fR, \fB\-\-schema\-only\fR .RS 4 Dump only the object definitions (schema), not data\&. .RE .PP \fB\-S \fR\fB\fIusername\fR\fR, \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, \fB\-\-tablespaces\-only\fR .RS 4 Dump only tablespaces, no databases or roles\&. .RE .PP \fB\-v\fR, \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, \fB\-\-version\fR .RS 4 Print the pg_dumpall version and exit\&. .RE .PP \fB\-x\fR, \fB\-\-no\-privileges\fR, \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, \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 reloaded\&. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload\&. .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\-\-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\-\-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\-security\-labels\fR .RS 4 Do not dump security labels\&. .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\-\-quote\-all\-identifiers\fR .RS 4 Force quoting of all identifiers\&. This may be useful when dumping a database for migration to a future version that may have introduced additional keywords\&. .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, \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, \fB\-\-dbname=\fR\fB\fIconnstr\fR\fR .RS 4 Specifies parameters used to connect to the server, as a connection string\&. See Section 31.1.1, \(lqConnection Strings\(rq, in the documentation for more information\&. .sp The option is called \-\-dbname for consistency with other client applications, but because pg_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\&. .RE .PP \fB\-h \fR\fB\fIhost\fR\fR, \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, \fB\-\-database=\fR\fB\fIdbname\fR\fR .RS 4 Specifies the name of the database to connect to to dump global objects and discover 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, \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, \fB\-\-username=\fR\fB\fIusername\fR\fR .RS 4 User name to connect as\&. .RE .PP \fB\-w\fR, \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, \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, \fBPGOPTIONS\fR, \fBPGPORT\fR, \fBPGUSER\fR .RS 4 Default connection parameters .RE .PP This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 31.14, \(lqEnvironment Variables\(rq, in the documentation)\&. .SH "NOTES" .PP Since pg_dumpall calls pg_dump internally, some diagnostic messages will refer to pg_dump\&. .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 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 reload 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\&.) .SH "SEE ALSO" .PP Check \fBpg_dump\fR(1) for details on possible error conditions\&.