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 更新show pool_nodes中节点状态

案例说明:

pcp_attach_node工具是pgpool自带的集群管理工具,pcp_attach_node工具可以将集群中的节点重新注册到集群中,对于通过show_pools显示的状态不正常的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)

pcp_attach_node 更新show pool_nodes中节点状态

二、通过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)

pcp_attach_node 更新show pool_nodes中节点状态

----- 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 TABLE
prod=# 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_del
ay 
---------+---------------+-------+--------+-----------+---------+------------+-----------
 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 0
Password: 
Hostname   : 192.168.7.248
Port       : 54322
Status     : 2
Weight     : 0.500000
Status Name: up
[kingbase@node2 bin]$ ./pcp_node_info -h 192.168.7.245 -U kingbase -v 1
Password: 
Hostname   : 192.168.7.249
Port       : 54322
Status     : 2
Weight     : 0.500000
Status Name: up

2)查看watchdog信息


[kingbase@node2 bin]$ ./pcp_watchdog_info -h 192.168.7.245 -U kingbase -v
Password: 
Watchdog Cluster Information 
Total Nodes          : 2
Remote Nodes         : 1
Quorum state         : QUORUM EXIST
Alive Remote Nodes   : 1
VIP up on local node : NO
Master Node Name     : 192.168.7.248:9999 Linux node1
Master Host Name     : 192.168.7.248

Watchdog Node Information 
Node Name      : 192.168.7.249:9999 Linux node2
Host Name      : 192.168.7.249
Delegate IP    : 192.168.7.244
Kingbasecluster port    : 9999
Watchdog port  : 9000
Node priority  : 1
Status         : 7
Status Name    : STANDBY

Node Name      : 192.168.7.248:9999 Linux node1
Host Name      : 192.168.7.248
Delegate IP    : 192.168.7.244
Kingbasecluster port    : 9999
Watchdog port  : 9000
Node priority  : 3
Status         : 4
Status Name    : MASTER

六、总结

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

pcp_attach_node 更新show pool_nodes中节点状态

上一篇:Push打开量不高?快试试这几个方法!


下一篇:git操作指令