gs_dumpall

Context

gs_dumpall, provided by GaussDB, is used to export all database information, including the data of the default postgres database, data of user-specified databases, and global objects of all databases in a cluster.

When gs_dumpall is used to export data, other users can still access (read/write) the databases in a cluster.

gs_dumpall can export complete, consistent data. For example, if gs_dumpall is started to export database from the entire cluster at T1, data of the cluster databases at that time point will be exported, and modifications on the cluster databases after that time point will not be exported.

gs_dumpall exports all databases from the entire cluster in two parts:

• gs_dumpall exports all global objects, including information about database users and groups, tablespaces, and attributes (for example, global access permissions).
• gs_dumpall invokes gs_dump to export SQL scripts from each cluster database, which contain all the SQL statements required to restore databases.

The exported files are both plain-text SQL scripts. Use gsql to execute them to restore cluster databases.

gs_dumpall supports SSL encrypted communication. The method is the same as that of using gsql.

Before using gs_dumpall, ensure that the gs_dumpall version is consistent with the gs_dump version and database version. gs_dumpall of a later version may not be fully compatible with the kernel data of an earlier version.

Precautions

• Do not modify any exported file. Otherwise, restoration may fail.
• To ensure the data consistency and integrity, gs_dumpall sets a share lock for a table to be dumped. If a share lock has been set for the table in other transactions, gs_dumpall locks the table after it is released. If the table cannot be locked within the specified time, the dump fails. You can customize the timeout interval to wait for lock release by specifying the --lock-wait-timeout parameter.
• During an export, gs_dumpall reads all tables in a database. Therefore, you need to connect to the database as a database cluster administrator to export a complete file. When you use gsql to execute SQL scripts, administrator permissions are also required to add users and user groups, and create databases. Before importing a backup, you need to verify the security to prevent administrator permissions from being exploited.
• If you use gs_dumpall to export all database objects and want to import them to a new instance environment, ensure that the names and permissions of the users used for the export and import are the same. Otherwise, an error message will be displayed, indicating that the names are inconsistent or the permissions are insufficient.
• For scheduled tasks, this tool can export only scheduled tasks created using CREATE EVENT or non-periodic scheduled tasks created using advanced packages from a MySQL-compatible database.

Syntax

gs_dumpall [OPTION]...

Parameters

Common parameters:

• -f, --filename=FILENAME

  Sends the output to the specified file. If this option is omitted, the standard output is used.

• -v, --verbose

  Specifies the verbose mode. This will cause gs_dumpall to output detailed object comments and start/stop times to the dump file, and progress messages to standard errors.

• -V, --version

  Prints the gs_dumpall version and exits.

• --lock-wait-timeout=TIMEOUT

  Do not keep waiting to obtain shared table locks at the beginning of the dump. Consider it as failed if you are unable to lock a table within the specified time. The timeout interval can be specified in any of the formats accepted by SET statement_timeout.

• -?, --help

  Displays help about the command line parameters for gs_dumpall and exits.

Dump parameters:

• -a, --data-only

  Dumps only the data, not the schema (data definition).

• -c, --clean

  Runs SQL statements to delete databases before rebuilding them. Statements for dumping roles and tablespaces are added.

• -g, --globals-only

  Dumps only global objects (roles and tablespaces) but no databases.

• -o, --oids

  Dumps OIDs as parts of the data in each table. Use this parameter if your application references the OID columns in some way. If the preceding situation does not occur, do not use this parameter.

• -O, --no-owner

  Do not output commands to set ownership of objects to match the original database. By default, gs_dumpall issues the ALTER OWNER or SET SESSION AUTHORIZATION statement to set ownership of created schema elements. These statements will fail when the script is running unless it is started by a system administrator (or the same user who owns all of the objects in the script). By specifying -O, you can compile a script that can be stored by any user. The script grants the user the permission to own all objects because the ALTER OWNER or SET SESSION AUTHORIZATION statement is not used and the execution user permission is always used during the import. Therefore, before importing the dump file, check whether there are risks in the dump file. For example, check whether the dump file contains a privilege escalation statement and whether the statement is known to the administrator.

• -r, --roles-only

  Dumps only roles but not databases or tablespaces.

• -s, --schema-only

  Dumps only the object definition (schema) but not data.

• -S, --sysadmin=NAME

  Reserved for function extension. The option is not recommended.

• -t, --tablespaces-only

  Dumps only tablespaces but not databases or roles.

• -x, --no-privileges

  Prevents the dumping of access permissions (grant/revoke commands).

• --column-inserts|--attribute-inserts

  Exports data by running the INSERT command with explicit column names {INSERT INTO table (column, ...) VALUES ...}. This will cause a slow restoration. However, since this option generates an independent command for each row, an error in reloading a row causes only the loss of the row rather than the entire table content.

• --disable-dollar-quoting

  Disables the use of dollar sign ($) for function bodies, and forces them to be quoted using the SQL standard string syntax.

• --disable-triggers

  Reserved for function extension. The option is not recommended.

• --inserts

  Dumps data when the INSERT statement (rather than COPY) is issued. This will cause a slow restoration. The restoration may fail if you rearrange the column order. The --column-inserts parameter is safer against column order changes, though even slower.

• --no-security-labels

  Reserved for function extension. The option is not recommended.

• --no-tablespaces

  Do not output statements to create tablespaces or select tablespaces for objects. All the objects will be created during the restoration process, no matter which tablespace is selected when using this option.

• --no-unlogged-table-data

  Reserved for function extension. The option is not recommended.

• --include-alter-table

  Exports information about deleted columns in the table.

• --quote-all-identifiers

  Forcibly quotes all identifiers. This parameter is useful when you dump a database for migration to a later version, in which additional keywords may be introduced.

• --dont-overwrite-file

  Do not overwrite the current file.

• --use-set-session-authorization

  Specifies that the standard SQL SET SESSION AUTHORIZATION command rather than ALTER OWNER is returned to ensure the object ownership. This makes dumping more standard. However, if a dump file contains objects that have historical problems, restoration may fail. A dump using SET SESSION AUTHORIZATION requires the system administrator permissions, whereas ALTER OWNER requires lower permissions. However, the SET SESSION AUTHORIZATION statement supports user and permission switching using a ciphertext password. The script exported using this parameter may not be restored. Therefore, you are advised not to use this parameter to export the script.

  

  Application scope of SET SESSION AUTHORIZATION:

  • The system administrator can switch to a common user through the SET SESSION AUTHORIZATION statement, but cannot switch to an initial user, sysadmin, opradmin, monadmin, poladmin, or auditadmin.
  • Other users cannot switch to another user through the SET SESSION AUTHORIZATION statement.
• --with-encryption=AES128

  Specifies that dumping data needs to be encrypted using AES128.

• --with-key=KEY

  The AES128 key rules are as follows:

  Consists of 8 to 16 characters.

  Contains at least three of the following character types: uppercase characters, lowercase characters, digits, and special characters (limited to ~!@#$ %^&*()-_=+\|[{}];:,<.>/?).

• --include-extensions

  Backs up all CREATE EXTENSION statements if the include-extensions parameter is set.

  

  The extended function is for internal use only. You are advised not to use it.

• --include-templatedb

  Includes template databases during the dump.

• --dump-nodes

  Includes nodes and Node Groups during the dump.

• --include-nodes

  Includes TO NODE statement in the dumped CREATE TABLE statement.

• --include-buckets

  Reserved for function extension. The option is not recommended.

• --dump-wrm

  Includes workload resource manager (resource pool, load group, and load group mapping) during the dump.

• --binary-upgrade

  Reserved for function extension. The option is not recommended.

  

  M-compatible databases do not support this option. This option is skipped when an M-compatible database is exported.

• --binary-upgrade-usermap="USER1=USER2"

  Reserved for function extension. The option is not recommended.

• --non-lock-table

  This parameter is used only for inter-software API calling.

• --tablespaces-postfix

  Reserved for function extension. The option is not recommended.

• --parallel-jobs

  Specifies the number of concurrent backup processes. The value range is 1–1000.

• --pipeline

  Uses a pipe to transmit the password. This parameter cannot be used on devices.

Description

• gs_dumpall internally invokes gs_dump. For details about the diagnosis information, see gs_dump.
• Once gs_dumpall is restored, it is advised to run ANALYZE on each database so that the optimizer can provide useful statistics.
• gs_dumpall requires all needed tablespace directories to be empty before the restoration. Otherwise, database creation will fail if the databases are in non-default locations.

Example

Use gs_dumpall to export all databases from a cluster at a time.

gs_dumpall supports only plain-text format export. Therefore, only gsql can be used to restore a file exported using gs_dumpall.

Related Commands

gs_dump and gs_restore