KingbaseES V8R6集群运维系列之 -- 创建流复制只读副本库
案例说明：
在kingbaseES V8R6集群已经部署了一主一备的架构下，现因业务需求，客户需要再构建一个只读的副本（流复制备库），此备库不需要纳入repmgr的管理。
适用版本：
KingbaseES V8R6
集群架构：
一、查看原集群节点状态和流复制状态
1）查看集群节点状态
[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+--------
1 | node248 | standby | running | node249 | default | 100 | 6 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node249 | primary | * running | | default | 100 | 6 | host=192.168.7.249 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2）查看流复制状态
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_s
tart | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_la
g | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+-
32701 | 16384 | esrep | node248 | 192.168.7.248 | | 40835 | 2021-03-01 14:14:
46.302306+08 | | streaming | 2/21000060 | 2/21000060 | 2/21000060 | 2/21000060 | |
| | 1 | quorum | 2021-03-01 17:35:03.927948+08
(1 row)
二、在主库生成检查点
[kingbase@node2 bin]$ ./ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# checkpoint;
CHECKPOINT
三、克隆新的流复制备库（sys_basebackup）
[kingbase@node2 kingbase]$ pwd
/home/kingbase/cluster/R6HA/KHA/kingbase
[kingbase@node2 kingbase]$ mkdir data1
[kingbase@node2 bin]$ ./sys_basebackup -h 127.0.0.1 -D /home/kingbase/cluster/R6HA/KHA/kingbase/data1 -F p -X stream -v -P -U system -p 54321
sys_basebackup: initiating base backup, waiting for checkpoint to complete
.......
sys_basebackup: write-ahead log end point: 2/20000138
sys_basebackup: waiting for background process to finish streaming ...
sys_basebackup: syncing data to disk ...
sys_basebackup: base backup completed
四、配置新备库data目录权限
[kingbase@node2 kingbase]$ chmod 700 data1
五、在新备库data下生成standby.signal文件
=== 注意：此文件标识此节点是备库，一定在启动备库数据库服务前创建；否则数据库服务启动，默认为主库，将无法再加入流复制集群。此文件可以用touch创建空文件，也可以从原备库拷贝。===
[kingbase@node1 data]$ scp standby.signal node2:/home/kingbase/cluster/R6HA/KHA/kingbase/data1
standby.signal
[kingbase@node2 data1]$ ls -lh standby.signal
-rw------- 1 kingbase kingbase 20 Mar 1 17:45 standby.signal
[kingbase@node2 data1]$ cat standby.signal
# created by repmgr
六、编辑kingbase.auto.conf连接主库
[kingbase@node2 data1]$ cat kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=esrep connect_timeout=10 host=192.168.7.249 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_3'
wal_retrieve_retry_interval = '5000'
synchronous_standby_names = 'ANY 1(*)'
=== 注意：此处填写主库的ip信息，对应的复制槽要在主库创建。===
七、创建复制槽和启动备库数据库服务
1）查看已有的复制槽信息
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_1 | | physical | | | f | t | 32701 | | |
2/21000060 |
(1 row)
2）创建复制槽
test=# select sys_create_physical_replication_slot('repmgr_slot_3');
sys_create_physical_replication_slot
--------------------------------------
(repmgr_slot_3,)
(1 row)
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_1 | | physical | | | f | t | 32701 | | |
2/21000148 |
repmgr_slot_3 | | physical | | | f | f | | | |
|
(2 rows)
3）修改服务端口号（因为此备库和主库在同一个主机，所以需要修改服务端口号，同时启动两个实例）
[kingbase@node2 data1]$ cat kingbase.conf |grep port
port = 54322 # (change requires restart)
4）启动数据库服务
[kingbase@node2 bin]$ ./sys_ctl start -D ../data1
......
server started
[kingbase@node2 bin]$ ps -ef |grep kingbase
.......
kingbase 16562 1 0 17:52 ? 00:00:00 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/kingbase -D ../data1
kingbase 16563 16562 0 17:52 ? 00:00:00 kingbase: logger
kingbase 16564 16562 1 17:52 ? 00:00:00 kingbase: startup recovering 000000060000000200000023
kingbase 16567 16562 0 17:52 ? 00:00:00 kingbase: checkpointer
kingbase 16568 16562 0 17:52 ? 00:00:00 kingbase: background writer
kingbase 16569 16562 0 17:52 ? 00:00:00 kingbase: stats collector
kingbase 16570 16562 0 17:52 ? 00:00:00 kingbase: walreceiver streaming 2/23000060
kingbase 16571 32623 0 17:52 ? 00:00:00 kingbase: walsender esrep 192.168.7.249(59875) streaming 2/23000060
八、查看流复制状态
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_s
tart | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_la
g | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+
32701 | 16384 | esrep | node248 | 192.168.7.248 | | 40835 | 2021-03-01 14:14:
46.302306+08 | | streaming | 2/23000060 | 2/23000060 | 2/23000060 | 2/23000060 | |
| | 1 | quorum | 2021-03-01 17:54:12.970131+08
16571 | 16384 | esrep | walreceiver | 192.168.7.249 | | 59875 | 2021-03-01 17:52:
24.644390+08 | | streaming | 2/23000060 | 2/23000060 | 2/23000060 | 2/23000060 | |
| | 1 | quorum | 2021-03-01 17:53:04.788830+08
(2 rows)
=== 从以上可以获知，新的备库已经加入到流复制中。===
九、测试数据同步
1）主库
prod=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | integer | | |
name | character varying(10 char) | | |
prod=# select * from t2;
id | name
----+------
10 | tom
(1 row)
prod=# insert into t2 values(20,'jerry'),(30,'rose');
INSERT 0 2
prod=# select * from t2;
id | name
----+-------
10 | tom
20 | jerry
30 | rose
(3 rows)
2）备库
[kingbase@node1 bin]$ ./ksql -U system prod -c 'select * from t2'
id | name
----+-------
10 | tom
20 | jerry
30 | rose
(3 rows)
3）只读副本
[kingbase@node2 bin]$ ./ksql -U system prod -c 'select * from t2' -p 54322
id | name
----+-------
10 | tom
20 | jerry
30 | rose
=== 从以上获知，此只读的流复制备库创建成功！===
十、总结
部署步骤总结：
1）查看源集群节点状态及流复制状态。
2）在集群主库上执行checkpoint。
3）在新增节点下执行sys_basebackup创建流复制备库。
4）设置新备库data目录700权限。
5）在新备库data目录下创建standby.signal文件（可以touch创建或从集群备库拷贝）。
6）修改新备库kingbase.auto.conf文件，连接集群主库。
7）在集群主库创建新备库复制槽，启动新备库数据库服务（sys_ctl启动）。
8）查看流复制状态。
9）在集群主库做DML操作，验证数据同步。
KINGBASE研究院
