Manager Handbook for Centralized AntDB-T - P5–4
Published: 2023-08-18 09:02:31

pg_dumpall
pg_dumpall - Extracts an AntDB database cluster into a script file.
Outline

```
pg_dumpall [connection-option...] [option...]
```
Description
The pg_dumpall tool can write out ("dump") all AntDB databases in a cluster to a script file. This script file contains input SQL commands that can be used as psql to recover the database. It will call pg_dump for each database in the cluster to do the job. pg_dumpall also dumps global objects that are common to all databases (pg_dump does not save these objects), meaning that database roles and tablespaces are dumped. This currently includes properties such as appropriate database users and groups, tablespaces, and access rights appropriate for all databases.
Since pg_dumpall reads tables from all databases, you will likely need to connect as a database superuser in order to generate a full dump. Likewise, you will need superuser privileges to execute the saved scripts in order to add roles and groups and create databases.
The SQL script will be written out to standard output. Use the -f/--file option or the shell operator to redirect it to a file.
pg_dumpall requires multiple connections to the AntDB server (one per database). If you use password authentication, you may be asked for a password each time. It is easier to use one~/.pgpass in this case.

option
The following command row options are used to control the content and format of the output.

```
-a
--data-only
Dump only data, not dump mode (data definition).

-c
--clean
Includes SQL commands to clean (remove) databases before rebuilding them. DROP commands for roles and tablespaces will also be added.

-E encoding
--encoding=encoding
Creates a dump with the specified character set encoding. By default, dumps are created using the encoding of the database (another way to get the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding).

-f filename
--file=filename
Sends the output to the specified file. If omitted, standard output is used.
-g
--globals-only
Only global objects (roles and table spaces) are dumped, not databases.
-O
--no-owner
Commands for setting object ownership to conform to the original database are not output. By default, pg_dumpall issue an ALTER OWNER or SET SESSION AUTHORIZATION statement to set ownership of the created schema element. Unless the script is run by a superuser (or the same user who owns all the objects in the script), these statements fail when the script runs. To make a script recoverable by any user, but do not want to give the user ownership of all objects, you can specify -O.
-r
--roles-only
Only the role is dumped, not the database and table space.
-s
--schema-only
Only the object definition (schema) is dumped, not the data.
-S username
--superuser=username
Specifies the user name of the superuser to use when disabling the trigger. This option is only relevant if --disable-triggers is used (usually, it is better to omit this option and start the resulting script as superuser instead).
-t
--tablespaces-only
Only table spaces are dumped, not databases and roles.
-v
--verbose
Specifies the detail mode. This causes the pg_dumpall to output detailed object comments to standard errors along with the start/stop time of the dump file, as well as progress messages. It also enables detail output in the pg_dump.
-V
--version
Print pg_dumpall version and exit.
-x
--no-privileges
--no-acl
Prevent dump access privileges (grant/revoke commands).
--binary-upgrade
This option is used for in-place upgrades. We do not recommend or support its use for other purposes. This option is subject to change in future releases without notice.
--column-inserts
--attribute-inserts
Dump data as an INSERT command with explicit column names (INSERT INTO table(column, ...) VALUES ... ）。 This will make the recovery process very slow, which is mainly used to enable dumps to be loaded into non-AntDB databases.

--disable-dollar-quoting
This option disables the use of dollar sign references in function bodies and forces them to be referenced using SQL standard string syntax.
--disable-triggers
This option is only relevant if you create a dump that dumps only data. It indicates pg_dumpall includes commands that temporarily disable triggers on the target table when the data is reloaded. Use this option if you have referential integrity checks or other triggers on the table and you don't want to call them during data reloading.
Currently, commands issued for --disable-triggers must be executed as superuser. Therefore, you should also use -S to specify a superusername, or rather start the resulting script as a superuser.
--exclude-database=pattern
Do not dump databases with names that match patterns. Multiple patterns can be excluded by writing multiple --exclude-database switches. The pattern parameter is interpreted as a pattern, according to the same rules used by the d command of psql (see Patterns below), so multiple databases can also be excluded by writing wildcards in the schema. When using wildcards, be cautious about referencing patterns if needed to prevent shell wildcard extensions.
--extra-float-digits=ndigits
Use extra_float_digits specified values instead of the maximum available precision when dumping floating-point data. This option is not used for regular dumps for backup purposes.
--if-exists
A time-conditional command (i.e. adding an IF EXISTS clause) to clear the database and other objects. This option is only available if --clean is also specified.
--inserts
Dump the data as an INSERT command (not COPY). This will make recovery very slow, which is mainly used to enable dumps to be loaded into non-AntDB databases. Note If you have rearranged the column order, the recovery may fail together. The --column-inserts option is safe for column order changes, but is slower.
--load-via-partition-root
When dumping data for a partitioned table, have the COPY statement or INSERT statement target the root of the partition hierarchy that contains it, rather than the partition itself. This results in the appropriate partitioning being redetermined for each row as the data is loaded. This option is useful if rows do not always fall into the same partition as the original server when data is reloaded on one server. This can happen, for example, if the partition column is of type text and the collation used to sort the partition column in the two systems has different definitions.
--lock-wait-timeout=timeout
At the beginning of the dump, you never wait for a shared table lock to be acquired. Instead, it fails when a table cannot be locked within the specified timeout. The timeout length can be specified in any format accepted by the SET statement_timeout (the allowed value varies depending on the server version you are transferring from, but all versions since 7.3 accept an integer in milliseconds.) This option is ignored if rolling out of a pre-7.3 server. ）。
--no-comments
Comments are not dumped.
--no-publications
Do not dump publication.

--no-role-passwords
The password is not dumped for the role. After recovery, the character's password will be empty, and password authentication will not succeed until the password is set. Since no password value is required to specify this option, the role information is read from the catalog view pg_roles not the pg_authid. Therefore, this option can also help if access to pg_authid is restricted by a security policy.

--no-security-labels
Security labels are not dumped.

--no-subscriptions
Do not dump subscriptions.

--no-sync
By default, the pg_dumpall will wait for all files to be safely written to disk. This option will allow the pg_dumpall to return without this wait, which will be faster, but it means that subsequent operating system crashes may leave a corrupted dump. In general, this option is useful for testing, but should not be used when dumping data from a production installation.
--no-tablespaces
Do not output commands that select tablespaces. With this option, all objects will be created in any tablespace as default during recovery.
--no-unlogged-table-data
The contents of non-logging tables are not dumped. This option has no effect on whether the table definition (schema) is dumped, it only restricts the dumping of table data.

--on-conflict-do-nothing
ADD ON CONFLICT DO NOTHING TO THE INSERT COMMAND. This option does not take effect unless --inserts or --column-inserts are also specified.

--quote-all-identifiers
Force references to all identifiers. This option is recommended when dumping a database from an AntDB server that is different from the pg_dumpall major version, or when loading output to a server with a different major version. By default, pg_dumpall only enclose quotation marks around identifiers that are reserved for their major version. This can sometimes cause compatibility issues when interacting with other versions of servers that have different collections of reserved words. Using --quote-all-identifiers can prevent such problems, but at the cost of dump scripts being harder to read.

--rows-per-insert=nrows
Dump the data as an INSERT command (not COPY). Controls the maximum number of lines per INSERT command. The specified value must be a number greater than zero. Any error during reload will result in the loss of only the rows of the INSERT in question, not the entire table contents.

--use-set-session-authorization
Output SQL-The standard SET SESSION AUTHORIZATION command replaces the ALTER OWNER command to determine all relationships for an object. This makes the dump more standards-compliant, but depending on the history of the objects in the dump, the dump may not recover properly.

-?
--help
Displays help for pg_dumpall command-line arguments and exits.

The following command-line options control database connection parameters.

-d connstr
--dbname=connstr
Specify parameters for connecting to the server, such as a connection string; These override any conflicting command-line options.

This option is called --dbname to be consistent with other client applications, but because pg_dumpall need to connect to multiple databases, the database name in the connection string will be ignored. Use the -l option to specify a database that is used for the initial connection, which will dump global objects and discover which other databases need to be dumped.

-h host
--host=host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as a directory for Unix domain sockets. The default is obtained from the PGHOST environment variable (if set), otherwise a Unix domain socket connection will be attempted.

-l dbname
--database=dbname
Specifies which database to connect to dump global objects and which other databases to dump. If not specified, the postgres database will be used, and template1 will be used if postgres does not exist.

-p port
--port=port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. The default is to put it in the PGPORT environment variable (if set), otherwise use the default value compiled in the program.

-U username
--username=username
Which user to connect as.

-w
--no-password
Never issue a password prompt. If the server requires password authentication and there is no other way to provide the password (for example, a .pgpass file), the connection attempt will fail. This option is useful for batch tasks and scripts where none of the users enter the password.

-W
--password
Force pg_dumpall prompt for a password before connecting to a database.

This option is never required, because if the server requires password authentication, pg_dumpall will automatically prompt for a password. However, pg_dumpall will waste a connection attempt to discover that the server wants a password. In some cases, it is worth typing -W to avoid additional connection attempts.
Note that for each database to be dumped, the password prompt appears again. In general, it is best to set up a ~/.pgpass file to reduce manual password entry.

--role=rolename
```
Specifies the name of the role used to create the dump. This option causes pg_dump to issue a SET ROLE rolename command after connecting to the database. This option is useful when an authenticated user (specified by -U) lacks the required privileges for pg_dump but is able to switch to a role with the required rights. Some installations have policies for logging in directly as superuser, and this option allows dumps to complete without violating that policy.
For example:

```
To dump all databases:
$ pg_dumpall > db.out

To reload the database from this file, you can use it：
$ psql -f db.out postgres
```