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.
When gs_dumpall is used to export data, other users can still access (read or write) the database.
gs_dumpall can export complete, consistent data. For example, if gs_dumpall is started to export entire database at T1, data of the databases at that time point will be exported, and modifications on the databases after that time point will not be exported.
The generated columns are not dumped during gs_dumpall is used.
gs_dumpall exports all databases 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 calls gs_dump to export SQL scripts from each 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 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_dump and gs_dump 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 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.
- When files exported using gs_dumpall are imported to a new instance, the connected database must be the default database. Otherwise, syntax incompatibility may occur, resulting in import errors.
- Only one M-compatible database can be created globally. If an M-compatible database is exported, ensure that no other M-compatible database exists in the target instance environment.
- 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 B-compatible database.
Syntax
gs_dumpall [OPTION]...
Parameters
Common parameters:
- -f, --filename=<FILE_NAME>
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
- --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
- -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 option if your application references the OID columns in some way (for example, in a foreign key constraint). 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 execute 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
- -s, --schema-only
- -S, --sysadmin=NAME
Reserved for function extension. The option is not recommended.
- -t, --tablespaces-only
- -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.
M-compatible databases do not support this option. This option is skipped when an M-compatible database is exported.
- --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.
M-compatible databases do not support this option. This option is skipped when an M-compatible database is exported.
- --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
- --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.
M-compatible databases do not support this option. This option is skipped when an M-compatible database is exported.
- --dont-overwrite-file
- --use-set-session-authorization
Specifies that the standard SQL SET SESSION AUTHORIZATION command rather than ALTER OWNER is generated to determine 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
- --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
- --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.
- The -g/--globals-only and -r/--roles-only parameters do not coexist.
- The -g/--globals-only and -t/--tablespaces-only parameters do not coexist.
- The -r/--roles-only and -t/--tablespaces-only parameters do not coexist.
- The -s/--schema-only and -a/--data-only parameters do not coexist.
- The -r/--roles-only and -a/--data-only parameters do not coexist.
- The -t/--tablespaces-only and -a/--data-only parameters do not coexist.
- The -g/--globals-only and -a/--data-only parameters do not coexist.
- --tablespaces-postfix must be used with --binary-upgrade.
- --binary-upgrade-usermap must be used with --binary-upgrade.
- --parallel-jobs must be used with -f/--file.
Connection parameters:
- -h, --host=HOSTNAME
Specifies the host name. If the value begins with a slash (/), it is used as the directory for the Unix domain socket. The default value is taken from the PGHOST environment variable. If it is not set, a Unix domain socket connection is attempted.
This parameter is valid only for the external database. For the local host in the database, only 127.0.0.1 can be used.
Environment variable: PGHOST
- -l, --database=DATABASENAME
Specifies the name of the database connected to dump all objects and discover other databases to be dumped. If this parameter is not specified, the postgres database will be used. If the postgres database does not exist, template1 will be used.
No matter which database is specified as the database to be connected for export, you must connect to postgres when using gsql to import data to a new instance. Otherwise, syntax incompatibility may occur, resulting in import errors. In particular, if the M-compatible database is incorrectly connected for import, the creation fails because CREATE DATABASE on the M-compatible database is equivalent to CREATE SCHEMA.
- -p, --port=PORT
Specifies the TCP port listened on by the server or the local Unix domain socket file name extension to ensure a correct connection. The default value is the PGPORT environment variable.
If the thread pool is enabled, you are advised to use the pooler port, that is, the listening port number plus 1.
Environment variable: PGPORT
- -U, --username=NAME
Specifies the name of the connected user. The initial user cannot be used for cross-node execution.
Environment variable: PGUSER
- -w, --no-password
Never issues a password prompt. The connection attempt fails if the server requires password for authentication and the password is not provided in other ways. This parameter is useful in batch jobs and scripts in which no user password is required.
- -W, --password=PASSWORD
Specifies the user password for connection. If the authentication policy of the host is trust, the password of the system administrator is not verified. That is, you do not need to enter the -W option. If this parameter is not specified and you are not a system administrator, the system prompts you to enter the password in interactive mode. To ensure system security, you are advised to enter the password in interactive mode.
- --role=ROLENAME
Specifies a role name to be used for creating the dump. This option causes gs_dumpall to issue the SET ROLE statement after connecting to the database. It is useful when the authenticated user (specified by -U) lacks the permissions required by gs_dumpall. It allows the user to switch to a role with the required permissions. Some installations have a policy against logging in directly as a system administrator. This option allows dumping data without violating the policy.
- --rolepassword=ROLEPASSWORD
Description
- gs_dumpall internally calls 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 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.
gs_dumpall -U omm -f backup/bkp2.sql -p 37300 gs_dump[user='omm'][localhost][port='37300'][dbname='testdb'][2018-06-27 09:55:09]: The total objects number is 2371. gs_dump[user='omm'][localhost][port='37300'][dbname='testdb'][2018-06-27 09:55:35]: [100.00%] 2371 objects have been dumped. gs_dump[user='omm'][localhost][port='37300'][dbname='testdb'][2018-06-27 09:55:46]: dump database dbname='testdb' successfully gs_dump[user='omm'][localhost][port='37300'][dbname='testdb'][2018-06-27 09:55:46]: total time: 55567 ms gs_dumpall[user='omm'][localhost][port='37300'][2018-06-27 09:55:46]: dumpall operation successful gs_dumpall[user='omm'][localhost][port='37300'][2018-06-27 09:55:46]: total time: 56088 ms In the preceding command, backup/bkp2.sql indicates the exported file, 37300 indicates the port number of the database server, and omm indicates the username.
Related Commands
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot