Using gs_dump and gs_dumpall to Export Data

Scenarios

GaussDB provides gs_dump and gs_dumpall to export required database objects and related information. You can use a tool to import the exported data to a target database for database migration. gs_dump can export a single database or its objects. gs_dumpall can export all databases or global objects in the database. For details, see Table 1.

In the multitenancy scenario, gs_dump can be used to export a single PDB or its objects, but gs_dumpall cannot support this scenario.

gs_dump and gs_dumpall use -U to specify the user that performs the export. If the specified user does not have the required permissions, data cannot be exported. For details about the scenarios where this function can be used, see Table 1.

Precautions

gs_dump and gs_dumpall encrypt the exported data files. These files are decrypted before being imported to prevent data disclosure for higher database security. Note that gsql cannot decrypt and import stored procedures and functions for plain-text files encrypted using gs_dump. Therefore, if the exported database contains stored procedures or functions, use the other three modes to export the database and use gs_restore to restore the database.

When gs_dump or gs_dumpall is used to export data, other users can still operate (read or write) the database.

gs_dump and gs_dumpall can export complete, consistent data. For example, if gs_dump exports data from database A or gs_dumpall exports data from GaussDB database at T1, the exported data is the data status of database A or GaussDB database at T1. Modified data of database A or GaussDB database after T1 will not be exported.

When gs_dump or gs_dumpall is used to export data, generated columns are not dumped.

• Do not modify the files and contents exported using the -F c/d/t format. Otherwise, the restoration may fail. For files exported using the -F p format, you can edit the exported files with caution if necessary.
• If the number of objects (data tables, views, and indexes) in the database exceeds 500,000, you are advised to contact technical support to improve performance and avoid memory problems.
• To ensure data consistency and integrity, the export tools will set a shared lock for the tables to be dumped. If a shared lock has been set for the table in other transactions, gs_dump and gs_dumpall lock the table after it is released. 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 parameter.
• During an export, gs_dumpall reads tables in all databases. Therefore, you need to connect to the databases as a database administrator to export a complete file. When you use gsql to execute scripts, administrator permissions are also required so as to add users and user groups, and create databases.

Preparing for Data Migration

1. Prepare an ECS or a device that can access the GaussDB instance over EIP.
   • To connect to a GaussDB instance through an ECS, you must first create an ECS.

     For details on how to create and log in to an ECS, see Purchasing an ECS and Logging In to an ECS in Elastic Cloud Server Getting Started.

   • To connect to a GaussDB instance through a device that can access the GaussDB instance over EIP, you must:
     1. Bind an EIP to the GaussDB instance. For details, see Binding an EIP.
     2. Ensure that the local device can access the EIP that has been bound to the GaussDB instance.
2. Install the gsql client on the prepared ECS or device that can access the GaussDB database, and connect it to the GaussDB database instance. For details, see Using gsql to Connect to a Database.

Exporting a Database

1. Create the database and table to be exported and insert data into them.

   create database gs_example;
   
   \c gs_example
   password: 
   
   create schema gs_sch_example;
   set search_path to gs_sch_example;
   create table gs_table_example
   (
   	col_1 integer,
   	col_2 text,
   	col_3 varchar(12),
   	col_4 date,
   	col_5 time
   );
   insert into gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
   insert into gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
   insert into gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');

2. Use gs_dump to export data of the gs_example database.
   • Example 1: Use gs_dump to export full information of the gs_example database by specifying the database IP address. The exported files are in .sql format.

     gs_dump -U root -f /tmp/data/gs_example_dump.sql -p 8000 gs_example -F p -h 192.*.*.139; 
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: The total objects number is 458.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: [100.00%] 458 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: total time: 8779  ms

   • Example 2: Use gs_dump to export full information of the gs_example database by specifying the database IP address. The exported information is archived to the /tmp/data/gs_example_dump.tar file in .tar format.

     gs_dump -U root -f /tmp/data/gs_example_dump.tar -p 8000 gs_example -F t -h 192.*.*.139; 
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: The total objects number is 458.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: [100.00%] 458 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: total time: 8201  ms

   • Example 3: Use gs_dump to export data of the gs_example database by specifying the database IP address. The exported data does not contain object definitions of the database. The exported files are in custom format.

     gs_dump -U root -f /tmp/data/gs_example_dump.dmp -p 8000 gs_example -a -F c -h 192.*.*.139; 
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:07:23]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:07:23]: total time: 8369  ms

   • Example 4: Use gs_dump to export all object definitions of the gs_example database by specifying the database IP address. The exported files are in .sql format.

     gs_dump -U root -f /tmp/data/gs_example_dump_s.sql -p 8000 gs_example -s -F p -h 192.*.*.139; 
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: The total objects number is 457.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: [100.00%] 457 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: total time: 8523  ms

   • Example 5: Use gs_dump to export all object definitions of the gs_example database by specifying the database IP address. The exported files are encrypted in .txt format.

     gs_dump -U root -f /tmp/data/gs_example_dump_s_key.sql -p 8000 gs_example --with-encryption AES128 --with-key abcdefg_?1234567 -s -F p -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: The total objects number is 457.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: [100.00%] 457 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: total time: 9101  ms

   Table 2 Common parameters

   Parameter	Description	Example
   -U	Username for database connection. NOTE: If the username for connecting to the database is not specified, the initial system administrator created during installation is used for connection by default.	-U jack
   -W	User password for database connection. This parameter is not required for database administrators if the trust policy is used for authentication. If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.	-W ********
   -f	Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output. If the output format is (-F c/-F d/-F t), the -f parameter must be specified.	-f /home/omm/backup/MPPDB_backup.tar
   -p	TCP port or local Unix-domain socket file name extension on which the server is listening for connections.	-p 8000
   dbname	Name of the database to be exported.	testdb
   -F	Format of exported files. The values are as follows: p: plain-text c: custom d: directory t: TAR	-F t

Exporting a Schema

1. Create a schema to be exported and insert data into it.

   create database gs_example;
   
   \c gs_example
   password: 
   
   create schema gs_sch_example;
   create schema gs_sch_1_example;
   create table gs_sch_example.gs_table_example
   (
   	col_1 integer,
   	col_2 text,
   	col_3 varchar(12),
   	col_4 date,
   	col_5 time
   );
   create table gs_sch_1_example.gs_table_example
   (
   	col_1 integer,
   	col_2 text,
   	col_3 varchar(12),
   	col_4 date,
   	col_5 time
   );
   insert into gs_sch_example.gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_example.gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
   insert into gs_sch_example.gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_1_example.gs_table_example values(7,'iamtext','iamvarchar','2006-07-07','12:00:00');
   insert into gs_sch_1_example.gs_table_example values(8,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_1_example.gs_table_example values(9,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_1_example.gs_table_example values(10,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_1_example.gs_table_example values(11,'sometext','somevarchar','2006-07-07', null);
   insert into gs_sch_1_example.gs_table_example values(12,'sometext','somevarchar','2006-07-07','19:00:02');

2. Use gs_dump to export schemas from the gs_example database at the same time.
   • Example 1: Use gs_dump to export the gs_sch_example and gs_sch_1_example schemas at the same time by specifying the database IP address. The exported files are in directory format.

     gs_dump -U root -f /tmp/data/gs_sch_dump -p 8000 gs_example -n gs_sch_example -n gs_sch_1_example -F d -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: The total objects number is 460.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: [100.00%] 460 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: dump schema gs_sch_example gs_sch_1_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: total time: 9602  ms

   • Example 2: Use gs_dump to export full information of the gs_sch_example schema by specifying the database IP address. The exported files are in .txt format.

     gs_dump -U root -f /tmp/data/gs_sch_dump.sql -p 8000 gs_example -n gs_sch_example -F p -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: The total objects number is 457.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: [100.00%] 457 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: dump schema gs_sch_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: total time: 8582  ms

   • Example 3: Use gs_dump to export data from the gs_example database by specifying the database IP address. The exported data does not contain the gs_sch_example schemas. The exported files are in custom format.

     gs_dump -U root -f /tmp/data/gs_sch_dump.dmp -p 8000 gs_example -N gs_sch_example -F c -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: The total objects number is 458.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: [100.00%] 458 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: total time: 8323  ms

   Table 3 Common parameters

   Parameter	Description	Example
   -U	Username for database connection.	-U jack
   -W	User password for database connection. This parameter is not required for database administrators if the trust policy is used for authentication. If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.	-W ********
   -f	Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.	-f /home/omm/backup/MPPDB_schema_backup
   -p	TCP port or local Unix-domain socket file name extension on which the server is listening for connections.	-p 8000
   dbname	Name of the database to be exported.	human_resource
   -n	Names of schemas to be exported. This option contains the schema and all its contained objects. Single schema: Enter -n schemaname. Multiple schemas: Enter -n schemaname for each schema.	Single schemas: -n hr Multiple schemas: -n hr -n public
   -F	Format of exported files. The values are as follows: p: plain-text c: custom d: directory t: TAR	-F d

Exporting a Table

1. Create a schema to be exported and insert data into it.

   create database gs_example;
   
   \c gs_example
   password: 
   
   create schema gs_sch_example;
   create table gs_sch_example.gs_table_example
   (
   	col_1 integer,
   	col_2 text,
   	col_3 varchar(12),
   	col_4 date,
   	col_5 time
   );
   create table gs_sch_example.gs_table_example_2
   (
   	col_1 integer,
   	col_2 text,
   	col_3 varchar(12),
   	col_4 date,
   	col_5 time
   );
   insert into gs_sch_example.gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_example.gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
   insert into gs_sch_example.gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_example.gs_table_example_2 values(7,'iamtext','iamvarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example_2 values(8,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example_2 values(9,'sometext','somevarchar','2006-07-07','12:00:00');
   insert into gs_sch_example.gs_table_example_2 values(10,'sometext','somevarchar','2006-07-07','19:00:02');
   insert into gs_sch_example.gs_table_example_2 values(11,'sometext','somevarchar','2006-07-07', null);
   insert into gs_sch_example.gs_table_example_2 values(12,'sometext','somevarchar','2006-07-07','19:00:02');

2. Use gs_dump to export the gs_sch_example.gs_table_example and gs_sch_example.gs_table_example_2 tables at the same time.
   

   1. In the following example, after the export, ensure that the schema to which the exported table belongs exists before the import.
   • Example 1: Use gs_dump to export the gs_sch_example.gs_table_example and gs_sch_example.gs_table_example_2 tables at the same time by specifying the database IP address. The exported files are in directory format.

     gs_dump -U root -f /tmp/data/gs_table_dump -p 8000 gs_example -t gs_sch_example.gs_table_example -t gs_sch_example.gs_table_example_2 -F d -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: The total objects number is 458.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: [100.00%] 458 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: dump table gs_sch_example.gs_table_example gs_sch_example.gs_table_example_2 successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: total time: 7694  ms

   • Example 2: Use gs_dump to export the tables excluding the gs_sch_example.gs_table_example_2 table by specifying the database IP address. The exported files are in custom format.

     gs_dump -U root -f /tmp/data/gs_table_dump.dmp -p 8000 gs_example -T gs_sch_example.gs_table_example_2 -F c -h 192.*.*.139;
     Password:
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: The total objects number is 461.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: [100.00%] 461 objects have been dumped.
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: dump database gs_example successfully
     gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: total time: 8203  ms

   Table 4 Common parameters

   Parameter	Description	Example
   -U	Username for database connection.	-U jack
   -W	User password for database connection. This parameter is not required for database administrators if the trust policy is used for authentication. If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.	-W ********
   -f	Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.	-f /home/omm/backup/MPPDB_table_backup
   -p	TCP port or local Unix-domain socket file name extension on which the server is listening for connections.	-p 8000
   dbname	Name of the database to be exported.	human_resource
   -t	Tables (or views, sequences, foreign tables) to export. You can specify multiple tables by listing them or using wildcard characters. When you use wildcard characters, quote the pattern to prevent the shell from expanding the wildcard characters. Single table: Enter -t schema.table. Multiple tables: Enter -t schema.table for each table.	Single table: -t hr.staffs Multiple tables: -t hr.staffs -t hr.employments
   -F	Format of exported files. The values are as follows: p: plain-text c: custom d: directory t: TAR	-F d
   -T	A list of tables, views, sequences, or foreign tables not to be dumped. You can use multiple -t parameters or wildcard characters to specify tables. When -t and -T are input, the object will be stored in -t list not -T table object.	-T table1

Exporting All Databases

Exporting Global Objects

Helpful Links

For more information, see:

• gs_dump (distributed)
• gs_dump (primary/standby)
• gs_dumpall (distributed)
• gs_dumpall (primary/standby)