系统环境:
操作系统:
[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工具是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工具注册备库
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 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工具,可以快速解决集群中遇到的故障问题。