随笔- 320 文章- 0 评论- 5 阅读- 34799

KingbaseES V8R6集群运维案例之---synchronous参数配置测试

案例说明：
测试在KingbaseES V8R6集群配置synchronous后，主备节点之间的同异步模式。

数据库版本：

test=# select version();
                                                      version()

----------------------------------------------------------------------------------------------------------
------------
 KingbaseES V008R006C008B0014 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-
28), 64-bit
(1 row)

集群架构：

如下所示，集群架构为一主两备：

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string                                                                                                         
----+-------+---------+-----------+----------+----------+----------+----------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 33       |         | host=192.168.1.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 tcp_user_timeout=9000
 2  | node2 | standby |   running | node1    | default  | 100      | 33       | 0 bytes | host=192.168.1.202 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
 3  | node3 | standby |   running | node1    | default  | 100      | 33       | 0 bytes | host=192.168.1.203 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

一、配置测试

1、synchronous='quorum'

[kingbase@node201 bin]$ cat ../etc/repmgr.conf |grep sync
synchronous='quorum'

test=# show synchronous_standby_names ;
 synchronous_standby_names
---------------------------
 ANY 1(node2,node3)
(1 row)

# 流复制状态
test=# select * from sys_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         ba
ckend_start         | backend_xmin |   state   | sent_lsn | write_lsn  | flush_lsn  | replay_lsn | write_l
ag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+---------+------------------+---------------+-----------------+-------------+-----------
--------------------+--------------+-----------+----------+------------+------------+------------+--------
---+-----------+------------+---------------+------------+-------------------------------
 8061 |    16384 | esrep   | node2            | 192.168.1.202 |                 |       24472 | 2024-03-20
 17:52:31.389908+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             1 | quorum      | 2024-03-20 17:54:32.433777+08
 8066 |    16384 | esrep   | node3            | 192.168.1.203 |                 |       40885 | 2024-03-20
 17:52:33.809830+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             2 | quorum       | 2024-03-20 17:54:34.816136+08
(2 rows)

2、synchronous='sync'

[kingbase@node201 bin]$ cat ../etc/repmgr.conf |grep sync
synchronous='sync'

test=# show synchronous_standby_names ;
 synchronous_standby_names
---------------------------
 FIRST 1 (node2,node3)
(1 row)

# 流复制状态

test=# select * from sys_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         ba
ckend_start         | backend_xmin |   state   | sent_lsn | write_lsn  | flush_lsn  | replay_lsn | write_l
ag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+---------+------------------+---------------+-----------------+-------------+-----------
--------------------+--------------+-----------+----------+------------+------------+------------+--------
---+-----------+------------+---------------+------------+-------------------------------
 8061 |    16384 | esrep   | node2            | 192.168.1.202 |                 |       24472 | 2024-03-20
 17:52:31.389908+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             1 | sync       | 2024-03-20 17:54:32.433777+08
 8066 |    16384 | esrep   | node3            | 192.168.1.203 |                 |       40885 | 2024-03-20
 17:52:33.809830+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             2 | potential       | 2024-03-20 17:54:34.816136+08
(2 rows)

3、synchronous='all'

[kingbase@node201 bin]$ cat ../etc/repmgr.conf |grep sync
synchronous='all'

test=# show synchronous_standby_names ;
 synchronous_standby_names
---------------------------
 2 (node2,node3)
(1 row)


# 流复制状态

test=# select * from sys_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         ba
ckend_start         | backend_xmin |   state   | sent_lsn | write_lsn  | flush_lsn  | replay_lsn | write_l
ag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+---------+------------------+---------------+-----------------+-------------+-----------
--------------------+--------------+-----------+----------+------------+------------+------------+--------
---+-----------+------------+---------------+------------+-------------------------------
 8061 |    16384 | esrep   | node2            | 192.168.1.202 |                 |       24472 | 2024-03-20
 17:52:31.389908+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             1 | sync       | 2024-03-20 17:54:32.433777+08
 8066 |    16384 | esrep   | node3            | 192.168.1.203 |                 |       40885 | 2024-03-20
 17:52:33.809830+08 |              | streaming |          | 1/4E000740 | 1/4E000740 | 1/4E000740 |
   |           |            |             2 | sync       | 2024-03-20 17:54:34.816136+08
(2 rows)

二、修改配置案例
如下所示，修改node3节点为异步模式后，查看最终的流复制状态：

1、配置node3节点（async）

[kingbase@node203 bin]$ cat ../etc/repmgr.conf |grep sync
synchronous='async'

test=# alter system set synchronous_standby_names='';
ALTER SYSTEM
test=# select sys_reload_conf();
 sys_reload_conf()
-------------------
 t
(1 row)

test=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------

(1 row)

2、查看流复制

如下所示，node3仍然为sync状态，非async：

test=# select * from sys_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         ba
ckend_start         | backend_xmin |   state   | sent_lsn | write_lsn  | flush_lsn  | replay_lsn | write_l
ag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+---------+------------------+---------------+-----------------+-------------+-----------
--------------------+--------------+-----------+----------+------------+------------+------------+--------
---+-----------+------------+---------------+------------+-------------------------------
 8061 |    16384 | esrep   | node2            | 192.168.1.202 |                 |       24472 | 2024-03-20
 17:52:31.389908+08 |              | streaming |          | 1/4E000818 | 1/4E000818 | 1/4E000818 |
   |           |            |             1 | sync       | 2024-03-20 18:02:40.499475+08
 8066 |    16384 | esrep   | node3            | 192.168.1.203 |                 |       40885 | 2024-03-20
 17:52:33.809830+08 |              | streaming |          | 1/4E000818 | 1/4E000818 | 1/4E000818 |
   |           |            |             2 | sync       | 2024-03-20 18:02:40.675064+08
(2 rows)