Help Center/ GaussDB/ User Guide/ Database Migration/ Using gs_restore to Import Data

Updated on 2024-10-08 GMT+08:00

View PDF

Using gs_restore to Import Data

Scenarios

gs_restore is an import tool provided by GaussDB and works together with gs_dump. You can use gs_restore to import the files exported by gs_dump to a database. gs_restore can import the files in .tar, custom, or directory format.

gs_restore can:

Import data to a database.
If a database is specified, data is imported to the database. If multiple databases are specified, the password for connecting to each database also needs to be specified. During data import, the generated columns are automatically updated and saved as common columns.
Import data to a script.
If no database is specified, a script containing the SQL statement to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain-text output of gs_dump.

You can specify and sort the data to import.

Precautions

gs_restore incrementally imports data by default. To prevent data exception caused by consecutive imports, use the -c and -e parameters for each import. -c indicates that the database objects that already exist in the database to be restored are cleared (deleted) before the database objects are rebuilt. -e indicates that if an error occurs when an SQL statement is sent to the database, the system exits. By default, the system continues to import data and displays a series of error information after the import is complete.

Preparing for Data Migration

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

Procedure

Upload the file exported by gs_dump to the device. For details about the file exported by gs_dump, see Using gs_dump and gs_dumpall to Export Data.

Use gs_restore to import the definitions of all database objects from the exported file to the target database.

Example 1: Use gs_restore to import the data and object definitions of the gs_example database from the gs_example_dump.tar file (in .tar format) by specifying the database IP address and an existing database (for example, gs_example_restore). In this example, only relative output information is included.
```
gs_restore -U root /tmp/data/gs_example_dump.tar -p 8000 -d gs_example_restore -e -h 192.*.*.139;
Password:
restore operation successful
total time: 1430  ms
```
Example 2: Use gs_restore to import the data and object definitions of the gs_example database from the gs_example_dump.tar file (in .tar format) by specifying the database IP address and an existing database (for example, gs_example_restore). In addition, the database objects that already exist in the database to be imported are cleared (deleted). In this example, only relative output information is included.
```
gs_restore -U root /tmp/data/gs_example_dump.tar -p 8000 -d gs_example_restore -e -c -h 192.*.*.139;
Password:
restore operation successful
total time: 1621  ms
```

**Table 1** 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 ********
-d	Name of a database to which data will be imported.	-d backupdb
-p	TCP port or local Unix-domain socket file name extension on which the server is listening for connections.	-p 8000
-e	If an error occurs when you send the SQL statement to the database, the system exits. Error messages are displayed after the import process is complete.	-e
-c	Before re-creating database objects, clear (delete) the database objects that exist in the database to be imported.	-c
-s	Only schema definitions are imported. Sequence values and data will not be imported.	-s