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

# 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

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

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;

--发布所有表
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

# 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