详解如何在数仓中搭建细粒度容灾应用

容灾配置文件backupRestore.ini

以下是backupRestore.ini文件示例，可根据具体业务场景修改配置参数值。

# Configuration file for SyncDataToStby tool # The backup life cycle life-cycle=10m # The table that records backups info backuprestoreinfo-file=backuprestoreinfo.csv # The cluster user name for cluster username=Ruby # The primary cluster env set primary-env= # The standby cluster env set standby-env= # Time interval between each full backup, uint: min full-backup-exec-time-interval=N/A # Time interval between each backup backup-exec-time-interval=2 # One of the backup hosts primary-host-ip=XXX.XXX.XXX.XX # The media type that restore backup files, DISK media-type=disk # The number of process that should be used. Range is (1~32) parrallel-process=4 # The compression level that should be used for backup. Range(0~9) compression-level=1 compression-type=2 # Media destination where the backup must be stored primary-media-destination=/DWS/data2/finedisaster/mediadata # Metadata destination where the metadata must be stored primary-metadata-destination=/DWS/data2/finedisaster/metadata # The master-port in which the backup must be executed backup-port=XXXX # Logging level for the log contents of backup:FATAL,ERROR,INFO,DEBUG primary-cluster-logging-level=INFO # Time interval between each restore, uint: min restore-interval=2 # One of the restore hosts restore-host-ip=XXX.XXX.XXX.XX # Media destination where the backup contents must be stored in the standby cluster restore-media-destination=/DWS/data2/finedisaster/mediadata # Metadata destination where the backup contents must be stored in the standby cluster restore-metadata-destination=/DWS/data2/finedisaster/metadata # The master-port in which the restore must be executed restore-port=XXXX # Logging level for the log contents of restore standby-cluster-logging-level=INFO # The maximum number of log files that should be created. Range is (5~1024). log-file-count=50 # The retry times of checking cluster balance for resuem backup or double clusters check-balance-retry-times=0 # cluster id primary-cluster-id=11111111-1111-1111-1111-111111111111 standby-cluster-id=22222222-2222-2222-2222-222222222222 # Processes tables for which fine-grained disaster recovery is no longer performed # Value should be 'none', 'log-desync-table', 'drop-desync-table' desync-table-operation=drop-desync-table # Number of CPU cores that can be used by each DR process. Range is (1~1024). cpu-cores=8 # The max number of rch files that can be reserved on master cluster before sent to standby cluster. 0 means no limit. local-reserve-file-count=160

主备集群倒换配置文件sw_backupRestore.ini相比于backupRestore.ini只需颠倒下列参数

• primary-host-ip / restore-host-ip
• backup-port / restore-port
• primary-media-destination / restore-media-destination
• primary-metadata-destination / restore-metadata-destination
• primary-cluster-id / standby-cluster-id

配置文件主要参数说明

3.3 主备集群准备

Ruby用户登录主集群主节点沙箱内。

设置集群GUC参数。

local-dn-num为本集群DN数，remote-dn-num为对方集群DN数，可替换为实际值。

python3 $GPHOME/script/DisasterFineGrained.py -t prepare --local-dn-num 6 --remote-dn-num 3 --config-file /DWS/data2/finedisaster/backupRestore.ini

3.4 容灾表数据准备

主集群连接数据库，准备容灾表数据。

create table schema1.table_1(id int, name text) with (orientation = column, colversion="2.0", enable_disaster_cstore="on", enable_delta=false) DISTRIBUTE BY hash(id);

alter table schema1.table_1 set (enable_disaster_cstore='on'); --查询表的分布方式 select pclocatortype from pg_catalog.pgxc_class where pcrelid = 'schema1.table_1'::regclass::oid limit 1; --若表的分布方式为H（HASH分布），获取一个hash表的分布列，后续以'id'列分布为例 select pg_catalog.getdistributekey('schema1.table_1'); --对表做重分布 alter table schema1.table_1 DISTRIBUTE BY HASH(id); --若表的分布方式为N(ROUNDROBIN分布)，对表做重分布 alter table schema1.table_1 DISTRIBUTE BY ROUNDROBIN; --若表的分布方式为R(REPLICATION分布)，对表做重分布 alter table schema1.table_1 DISTRIBUTE BY REPLICATION;

4. 细粒度容灾操作

4.1 定义发布

Ruby用户登录主集群主节点沙箱，以下操作均在沙箱内进行。

--发布所有表 CREATE PUBLICATION _pub_for_fine_dr FOR ALL TABLES; --发布schema CREATE PUBLICATION _pub_for_fine_dr FOR ALL TABLES IN SCHEMA schema1, schema2; --发布表和schema CREATE PUBLICATION _pub_for_fine_dr FOR ALL TABLES IN SCHEMA schema1, TABLE schema2.table_1; --增加一个发布表 ALTER PUBLICATION _pub_for_fine_dr ADD TABLE schema1.table_1; --增加发布SCHEMA ALTER PUBLICATION _pub_for_fine_dr ADD ALL TABLES IN SCHEMA schema2;

echo 'dbname._pub_for_fine_dr' > /DWS/data2/finedisaster/pub.list

若需要解除发布，通过DisasterFineGrained.py脚本下发cancel-publication命令

# 1、创建需要取消的容灾对象列表文件 # 解除发布表 echo 'db_name.schema_name.table_name' > /DWS/data2/finedisaster/config/disaster_object_list.txt # 解除发布SCHEMA echo 'db_name.schema_name' > /DWS/data2/finedisaster/config/disaster_object_list.txt # 2、下发cancel-publication命令 python3 $GPHOME/script/DisasterFineGrained.py -t cancel-publication --disaster-object-list-file /DWS/data2/finedisaster/config/disaster_object_list.txt --config-file /DWS/data2/finedisaster/backupRestore.ini # 3、取消全部发布 python3 $GPHOME/script/DisasterFineGrained.py -t cancel-publication --config-file /DWS/data2/finedisaster/backupRestore.ini --all-cancel

4.2 启动容灾

注意：

*/1 * * * * nohup ssh XXX.XXX.XXX.XXX "source /etc/profile;if [ -f ~/.profile ];then source ~/.profile;fi;source ~/.bashrc;nohup python3 /opt/dws/tools/script/SyncDataToStby.py -t backup --config-file /DWS/data2/finedisaster/backupRestore.ini --disaster-fine-grained --publication-list /DWS/data2/finedisaster/pub.list >>/dev/null 2>&1 &" >>/dev/null 2>&1 &

*/1 * * * * nohup ssh XXX.XXX.XXX.XXX "source /etc/profile;if [ -f ~/.profile ];then source ~/.profile;fi;source ~/.bashrc;nohup python3 /opt/dws/tools/script/SyncDataToStby.py -t restore --config-file /DWS/data2/finedisaster/backupRestore.ini --disaster-fine-grained >>/dev/null 2>&1 &" >>/dev/null 2>&1 &

ps ux | grep SyncDataToStby | grep -v grep

python3 $GPHOME/script/SyncDataToStby.py -t show-progress --config-file /DWS/data2/finedisaster/backupRestore.ini

{ "primary cluster": { "key": "20231109_212030", "priorKey": "20231109_211754", "actionType": "Backup", "progress": "100.00%", "backupRate": { "producerRate": "0MB/s", "compressRate": "0MB/s", "consumerRate": "0MB/s" }, "currentStep": "FINISH", "unrestoreKeys": "N/A", "failedStep": "INIT", "errorMsg": "", "errorCode": "", "actionStartTime": "2023-11-09 21:20:28", "actionEndTime": "2023-11-09 21:20:49", "updateTime": "2023-11-09 21:20:50" }, "standby cluster": { "key": "20231109_175002", "priorKey": "N/A", "actionType": "Restore", "progress": "100.00%", "backupRate": { "producerRate": "0MB/s", "compressRate": "0MB/s", "consumerRate": "0MB/s" }, "currentStep": "FINISH", "unrestoreKeys": "20231109_211754,20231109_212030", "failedStep": "INIT", "errorMsg": "", "errorCode": "", "actionStartTime": "2023-11-09 17:53:07", "actionEndTime": "2023-11-09 17:53:15", "updateTime": "2023-11-09 17:53:15" }, "apply": { "backupState": "waiting", "restoreState": "waiting", "backupSuccessTime": "2023-11-09 21:21:24", "restoreSuccessTime": "2023-11-09 17:53:55" }, "latestBarrierTime": "", "recovery point objective": "3:32:17", "failover recovery point time": "" }

# 主备集群取消沙箱外的定时容灾任务，在备份/恢复任务前添加注释符“#”，取消定时任务 crontab -e # 主集群Ruby用户登录主节点沙箱，停止备份 python3 $GPHOME/script/SyncDataToStby.py -t stop-backup --config-file /DWS/data2/finedisaster/backupRestore.ini --disaster-fine-grained # 备集群Ruby用户登录主节点沙箱，停止恢复 python3 $GPHOME/script/SyncDataToStby.py -t stop-restore --config-file /DWS/data2/finedisaster/backupRestore.ini --disaster-fine-grained

python3 $GPHOME/script/SyncDataToStby.py -t set-independent --config-file /DWS/data2/finedisaster/backupRestore.ini --disaster-fine-grained

Delete csv file. Delete roachbackup file from XXX.XXX.XXX.XX Delete roachbackup file from XXX.XXX.XXX.XX Clear cluster disaster state.