KingbaseES R3 集群pcp_attach_node 更新show pool_nodes中节点状态

系统环境：

操作系统：
[kingbase@node2 bin]$ cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core)

数据库：
prod=# select version();
                                                         version                                                       
----------------------------------------------------------------------------------------
 Kingbase V008R003C002B0270 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 
64-bit
(1 row)

案例架构：

案例说明：

pcp_attach_node工具是kingbasecluster(pgpool)自带的集群管理工具，pcp_attach_node工具可以将集群中的节点重新注册到集群中，对于主备流复制正常，但通过show pool_nodes显示的状态不正常的node，可以尝试通过此工具进行处理。

一、案例现象

备库数据库服务手工启动后，从sys_stat_replication查看主备流复制状态已经正常，但是从show pool_nodes中仍显示node status为“down”，通过pcp_attach_node工具重新将备节点注册到cluster，status状态正常“up”。

1)主备流复制状态

test=# select * from sys_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_s
tart         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync
_priority | sync_state 
-------+----------+---------+------------------+---------------+-----------------+------
 13458 |       10 | SYSTEM  | node1            | 192.168.7.248 |                 |       24328 | 2021-03-01 13:17:
21.697235+08 |              | streaming | 0/1A004380    | 0/1A004380     | 0/1A004380     | 0/1A004380      |     
        0 | async
(1 row)

2)show pool_nodes查看节点状态

[kingbase@node2 bin]$  ./ksql -U SYSTEM -W 123456Abc test -p 9999
ksql (V008R003C002B0270)
Type "help" for help.

test=# show pool_nodes;
 node_id |   hostname    | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_del
ay 
---------+---------------+-------+--------+-----------+---------+------------+------------
 0       | 192.168.7.248 | 54322 | down   | 0.500000  | standby | 0          | false             | 0
 1       | 192.168.7.249 | 54322 | up     | 0.500000  | primary | 0          | true              | 0
(2 rows)

=如上面所示，集群中主备流复制状态正常，standby节点数据库服务状态应该是“up" ; 但是在通过9999端口访问查询时，备库状态为”down“的状态，这和实际的node状态不符，应该是kingbasecluster的cache中记录node中后台数据库状态没有被更新，可以通过pcp_attach_node将node重新注册到集群，获取node最新状态。=

二、通过pcp_attach_node工具注册备库

1)查看pcp工具的连接用户

[kingbase@node1 etc]$ cat pcp.conf
kingbase:e10adc3949ba59abbe56e057f20f883e

2)使用pcp_attach_node注册

[kingbase@node2 bin]$ ./pcp_attach_node -h 192.168.7.245 -U kingbase 0
Password: 
pcp_attach_node -- Command Successful

三、查看show pool_nodes的节点状态

test=# show pool_nodes;
 node_id |   hostname    | port  | status  | lb_weight |  role   | select_cnt | load_balance_node | replication_de
lay 
---------+---------------+-------+---------+-----------+---------+------------+----------
 0       | 192.168.7.248 | 54322 | waiting | 0.500000  | standby | 0          | false             | 0
 1       | 192.168.7.249 | 54322 | up      | 0.500000  | primary | 0          | true              | 0
(2 rows)

----- stauts由“down” 更新为“waiting”

四、测试流复制同步

主库事务操作：

test=# \c prod;You are now connected to database "prod" as user "SYSTEM".prod=# \d                    List of relations Schema |             Name              | Type  | Owner  --------+-------------------------------+-------+-------- PUBLIC | pathman_cache_stats           | view  | SYSTEM PUBLIC | pathman_concurrent_part_tasks | view  | SYSTEM PUBLIC | pathman_config                | table | SYSTEM PUBLIC | pathman_config_params         | table | SYSTEM PUBLIC | pathman_partition_list        | view  | SYSTEM(5 rows)prod=# create table t1 (id int ,name varchar(20));CREATE TABLEprod=# insert into t1 values (generate_series(1,1000),'tom'||generate_series(1,1000));INSERT 0 1000

备库查询：

prod=# select count(*) from t1; count -------  1000(1 row)

查看show pool_nodes节点状态：

prod=# show pool_nodes; node_id |   hostname    | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay ---------+---------------+-------+--------+-----------+---------+------------+----------- 0       | 192.168.7.248 | 54322 | up     | 0.500000  | standby | 0          | false             | 0 1       | 192.168.7.249 | 54322 | up     | 0.500000  | primary | 3          | true              | 0(2 rows)

---- satus为“up”！！！

五 、使用pcp工具查看集群节点信息

1)查看node信息

[kingbase@node2 bin]$ ./pcp_node_info -h 192.168.7.245 -U kingbase -v 0Password: Hostname   : 192.168.7.248Port       : 54322Status     : 2Weight     : 0.500000Status Name: up[kingbase@node2 bin]$ ./pcp_node_info -h 192.168.7.245 -U kingbase -v 1Password: Hostname   : 192.168.7.249Port       : 54322Status     : 2Weight     : 0.500000Status Name: up

2)查看watchdog信息

[kingbase@node2 bin]$ ./pcp_watchdog_info -h 192.168.7.245 -U kingbase -vPassword: Watchdog Cluster Information Total Nodes          : 2Remote Nodes         : 1Quorum state         : QUORUM EXISTAlive Remote Nodes   : 1VIP up on local node : NOMaster Node Name     : 192.168.7.248:9999 Linux node1Master Host Name     : 192.168.7.248Watchdog Node Information Node Name      : 192.168.7.249:9999 Linux node2Host Name      : 192.168.7.249Delegate IP    : 192.168.7.244Kingbasecluster port    : 9999Watchdog port  : 9000Node priority  : 1Status         : 7Status Name    : STANDBYNode Name      : 192.168.7.248:9999 Linux node1Host Name      : 192.168.7.248Delegate IP    : 192.168.7.244Kingbasecluster port    : 9999Watchdog port  : 9000Node priority  : 3Status         : 4Status Name    : MASTER

六、总结

有效使用pcp工具，可以快速解决集群中遇到的故障问题。

七、现场案例

案例说明:

备库出现故障，通过sys_basebackup修复后，查看主备流复制状态正常，通过show pool_nodes查看节点出现异常。

故障现象：

故障处理：

采用pcp_attach_node工具将节点重新注册，集群状态恢复正常。