原作者：计议

数据导入
gs_restore默认是以追加的形式导入，为了避免主键冲突或者数据重复尽量使用-c参数，先drop表。如果执行只导入数据不导入表结构，需要先把表中的数据truncate掉，然后再执行导入。

1 测试自定义归档模式

1.1 导入全库的时候不指定-c参数

导入前的数据情况

解释[omm@mogdb01 datadump]$ gsql -d mogtest3 -h 192.168.10.13 -U mog03 -p 26000 -W enmo@123
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:38:16 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


mogtest3=> select 'dump_tables',count(*) from dump_tables
mogtest3-> union all
mogtest3-> select 'dump_tables_1',count(*) from dump_tables_1;
   ?column?    | count 
---------------+-------
 dump_tables   |   126
 dump_tables_1 |   127
(2 rows)

执行导入

解释[omm@mogdb01 datadump]$ gs_restore -d mogtest3 /home/omm/datadump/dumpc1

start restore operation ...
Error while PROCESSING TOC:
Error from TOC entry 504; 1259 17009 TABLE dump_tables mog03
could not execute query: ERROR:  relation "dump_tables" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
    Command was: CREATE TABLE dump_tables (
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindexes...
Error from TOC entry 505; 1259 17012 TABLE dump_tables_1 mog03
could not execute query: ERROR:  relation "dump_tables_1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
    Command was: CREATE TABLE dump_tables_1 (
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindex...

table dump_tables complete data imported !
table dump_tables_1 complete data imported !
Finish reading 10 SQL statements!
end restore operation ...
WARNING: errors ignored on restore: 2
restore operation successful
total time: 33  ms

因为在数据库里表已经存在，所以创建表失败，查看数据是否导入成功

解释[omm@mogdb01 datadump]$ gsql -d mogtest3 -h 192.168.10.13 -U mog03 -p 26000 -W enmo@123
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:38:16 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

mogtest3=> select 'dump_tables',count(*) from dump_tables
mogtest3-> union all
mogtest3-> select 'dump_tables_1',count(*) from dump_tables_1;
   ?column?    | count 
---------------+-------
 dump_tables   |   252
 dump_tables_1 |   254
(2 rows)

可见表数据已经插入进去，因为数据默认是追加的方式，而且我的表中没有主键

1.2 导入全库的时候指定-c参数

解释[omm@mogdb01 datadump]$ gs_restore -d mogtest3 -c /home/omm/datadump/dumpc1
start restore operation ...
table dump_tables complete data imported !
table dump_tables_1 complete data imported !
Finish reading 10 SQL statements!
end restore operation ...
restore operation successful
total time: 77  ms

mogtest3=> select 'dump_tables',count(*) from dump_tables
mogtest3-> union all
mogtest3-> select 'dump_tables_1',count(*) from dump_tables_1;
   ?column?    | count 
---------------+-------
 dump_tables   |   126
 dump_tables_1 |   127
(2 rows)

可见-c参数是先删除表，再创建表，再导入数据

1.2 导入单表并且把现在的表中增加一个字段

mogtest3=> alter table dump_tables add column new_table name;
ALTER TABLE

从备份文件中导入dump_tables表

解释mogtest3=> \d dump_tables
              Table "public.dump_tables"
    Column     |           Type           | Modifiers 
---------------+--------------------------+-----------
 schemaname    | name                     | 
 tablename     | name                     | 
 tableowner    | name                     | 
 tablespace    | name                     | 
 hasindexes    | boolean                  | 
 hasrules      | boolean                  | 
 hastriggers   | boolean                  | 
 tablecreator  | name                     | 
 created       | timestamp with time zone | 
 last_ddl_time | timestamp with time zone | 
 new_table     | name                     | 


[omm@mogdb01 datadump]$ gs_restore -d mogtest3 -c /home/omm/datadump/dumpc1 --table=dump_tables
start restore operation ...
table dump_tables complete data imported !
Finish reading 10 SQL statements!
end restore operation ...
restore operation successful
total time: 25  ms

mogtest3=> \d dump_tables
              Table "public.dump_tables"
    Column     |           Type           | Modifiers 
---------------+--------------------------+-----------
 schemaname    | name                     | 
 tablename     | name                     | 
 tableowner    | name                     | 
 tablespace    | name                     | 
 hasindexes    | boolean                  | 
 hasrules      | boolean                  | 
 hastriggers   | boolean                  | 
 tablecreator  | name                     | 
 created       | timestamp with time zone | 
 last_ddl_time | timestamp with time zone | 

并且重新查询表，发现新建的字段已经不存在。

2 测试目录归档格式

2.1 测试tar格式

解释[omm@mogdb01 ~]$ gs_restore -d mogtest3 -c /home/omm/datat.tar
start restore operation ...
table dump_tables complete data imported !
table dump_tables_1 complete data imported !
Finish reading 10 SQL statements!
end restore operation ...
restore operation successful
total time: 55  ms


mogtest3-> select 'dump_tables_1',count(*) from dump_tables_1;
   ?column?    | count 
---------------+-------
 dump_tables   |   126
 dump_tables_1 |   127
(2 rows)

mogtest3=> \d dump_tables
              Table "public.dump_tables"
    Column     |           Type           | Modifiers 
---------------+--------------------------+-----------
 schemaname    | name                     | 
 tablename     | name                     | 
 tableowner    | name                     | 
 tablespace    | name                     | 
 hasindexes    | boolean                  | 
 hasrules      | boolean                  | 
 hastriggers   | boolean                  | 
 tablecreator  | name                     | 
 created       | timestamp with time zone | 
 last_ddl_time | timestamp with time zone | 

结果与自定义归档模式类似

2.2 测试导出文件格式

解释[omm@mogdb01 ~]$ gs_restore /home/omm/datat.tar --file=test.sql --list
restore operation successful
total time: 0  ms
[omm@mogdb01 ~]$ ls -lrt
total 52
drwx------ 2 omm omm    48 Oct 23 21:58 datadump
drwx------ 2 omm omm    75 Oct 23 22:00 dumpfile
-rw------- 1 omm omm  2818 Oct 23 22:02 toc.dat
-rw------- 1 omm omm  2865 Oct 23 22:02 restore.sql
-rw------- 1 omm omm  6498 Oct 23 22:02 4747.dat
-rw------- 1 omm omm  6396 Oct 23 22:02 4746.dat
-rw------- 1 omm omm 22528 Oct 23 22:02 datat.tar
-rw------- 1 omm omm   654 Oct 24 10:26 test.sql

[omm@mogdb01 ~]$ more test.sql 
;
; Archive created at Sun Oct 23 22:02:06 2022
;     dbname: mogtest3
;     TOC Entries: 10
;     Compression: 0
;     Dump Version: 1.12-0
;     Format: TAR
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.2.4
;     Dumped by gs_dump version: 9.2.4
;
;
; Selected TOC Entries:
;
4752; 1262 16828 DATABASE - mogtest3 mog03
8; 2615 2200 SCHEMA - public omm
4754; 0 0 COMMENT - SCHEMA public omm
4755; 0 0 ACL - public omm
504; 1259 17009 TABLE public dump_tables mog03
505; 1259 17012 TABLE public dump_tables_1 mog03
4746; 0 17009 TABLE DATA public dump_tables mog03
4747; 0 17012 TABLE DATA public dump_tables_1 mog03

gs_restore主要是针对gs_dump导出的文件格式，如果是从异构数据库导出的带格式的文本文件，需要使用gs_loader。