greenplum单表恢复测试

gp version:4.3.16

os version: 

遇到一个场景：之前搭建好的集群环境中，有几张表没有建立压缩，目前每张表的容量大概在600GB左右。于是选择使用gp_dump和gp_restore来进行恢复测试。

1.#从master节点上导入数据测试

drop table amb_mac_cnt_bak;
CREATE TABLE amb_mac_cnt_bak (
    stat_date varchar(8),
    macid varchar(64),
    dtype text,
    name text,
    wifitype text,
    devicetype text,
    total bigint
) DISTRIBUTED BY (stat_date);

66G     amb_mac_cnt_bak.txt

导入数据所花时间：
haieredw=# copy amb_mac_cnt_bak from '/home/gpadmin/wxc/amb_mac_cnt_bak.txt' delimiter '|';
COPY 706995760
Time: 1685263.459 ms

2.使用gp_dump备份测试
备份时长：2min
gp_dump Command Line: --gp-d /export/gpbackup --gp-r /export/gpbackup/log -t haieredw.mytest.amb_mac_cnt_bak
Pass through Command Line Options: -t haieredw.mytest.amb_mac_cnt_bak
Compression Program: None
Backup Type: Full

Individual Results
        segment 15 (dbid 17) Host gp-s0009 Port 40003 Database haieredw BackupFile /export/gpbackup/gp_dump_15_17_20171208170105: Succeeded 
        segment 14 (dbid 16) Host gp-s0009 Port 40002 Database haieredw BackupFile /export/gpbackup/gp_dump_14_16_20171208170105: Succeeded 
        segment 13 (dbid 15) Host gp-s0009 Port 40001 Database haieredw BackupFile /export/gpbackup/gp_dump_13_15_20171208170105: Succeeded 
        segment 12 (dbid 14) Host gp-s0009 Port 40000 Database haieredw BackupFile /export/gpbackup/gp_dump_12_14_20171208170105: Succeeded 
        segment 11 (dbid 13) Host gp-s0008 Port 40003 Database haieredw BackupFile /export/gpbackup/gp_dump_11_13_20171208170105: Succeeded 
        segment 10 (dbid 12) Host gp-s0008 Port 40002 Database haieredw BackupFile /export/gpbackup/gp_dump_10_12_20171208170105: Succeeded 
        segment 9 (dbid 11) Host gp-s0008 Port 40001 Database haieredw BackupFile /export/gpbackup/gp_dump_9_11_20171208170105: Succeeded 
        segment 8 (dbid 10) Host gp-s0008 Port 40000 Database haieredw BackupFile /export/gpbackup/gp_dump_8_10_20171208170105: Succeeded 
        segment 7 (dbid 9) Host gp-s0007 Port 40003 Database haieredw BackupFile /export/gpbackup/gp_dump_7_9_20171208170105: Succeeded 
        segment 6 (dbid 8) Host gp-s0007 Port 40002 Database haieredw BackupFile /export/gpbackup/gp_dump_6_8_20171208170105: Succeeded 
        segment 5 (dbid 7) Host gp-s0007 Port 40001 Database haieredw BackupFile /export/gpbackup/gp_dump_5_7_20171208170105: Succeeded 
        segment 4 (dbid 6) Host gp-s0007 Port 40000 Database haieredw BackupFile /export/gpbackup/gp_dump_4_6_20171208170105: Succeeded 
        segment 3 (dbid 5) Host gp-s0010 Port 40003 Database haieredw BackupFile /export/gpbackup/gp_dump_3_5_20171208170105: Succeeded 
        segment 2 (dbid 4) Host gp-s0010 Port 40002 Database haieredw BackupFile /export/gpbackup/gp_dump_2_4_20171208170105: Succeeded 
        segment 1 (dbid 3) Host gp-s0010 Port 40001 Database haieredw BackupFile /export/gpbackup/gp_dump_1_3_20171208170105: Succeeded 
        segment 0 (dbid 2) Host gp-s0010 Port 40000 Database haieredw BackupFile /export/gpbackup/gp_dump_0_2_20171208170105: Succeeded 
        Master (dbid 1) Host sdw7 Port 2345 Database haieredw BackupFile /export/gpbackup/gp_dump_-1_1_20171208170105: Succeeded 
        Master (dbid 1) Host sdw7 Port 2345 Database haieredw BackupFile /export/gpbackup/gp_dump_-1_1_20171208170105_post_data: Succeeded 


gp_dump utility finished successfully.

3.使用gp_restore进行恢复测试

#重命名原表：
alter table amb_mac_cnt_bak rename to amb_mac_cnt_201712;
#恢复表数据
gp_restore -d haieredw --gp-d /export/gpbackup --gp-r /export/gpbackup/log --gp-k=20171208170105

20171208:17:21:26|gp_restore-[INFO]:-Report results also written to /export/gpbackup/log/gp_restore_20171208170105.rpt.


Greenplum Database Restore Report
Timestamp Key: 20171208170105
gp_restore Command Line: -d haieredw --gp-d /export/gpbackup --gp-r /export/gpbackup/log --gp-k=20171208170105
Pass through Command Line Options:  --post-data-schema-only
Compression Program: None


Individual Results
        Restore of haieredw on dbid 1 (sdw7:2345) from /export/gpbackup/gp_dump_-1_1_20171208170105_post_data: Succeeded
        Restore of haieredw on dbid 17 (gp-s0009:40003) from /export/gpbackup/gp_dump_15_17_20171208170105: Succeeded
        Restore of haieredw on dbid 16 (gp-s0009:40002) from /export/gpbackup/gp_dump_14_16_20171208170105: Succeeded
        Restore of haieredw on dbid 15 (gp-s0009:40001) from /export/gpbackup/gp_dump_13_15_20171208170105: Succeeded
        Restore of haieredw on dbid 14 (gp-s0009:40000) from /export/gpbackup/gp_dump_12_14_20171208170105: Succeeded
        Restore of haieredw on dbid 13 (gp-s0008:40003) from /export/gpbackup/gp_dump_11_13_20171208170105: Succeeded
        Restore of haieredw on dbid 12 (gp-s0008:40002) from /export/gpbackup/gp_dump_10_12_20171208170105: Succeeded
        Restore of haieredw on dbid 11 (gp-s0008:40001) from /export/gpbackup/gp_dump_9_11_20171208170105: Succeeded
        Restore of haieredw on dbid 10 (gp-s0008:40000) from /export/gpbackup/gp_dump_8_10_20171208170105: Succeeded
        Restore of haieredw on dbid 9 (gp-s0007:40003) from /export/gpbackup/gp_dump_7_9_20171208170105: Succeeded
        Restore of haieredw on dbid 8 (gp-s0007:40002) from /export/gpbackup/gp_dump_6_8_20171208170105: Succeeded
        Restore of haieredw on dbid 7 (gp-s0007:40001) from /export/gpbackup/gp_dump_5_7_20171208170105: Succeeded
        Restore of haieredw on dbid 6 (gp-s0007:40000) from /export/gpbackup/gp_dump_4_6_20171208170105: Succeeded
        Restore of haieredw on dbid 5 (gp-s0010:40003) from /export/gpbackup/gp_dump_3_5_20171208170105: Succeeded
        Restore of haieredw on dbid 4 (gp-s0010:40002) from /export/gpbackup/gp_dump_2_4_20171208170105: Succeeded
        Restore of haieredw on dbid 3 (gp-s0010:40001) from /export/gpbackup/gp_dump_1_3_20171208170105: Succeeded
        Restore of haieredw on dbid 2 (gp-s0010:40000) from /export/gpbackup/gp_dump_0_2_20171208170105: Succeeded


gp_restore  utility finished successfully.

一共花时15分钟。

4.问题：

使用gp_restore进行恢复后，遇到如下问题：

 dbid | content | role | preferred_role | mode | status | port  | hostname | address | replication_port | san_mounts 
------+---------+------+----------------+------+--------+-------+----------+---------+------------------+------------
   10 |       8 | p    | p              | s    | u      | 40000 | gp-s0008 | sdw8    |            41000 | 
   11 |       9 | p    | p              | s    | u      | 40001 | gp-s0008 | sdw8    |            41001 | 
   12 |      10 | p    | p              | s    | u      | 40002 | gp-s0008 | sdw8    |            41002 | 
   13 |      11 | p    | p              | s    | u      | 40003 | gp-s0008 | sdw8    |            41003 | 
   26 |       8 | m    | m              | s    | u      | 50000 | gp-s0009 | sdw9    |            51000 | 
   27 |       9 | m    | m              | s    | u      | 50001 | gp-s0009 | sdw9    |            51001 | 
   28 |      10 | m    | m              | s    | u      | 50002 | gp-s0009 | sdw9    |            51002 | 
   29 |      11 | m    | m              | s    | u      | 50003 | gp-s0009 | sdw9    |            51003 | 
    1 |      -1 | p    | p              | s    | u      |  2345 | sdw7     | sdw7    |                  | 
    6 |       4 | p    | p              | s    | u      | 40000 | gp-s0007 | sdw7    |            41000 | 
   22 |       4 | m    | m              | s    | u      | 50000 | gp-s0008 | sdw8    |            51000 | 
    7 |       5 | p    | p              | s    | u      | 40001 | gp-s0007 | sdw7    |            41001 | 
   23 |       5 | m    | m              | s    | u      | 50001 | gp-s0008 | sdw8    |            51001 | 
    8 |       6 | p    | p              | s    | u      | 40002 | gp-s0007 | sdw7    |            41002 | 
   24 |       6 | m    | m              | s    | u      | 50002 | gp-s0008 | sdw8    |            51002 | 
    9 |       7 | p    | p              | s    | u      | 40003 | gp-s0007 | sdw7    |            41003 | 
   25 |       7 | m    | m              | s    | u      | 50003 | gp-s0008 | sdw8    |            51003 | 
    2 |       0 | p    | p              | c    | u      | 40000 | gp-s0010 | sdw10   |            41000 | 
   18 |       0 | m    | m              | s    | d      | 50000 | gp-s0007 | sdw7    |            51000 | 
    3 |       1 | p    | p              | c    | u      | 40001 | gp-s0010 | sdw10   |            41001 | 
   19 |       1 | m    | m              | s    | d      | 50001 | gp-s0007 | sdw7    |            51001 | 
    4 |       2 | p    | p              | c    | u      | 40002 | gp-s0010 | sdw10   |            41002 | 
   20 |       2 | m    | m              | s    | d      | 50002 | gp-s0007 | sdw7    |            51002 | 
    5 |       3 | p    | p              | c    | u      | 40003 | gp-s0010 | sdw10   |            41003 | 
   21 |       3 | m    | m              | s    | d      | 50003 | gp-s0007 | sdw7    |            51003 | 
   14 |      12 | p    | p              | c    | u      | 40000 | gp-s0009 | sdw9    |            41000 | 
   30 |      12 | m    | m              | s    | d      | 50000 | gp-s0010 | sdw10   |            51000 | 
   15 |      13 | p    | p              | c    | u      | 40001 | gp-s0009 | sdw9    |            41001 | 
   31 |      13 | m    | m              | s    | d      | 50001 | gp-s0010 | sdw10   |            51001 | 
   16 |      14 | p    | p              | c    | u      | 40002 | gp-s0009 | sdw9    |            41002 | 
   32 |      14 | m    | m              | s    | d      | 50002 | gp-s0010 | sdw10   |            51002 | 
   17 |      15 | p    | p              | c    | u      | 40003 | gp-s0009 | sdw9    |            41003 | 
   33 |      15 | m    | m              | s    | d      | 50003 | gp-s0010 | sdw10   |            51003 | 

20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:--Current GPDB mirror list and status
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:--Type = Group
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:--------------------------------------------------------------
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   Mirror   Datadir                            Port    Status    Data Status    
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw7     /export/gpdata/gpdatam1/gpsegs0    50000   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw7     /export/gpdata/gpdatam2/gpsegs1    50001   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw7     /export/gpdata/gpdatam3/gpsegs2    50002   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw7     /export/gpdata/gpdatam4/gpsegs3    50003   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw8     /export/gpdata/gpdatam1/gpsegs4    50000   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw8     /export/gpdata/gpdatam2/gpsegs5    50001   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw8     /export/gpdata/gpdatam3/gpsegs6    50002   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw8     /export/gpdata/gpdatam4/gpsegs7    50003   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw9     /export/gpdata/gpdatam1/gpsegs8    50000   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw9     /export/gpdata/gpdatam2/gpsegs9    50001   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw9     /export/gpdata/gpdatam3/gpsegs10   50002   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:-   sdw9     /export/gpdata/gpdatam4/gpsegs11   50003   Passive   Synchronized
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw10    /export/gpdata/gpdatam1/gpsegs12   50000   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw10    /export/gpdata/gpdatam2/gpsegs13   50001   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw10    /export/gpdata/gpdatam3/gpsegs14   50002   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-sdw10    /export/gpdata/gpdatam4/gpsegs15   50003   Failed                   <<<<<<<<
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[INFO]:--------------------------------------------------------------
20171208:17:28:52:051809 gpstate:gp-s0007:gpadmin-[WARNING]:-8 segment(s) configured as mirror(s) have failed

措施：使用gprecoverseg进行恢复。

5. 把表使用列存储和压缩，再进行恢复，耗时3分钟，

结果如下

20171208:17:51:05|gp_restore-[INFO]:-restore started for source dbid 1, target dbid 1 on host sdw7
20171208:17:51:05|gp_restore-[INFO]:-restore succeeded for source dbid 1, target dbid 1 on host sdw7
20171208:17:51:05|gp_restore-[INFO]:-Successfully restored master database: host sdw7 port 2345 database haieredw
20171208:17:51:05|gp_restore-[INFO]:-Report results also written to /export/gpbackup/log/gp_restore_20171208170105.rpt.


Greenplum Database Restore Report
Timestamp Key: 20171208170105
gp_restore Command Line: -d haieredw --gp-d /export/gpbackup --gp-r /export/gpbackup/log --gp-k=20171208170105
Pass through Command Line Options:  --post-data-schema-only
Compression Program: None


Individual Results
        Restore of haieredw on dbid 1 (sdw7:2345) from /export/gpbackup/gp_dump_-1_1_20171208170105_post_data: Succeeded
        Restore of haieredw on dbid 17 (gp-s0009:40003) from /export/gpbackup/gp_dump_15_17_20171208170105: Succeeded
        Restore of haieredw on dbid 16 (gp-s0009:40002) from /export/gpbackup/gp_dump_14_16_20171208170105: Succeeded
        Restore of haieredw on dbid 15 (gp-s0009:40001) from /export/gpbackup/gp_dump_13_15_20171208170105: Succeeded
        Restore of haieredw on dbid 14 (gp-s0009:40000) from /export/gpbackup/gp_dump_12_14_20171208170105: Succeeded
        Restore of haieredw on dbid 13 (gp-s0008:40003) from /export/gpbackup/gp_dump_11_13_20171208170105: Succeeded
        Restore of haieredw on dbid 12 (gp-s0008:40002) from /export/gpbackup/gp_dump_10_12_20171208170105: Succeeded
        Restore of haieredw on dbid 11 (gp-s0008:40001) from /export/gpbackup/gp_dump_9_11_20171208170105: Succeeded
        Restore of haieredw on dbid 10 (gp-s0008:40000) from /export/gpbackup/gp_dump_8_10_20171208170105: Succeeded
        Restore of haieredw on dbid 9 (gp-s0007:40003) from /export/gpbackup/gp_dump_7_9_20171208170105: Succeeded
        Restore of haieredw on dbid 8 (gp-s0007:40002) from /export/gpbackup/gp_dump_6_8_20171208170105: Succeeded
        Restore of haieredw on dbid 7 (gp-s0007:40001) from /export/gpbackup/gp_dump_5_7_20171208170105: Succeeded
        Restore of haieredw on dbid 6 (gp-s0007:40000) from /export/gpbackup/gp_dump_4_6_20171208170105: Succeeded
        Restore of haieredw on dbid 5 (gp-s0010:40003) from /export/gpbackup/gp_dump_3_5_20171208170105: Succeeded
        Restore of haieredw on dbid 4 (gp-s0010:40002) from /export/gpbackup/gp_dump_2_4_20171208170105: Succeeded
        Restore of haieredw on dbid 3 (gp-s0010:40001) from /export/gpbackup/gp_dump_1_3_20171208170105: Succeeded
        Restore of haieredw on dbid 2 (gp-s0010:40000) from /export/gpbackup/gp_dump_0_2_20171208170105: Succeeded


gp_restore  utility finished successfully.

结果再次遭遇失败，如下所示，再次进行gprecoverseg进行懒得。

6.对两表容量进行测试：

CREATE TABLE amb_mac_cnt_bak (
    stat_date character varying(8) ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    macid character varying(32) ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    dtype text ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    name text ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    wifitype text ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    devicetype text ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768),
    total bigint ENCODING (compresslevel=5,compresstype=zlib,blocksize=32768)
)
WITH (appendonly=true, orientation=column, compresslevel=5) DISTRIBUTED BY (macid);

select pg_size_pretty(pg_total_relation_size('amb_mac_cnt_201712'));

select pg_size_pretty(pg_total_relation_size('amb_mac_cnt_bak')); #列存储并压缩

压缩量能到达20倍左右。

haieredw=# select pg_size_pretty(pg_total_relation_size('amb_mac_cnt_201712'));
select pg_size_pretty(pg_total_relation_size('amb_mac_cnt_bak'));  pg_size_pretty 
----------------
 90 GB
(1 row)


Time: 283.434 ms
haieredw=# select pg_size_pretty(pg_total_relation_size('amb_mac_cnt_bak')); 
 pg_size_pretty 
----------------
 4715 MB
(1 row)