gs_restore

Context

gs_restore is a tool provided by GaussDB(DWS) to import data that was exported using gs_dump. It can also be used to import files that were exported using gs_dump.

It has the following functions:

Syntax

gs_restore [OPTION]... FILE
  • The FILE does not have a short or long parameter. It is used to specify the location for the archive files.
  • The dbname or -l parameter is required as prerequisites. Users cannot enter dbname and -l parameters at the same time.
  • gs_restore incrementally imports data by default. To prevent data exception caused by multiple import jobs, use the -e and -c parameters during the jobs. In this way, existing database objects in a target database are deleted before import; and errors during import will be ignored to proceed the import and the error information will be displayed after the import.

Parameter Description

Common parameters:

Import parameters

  • If any local additions need to be added to the template1 database during the installation, restore the output of gs_restore into an empty database with caution. Otherwise, you are likely to obtain errors due to duplicate definitions of the added objects. To create an empty database without any local additions, copy data from template0 rather than template1. Example:
CREATE DATABASE foo WITH TEMPLATE template0;
  • gs_restore cannot import large objects selectively. For example, it can only import the objects of a specified table. If an archive contains large objects, all large objects will be imported, or none of them will be restored if they are excluded by using -L, -t, or other parameters.

1. The -d/--dbname and -f/--file parameters do not coexist.

2. The -s/--schema-only and -a/--data-only parameters do not coexist.

3. The -c/--clean and -a/--data-only parameters do not coexist.

4. When --single-transaction is used, -j/--jobs must be a single job.

5. --role must be used in conjunction with --rolepassword.

Connection parameters:

Examples

Special case: Execute the gsql tool. Run the following commands to import the MPPDB_backup.sql file in the exported folder (in plain-text format) generated by gs_dump/gs_dumpall to the gaussdb database:

gsql -d gaussdb -p 8000 -W {password} -f /home/omm/test/MPPDB_backup.sql
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
SET
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
total time: 30476  ms

gs_restore is used to import the files exported by gs_dump.

Example 1: Execute the gs_restore tool to import the exported MPPDB_backup.dmp file (in custom format) to the gaussdb database.

gs_restore -W {password} backup/MPPDB_backup.dmp -p 8000 -d gaussdb
gs_restore: restore operation successful
gs_restore: total time: 13053  ms

Example 2: Execute the gs_restore tool to import the exported MPPDB_backup.tar file (in tar format) to the gaussdb database.

gs_restore backup/MPPDB_backup.tar -p 8000 -d gaussdb 
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore[2017-07-21 19:16:26]: total time: 21203  ms

Example 3: Execute the gs_restore tool to import the exported MPPDB_backup file (in directory format) to the gaussdb database.

gs_restore backup/MPPDB_backup -p 8000 -d gaussdb
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore[2017-07-21 19:16:26]: total time: 21003  ms

Example 4: Execute the gs_restore tool and run the following commands to import the MPPDB_backup.dmp file (in custom format). Specifically, import all the object definitions and data in the PUBLIC schema. Existing objects are deleted from the target database before the import. If an existing object references to an object in another schema, you need to manually delete the referenced object first.

gs_restore backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -c -n PUBLIC
gs_restore: [archiver (db)] Error while PROCESSING TOC:
gs_restore: [archiver (db)] Error from TOC entry 313; 1259 337399 TABLE table1 gaussdba
gs_restore: [archiver (db)] could not execute query: ERROR:  cannot drop table table1 because other objects depend on it
DETAIL:  view t1.v1 depends on table table1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
    Command was: DROP TABLE public.table1;

Manually delete the referenced object and create it again after the import is complete.

gs_restore backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -c -n PUBLIC
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore[2017-07-21 19:16:26]: total time: 2203  ms

Example 5: Execute the gs_restore tool and run the following commands to import the MPPDB_backup.dmp file (in custom format). Specifically, import only the definition of table1 in the PUBLIC schema.

gs_restore backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -c -s -n PUBLIC -t table1
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore[2017-07-21 19:16:26]: total time: 21000  ms

Example 6: Execute the gs_restore tool and run the following commands to import the MPPDB_backup.dmp file (in custom format). Specifically, import only the data of table1 in the PUBLIC schema.

gs_restore backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -a -n PUBLIC -t table1
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore[2017-07-21 19:16:26]: total time: 20203  ms

Helpful Links

gs_dump and gs_dumpall

Parent topic: Server Tool