[pgpool-general: 8172] Re: Problems taking node offline
Bo Peng
pengbo at sraoss.co.jp
Tue May 24 21:54:17 JST 2022
Hello,
> I was able to do some experimentation this today.
>
> To take a frontend offline I believe I just need to stop the pgpool process on that system and then let pgpool figure out the new primary and grab the virtual IP address. Correct?
Yes.
> To take a backend offline I believe I use pcp_detach_node. Correct?
Yes.
If failover_command and follow_primary_command are specified, they are executed after you run pcp_detach_node.
If you run pcp_detach_node to detach the primary node,
failover_command promotes a standby to primary and
follow_primary_command enables the other alive nodes to follow the new primary.
It seems that the failover_command and follow_primary_command didn't executed in your logs.
Can you successfully detach a standby PostgreSQL node?
If you shutdown the primary PostgreSQL node, will failover be performed correctly?
I found that the setting parameters of 4.1 and 4.2 are mixed in pgpool.conf.
I am not sure if it affects the behavior of watchdog.
> As far as testing with "use_watchdog = off", I just tried that. I changed the parameter on all 3 of my pgpool hosts. I then restarted the pgpool process on all 3 hosts. I noticed that no host picked up the virtual IP, I'm assuming that is because the watchdog is off, correct?
> I manually assigned the virtual IP to one of the hosts.
> Before I execute any commands:
> 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 | 282959 | true | 0 | | | 2022-05-13 12:46:54
> 1 | psql-02.mgmt.bbn.com | 5432 | up | 0.333333 | standby | 230916 | false | 0 | streaming | potential | 2022-05-13 12:46:54
> 2 | psql-03.mgmt.bbn.com | 5432 | up | 0.333333 | standby | 370021 | false | 0 | streaming | sync | 2022-05-13 12:46:54
> (3 rows)
>
> I then executed:
> $ pcp_detach_node -h psql.mgmt.bbn.com -p 9897 -U pgpool -g -n 1
> Password:
> pcp_detach_node -- Command Successful
>
> This took a long time (60 seconds) to finish.
>
> The node does not appear to be offline:
> 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 | 311652 | true | 0 | | | 2022-05-13 12:46:54
> 1 | psql-02.mgmt.bbn.com | 5432 | up | 0.333333 | standby | 248369 | false | 0 | streaming | potential | 2022-05-13 12:46:54
> 2 | psql-03.mgmt.bbn.com | 5432 | up | 0.333333 | standby | 410891 | false | 0 | streaming | sync | 2022-05-13 12:46:54
> (3 rows)
>
> The system log for pgpool during this time is attached.
>
> What am I doing wrong here?
>
>
>
>
>
>
>
>
> Jon Schewe
> Principal Software Systems Technologist
> C: +1 612.263.2718
> O: +1 952.545.5720
> jon.schewe at raytheon.com
>
> Raytheon BBN
> Raytheon Intelligence & Space
> 5775 Wayzata Blvd. Suite 630
> St. Louis Park, MN 55416
>
>
>
>
> From: pgpool-general <pgpool-general-bounces at pgpool.net> on behalf of Jon SCHEWE <jon.schewe at raytheon.com>
> Sent: Thursday, May 12, 2022 16:14
> To: Bo Peng <pengbo at sraoss.co.jp>
> Cc: pgpool-general at pgpool.net <pgpool-general at pgpool.net>
> Subject: [External] [pgpool-general: 8155] Re: Problems taking node offline
>
> I have not been able to test with the watchdog off, however I am wondering about the proper commands to switch backends and frontends.
>
> I see pcp_detatch_node removes a pgpool frontend.
> What command can I use to tell pgpool to switch to a different primary backend? Do I just stop the postgresql process?
>
> Jon Schewe
> Principal Software Systems Technologist
>
> C: +1 612.263.2718
> O: +1 952.545.5720
> jon.schewe at raytheon.com
>
> Raytheon BBN
> Raytheon Intelligence & Space
> 5775 Wayzata Blvd. Suite 630
> St. Louis Park, MN 55416
>
> RTX.com | LinkedIn | Twitter | Instagram
>
>
> From: pgpool-general <pgpool-general-bounces at pgpool.net> on behalf of Jon SCHEWE <jon.schewe at raytheon.com>
> Sent: Wednesday, April 27, 2022 13:07
> To: Bo Peng <pengbo at sraoss.co.jp>
> Cc: pgpool-general at pgpool.net <pgpool-general at pgpool.net>
> Subject: [External] [pgpool-general: 8108] Re: Problems taking node offline
>
> > 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.
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/
More information about the pgpool-general
mailing list