容灾配置文件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.