Background information
gs_restore is an import tool provided by openGauss for exporting data for gs_dump. This tool allows you to import the export file generated by gs_dump.
The gs_restore tool is executed by the operating system user ommm.
The main functions include:
- Import to database
If a database is specified in the connection parameter, the data will be imported into the specified database. Among them, the connection password must be specified for parallel import. The generated column is automatically updated when imported and saved like a normal column.
- Import to script file
If the import database is not specified, create a SQL statement script that contains the necessary to rebuild the database and write it to a file or standard output. It is equivalent to directly exporting to plain text format using gs_dump.
Command format
gs_restore [OPTION]... FILE
illustrate:
- FILE has no short or long options. Used to specify the location of the archive file.
- As a prerequisite, you need to enter the dbname or -l option. Users are not allowed to enter the dbname and -l options at the same time.
- gs_restore imports data in append mode by default. To avoid data exceptions caused by multiple imports, it is recommended to use the "-c" parameter when importing. Before recreating the database object, clean (delete) the database objects that already exist in the database to be restored.
- Log printing has no switch. If you need to hide the log, please redirect the log to the log file. If the table data is restored, the amount of data is large and it will be restored in batches, so the log "table data has been imported" will appear many times.
Table 1Common parameters description
parameter |
Parameter description |
Give an example |
|---|---|---|
-U |
The user name to connect to the database. |
-U jack |
-W |
Specify the password for the user to connect.
|
-W abcd@123 |
-d |
Connect to the database dbname and import the data directly into the database. |
-d backupdb |
-p |
Specifies the TCP port or local Unix domain socket suffix that the server is listening on to to ensure the connection. |
-p 8000 |
-e |
If an error occurs when sending SQL statements to the database, exit. By default, the error task is ignored and the import continues, and a series of error messages are displayed after the import. |
- |
-c |
Before recreating the database object, clean (delete) the database object that already exists in the database to be imported. |
- |
-s |
Only import schema definitions, not import data. The current sequence value will not be imported either. |
- |
Example
Special example: Execute the gsql program and use the following options to import the MPPDB_backup.sql file that generates the export folder (plain text format) from gs_dump/gs_dumpall to the postgres database.
gsql -d postgres -p 15400 -W Bigdata@123 -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 export file generated by gs_dump.
Example 1: Execute gs_restore and import the exported MPPDB_backup.dmp file (custom archive format) into the postgres database.
gs_restore -W Bigdata@123 backup/MPPDB_backup.dmp -p 15400 -d postgres gs_restore: restore operation successful gs_restore: total time: 13053 ms
Example 2: Execute gs_restore and import the exported MPPDB_backup.tar file (tar format) into the postgres database.
gs_restore backup/MPPDB_backup.tar -p 15400 -d postgres 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 gs_restore and import the exported MPPDB_backup file (directory format) into the postgres database.
gs_restore backup/MPPDB_backup -p 15400 -d postgres 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 gs_restore and use the MPPDB_backup.dmp file in a custom archive format to perform the following import operations. Import the definitions and data of all objects in PUBLIC mode. When importing, the existing objects will be deleted first. If the original object has cross-patterned dependencies, it requires manual forced intervention.
gs_restore backup/MPPDB_backup.dmp -p 15400 -d postgres -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 dependencies and recreate them after the import is completed.
gs_restore backup/MPPDB_backup.dmp -p 15400 -d postgres -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 gs_restore and use the MPPDB_backup.dmp file in a custom archive format to perform the following import operations. Only import the definition of table table1 in PUBLIC mode.
gs_restore backup/MPPDB_backup.dmp -p 15400 -d postgres -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 gs_restore and use the MPPDB_backup.dmp file in a custom archive format to perform the following import operations. Only import data of table1 in PUBLIC mode.
gs_restore backup/MPPDB_backup.dmp -p 15400 -d postgres -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
Summarize
This is the end of this article about importing data using gs_restore. For more related contents of importing data from gs_restore, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!