KingbaseES V8R6集群部署案例之---Windows环境配置主备流复制(同一主机）
案例说明：
目前KingbaseES V8R6的Windows版本不支持数据库sys_rman的物理备份，可以考虑通过建立主备流复制实现数据库的异机物理备份。本案例详细介绍了，在Windows环境下建立流复制的过程，备库的创建可以在同一主机完成，也可以异机创建流复制。
适用版本：
Windows KingbaseES V8R6
系统环境：
主备节点数据存储结构：
# 主库：
Data目录: D:\Program Files\Kingbase\ES\V8\data
archive目录：d:\\kes_arch
port：54321
# 备库：
Data目录: E:\kes\data
archive目录：e:\kes\arch
port：54322
一、配置主库归档及节点通讯
1、主库配置
# kingbase.conf配置
port=54321
archive_mode = on
archive_command = 'copy "%p" "d:\\kes_arch\\%f"'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 100
# kingbase.auto.conf 配置
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=system password=123456 connect_timeout=10 host=127.0.0.1 port=54322
keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
application_name=node01'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_1'
wal_retrieve_retry_interval = '5000'
二、通过sys_basebackup克隆备库
Tips：
备节点的数据，可以通过sys_basebackup在线clone方式创建，或主库数据库服务正常关闭后，拷贝数据到备库。
D:\Program Files\Kingbase\ES\V8\Server\bin>sys_basebackup -h 127.0.0.1 -U system -W
-C -S "repmgr_slot_2" -R -F p -X stream -v -P -D "e:\kes\data"
Password:
sys_basebackup: initiating base backup, waiting for checkpoint to complete
已复制 1 个文件。
sys_basebackup: checkpoint completed
sys_basebackup: write-ahead log start point: 0/7000028 on timeline 1
sys_basebackup: starting background WAL receiver
sys_basebackup: created replication slot "repmgr_slot_2"
60041/60041 kB (100%), 1/1 tablespace
sys_basebackup: write-ahead log end point: 0/70000F8
sys_basebackup: waiting for background process to finish streaming ...
已复制 1 个文件。
已复制 1 个文件。
sys_basebackup: syncing data to disk ...
sys_basebackup: base backup completed
如下所示：备库数据文件
配置备库流复制：
# kingbase.conf配置
port=54322
archive_mode = on
archive_command = 'copy "%p" "e:\\kes\\arch\\%f"'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 100
# kingbase.auto.conf 配置
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=system password=123456 connect_timeout=10 host=127.0.0.1
port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
application_name=node02'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_2'
wal_retrieve_retry_interval = '5000'
三、启动主备库数据库服务
1、启动数据库服务
# 主库
D:\Program Files\Kingbase\ES\V8\Server\bin>sys_ctl restart -D "D:\Program Files\Kingbase\ES\V8\data"
......
server started
# 备库
D:\Program Files\Kingbase\ES\V8\Server\bin>sys_ctl restart -D "e:\kes\data"
.....
server started
2、查看复制槽信息
D:\Program Files\Kingbase\ES\V8\Server\bin>ksql -U system -W test
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# select * from sys_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
repmgr_slot_2 | | physical | | | f | t | 18324 | | | 0/5019BE0 |
repmgr_slot_1 | | physical | | | f | f | | | | |
(2 行记录)
四、查看主备流复制状态
D:\Program Files\Kingbase\ES\V8\Server\bin>ksql -U system -W test
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
18324 | 10 | system | node02 | 127.0.0.1 | | 54411 | 2022-08-16 14:57:31.810737+08 | | streaming | 0/50001A8 | 0/50001A8 | 0/50001A8 | 0/50001A8 | | | | 0 | async | 2022-08-16 14:57:42.327673+08
(1 行记录)
五、测试数据同步
1、主库DML操作
D:\Program Files\Kingbase\ES\V8\Server\bin>ksql -U system -W prod
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# create database prod;
CREATE DATABASE
test=# \c prod
口令：
您现在已经连接到数据库 "prod",用户 "system".
prod=# create table t1 (id int);
CREATE TABLE
prod=# insert into t1 values (10),(20),(30);
INSERT 0 3
prod=# select * from t1;
id
----
10
20
30
(3 行记录)
2、备库查询
D:\Program Files\Kingbase\ES\V8\Server\bin>ksql -U system -W -p 54322 test
口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# show port;
port
-------
54322
(1 行记录)
test=# \c prod
口令：
您现在已经连接到数据库 "prod",用户 "system".
prod=# select * from t1;
id
----
10
20
30
(3 行记录)
=如上所示，备库已经同步主库的数据，主备流复制创建成功。=
六、总结
对于在KingbaseES V8R6的Windows环境，创建主备流复制与Linux环境基本相同，对于Windows环境不支持sys_rman的物理备份，可以考虑通过物理的流复制建立异机的备份。
KINGBASE研究院
