[pgpool-general: 8108] Re: Problems taking node offline

[Jon SCHEWE]

> On Tue, 26 Apr 2022 15:01:15 +0000
> Jon SCHEWE <jon.schewe at raytheon.com> wrote:
> 
> > >> I want to take a backend node offline and having some trouble with it.
> > >>
> > >> I check the status of my notes:
> > >> template1=> show pool_nodes;
> > >>  node_id |       hostname       | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
> > >> ---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
> > >>  0       | psql-01.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 646198     | false             | 0                 | streaming         | sync                   | 2022-04-25 14:19:57
> > >>  1       | psql-02.mgmt.bbn.com | 5432 | up     | 0.333333  | primary | 2115353    | true              | 0                 |                   |                        | 2022-04-25 14:16:24
> > >>  2       | psql-03.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 2913       | false             | 0                 | streaming         | potential              | 2022-04-25 14:24:25
> > >> (3 rows)
> > >>
> > >> I want to take psql-02 offline.
> > >>
> > >> pcp_detach_node -h psql.mgmt.bbn.com -p 9897 -U pgpool -g -n 1
> > >> Password:
> > >> pcp_detach_node -- Command Successful
> > >>
> > >>
> > >> I check the status again:
> > >> template1=> show pool_nodes;
> > >>  node_id |       hostname       | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
> > >> ---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
> > >>  0       | psql-01.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 718555     | true              | 0                 | streaming         | sync                   | 2022-04-25 14:19:57
> > >>  1       | psql-02.mgmt.bbn.com | 5432 | up     | 0.333333  | primary | 2373454    | false             | 0                 |                   |                        | 2022-04-25 14:16:24
> > >>  2       | psql-03.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 3310       | false             | 0                 | streaming         | potential              | 2022-04-25 14:24:25
> > >> (3 rows)
> > >>
> > >>
> > >> I still see psql-02 online. Why is that?
> > >
> > >Could you share pgpool.conf
> >
> > Yes, attached.
> >
> > > and full log after running pcp_detach_node?
> >
> > The only log messages are what I sent originally.
> >
> > >Which version of Pgpool-II are you using?
> >
> > 4.1.4
> 
> Thank you.
> 
> I think watchdog may not be working properly.
> If you run pcp_detach_node, failover_command and follow_master_command should be executed.
> But I could not see the related logs.
> 
> Could you check the watchdog status using "pcp_watchdog_info" command?


[jschewe-adm at psql-01 ~]$ pcp_watchdog_info -h psql.mgmt.bbn.com -p 9897 -U pgpool 
Password: 
3 YES psql-02.mgmt.bbn.com:9898 Linux psql-02 psql-02.mgmt.bbn.com

psql-02.mgmt.bbn.com:9898 Linux psql-02 psql-02.mgmt.bbn.com 9898 9000 4 MASTER
psql-01.mgmt.bbn.com:9898 Linux psql-01 psql-01.mgmt.bbn.com 9898 9000 7 STANDBY
Not_Set psql-03.mgmt.bbn.com 9898 9000 0 DEAD
[jschewe-adm at psql-01 ~]$ psql -h psql.mgmt.bbn.com -p 9898 -U postgres
Password for user postgres: 
psql (13.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# show pool_nodes;
 node_id |       hostname       | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | psql-01.mgmt.bbn.com | 5432 | up     | 0.333333  | primary | 30419799   | false             | 0                 |                   |                        | 2022-04-26 00:19:21
 1       | psql-02.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 20228026   | false             | 0                 | streaming         | potential              | 2022-04-26 10:57:15
 2       | psql-03.mgmt.bbn.com | 5432 | up     | 0.333333  | standby | 2974278    | true              | 0                 | streaming         | sync                   | 2022-04-26 11:04:41
(3 rows)

postgres=# 


> Does this issue occur if you disable watchdog "use_watchdog = off"?

I will give that a try when I have some downtime.