greenplum单表恢复测试
来源:互联网 发布:帝国时代3 mac 编辑:程序博客网 时间:2024/05/23 23:24
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_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)
- greenplum单表恢复测试
- mysql单表恢复--测试
- greenplum恢复standby
- greenplum备份与恢复
- greenplum segment恢复的过程
- Greenplum gpdbrestore 全量恢复
- MySQL单表恢复方法
- greenplum 节点失败后恢复步骤
- greenplum数据倾斜爆库--库恢复
- Greenplum中备份和恢复数据
- greenplum恢复失败的segment的方法
- ODU恢复测试一:truncate表恢复
- dul恢复truncate表测试
- dul恢复drop表测试
- dul恢复drop表测试
- Greenplum管理表
- Greenplum 创建表空间
- Greenplum-表空间笔记
- Git 仓库迁移
- 在Fluent中实现自动操作
- [知了堂学习笔记] javascript DOM学习
- 感受野计算
- vue-cli生成vue+webpack的项目模板怎么设置为vue1.0
- greenplum单表恢复测试
- 坦克小战的游戏规则说明(2)
- Python checkio Min and Max解决方案
- 最近在做微信上传素材,使用tp5做框架,遇到了41005的问题,这里是解决的方法
- 织梦添加自定义字段“附件” 如何修改超链接附件URL地址
- python 搭建简单的http server,可直接post文件
- 529. Minesweeper(BFS)
- ulua_framwork中lua class 中使用Events事件注册的异步线程问题
- linux下编译安装ffmpeg