gs_dumpall

Context

gs_dumpall, provided by openGauss, is used to export all openGauss database information, including data of the default database postgres, user-defined databases, and common global objects of all openGauss databases.

gs_dumpall is executed by OS user omm.

When gs_dumpall is used to export data, other users can still access (read or write) openGauss databases.

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

The generated columns are not dumped during gs_dumpall is used.

gs_dumpall exports all openGauss 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 openGauss 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 openGauss databases.

Precautions

  • Do not modify an exported file or its content. Otherwise, restoration may fail.
  • To ensure the data consistency and integrity, gs_dumpall acquires a share lock on a table to be dumped. If another transaction has acquired a share lock on the table, gs_dumpall waits until this lock is released and then locks the table for dumping. If the table cannot be locked within the specified time, the dump fails. You can customize the timeout duration to wait for lock release by specifying the --lock-wait-timeout option.
  • 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.
  • If the database contains time series tables, gs_dumpall cannot be used to export data.

Syntax

gs_dumpall [OPTION]...

Parameter Description

Common parameters:

  • -f, --filename=FILENAME

    Sends the output to the specified file. If this parameter is omitted, the standard output is generated.

  • -v, --verbose

    Specifies the verbose mode. If it is specified, gs_dumpall writes detailed object comments and the number of startups/stops to the dump file, and progress messages to standard error.

  • -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 period 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 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

    Does 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 command to set ownership of created schema objects. These statements will fail when the script is running unless it is started by a system administrator (or the same user that owns all of the objects in the script). To make a script that can be stored by any user and give the user ownership of all objects, specify -O.

  • -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

    Name of the system administrator during the dump.

  • -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

    Specifies a reserved port for function expansion. This parameter 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

    Specifies a reserved port for function expansion. This parameter is not recommended.

  • --no-tablespaces

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

  • --no-unlogged-table-data

    Specifies a reserved port for function expansion. This parameter 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

    Does 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.

  • --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.

  • --include-templatedb

    Includes template databases during the dump.

  • --binary-upgrade

    Specifies a reserved port for function expansion. This parameter is not recommended.

  • --binary-upgrade-usermap=“USER1=USER2”

    Specifies a reserved port for function expansion. This parameter is not recommended.

  • --non-lock-table

    This parameter is used only by the OM tool.

  • --tablespaces-postfix

    Specifies a reserved port for function expansion. This parameter 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.

NOTE:

  • -g/--globals-only and -r/--roles-only do not coexist.
  • -g/--globals-only and -t/--tablespaces-only do not coexist.
  • -r/--roles-only and -t/--tablespaces-only do not coexist.
  • -s/--schema-only and -a/--data-only do not coexist.
  • -r/--roles-only and -a/--data-only do not coexist.
  • -t/--tablespaces-only and -a/--data-only do not coexist.
  • -g/--globals-only and -a/--data-only do not coexist.
  • --tablespaces-postfix must be used in conjunction with --binary-upgrade.
  • --binary-upgrade-usermap must be used in conjunction with --binary-upgrade.
  • --parallel-jobs must be used in conjunction with -f/--file.

Connection parameters:

  • -h, --host

    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 used only for defining names of the hosts outside openGauss. The names of the hosts inside openGauss must be 127.0.0.1.

    Environment variable: PGHOST

  • -l, --database

    Specifies the name of the database connected to dump global 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.

  • -p, --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 pooler port, that is, the listening port number plus 1.

    Environment variable: PGPORT

  • -U, --username

    Specifies the username for connection.

    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 option is useful in batch jobs and scripts in which no user password is required.

  • -W, --password

    Specifies the user password for connection. If the host uses the trust authentication policy, the administrator does not need to enter the -W option. If the -W option is not provided and you are not a system administrator, the Dump Restore tool will ask you to enter a password.

  • --role

    Specifies a role name to be used for creating the dump. If this option is selected, the SET ROLE command will be issued after the database is connected to gs_dumpall. 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

    Specifies the password of the specific role.

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 exit before the restoration. Otherwise, database creation will fail if the databases are in non-default locations.

Examples

Use gs_dumpall to export all openGauss databases at a time.

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

gs_dumpall -f backup/bkp2.sql -p 37300
Password:
gs_dump[port='37300'][dbname='postgres'][2018-06-27 09:55:09]: The total objects number is 2371.
gs_dump[port='37300'][dbname='postgres'][2018-06-27 09:55:35]: [100.00%] 2371 objects have been dumped.
gs_dump[port='37300'][dbname='postgres'][2018-06-27 09:55:46]: dump database dbname='postgres' successfully
gs_dump[port='37300'][dbname='postgres'][2018-06-27 09:55:46]: total time: 55567  ms
gs_dumpall[port='37300'][2018-06-27 09:55:46]: dumpall operation successful
gs_dumpall[port='37300'][2018-06-27 09:55:46]: total time: 56088  ms
In the preceding command, backup/bkp2.sql indicates the exported file, and 37300 indicates the port number of the database server.

Helpful Links

gs_dump

Feedback
编组 3备份
    openGauss 2024-04-22 00:47:24
    cancel