[pgpool-general: 7422] Re: Failover triggered when DB connections killed.

[Tatsuo Ishii]

Please note that this is written in the documentation:

https://www.pgpool.net/docs/41/en/html/restrictions.html

>> SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
>> WHERE pg_stat_activity.datname = 'DBNAME' AND pid <> pg_backend_pid();
> 
> This form of pg_terminate_backend() is not supported in Pgpool-II.
> You need to call pg_terminate_backend() with integer constant
> argument. i.e.
> 
> SELECT pg_ternamite_backend(1234);
> 
> Other forms including yours will trigger failover.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
> From: Sushil Shirodkar <sushilps at hotmail.com>
> Subject: Re: Failover triggered when DB connections killed.
> Date: Mon, 22 Feb 2021 22:10:49 +0000
> Message-ID: 
>  <MN2PR12MB420832CF2ECC57001701A223D4819 at MN2PR12MB4208.namprd12.prod.outlook.com>
> 
>> Hello friend,
>> 
>> Came across your suggestion and wanted to ask you some questions, can you
>> please suggest your recommendations.  I have already posted my issue earlier if
>> you see at the bottom of this email on the mailing-list but didn't get any response
>>  from other friends.
>> 
>> Basically, we are using Pgpool for failover and load balancer,  1 +2 servers (pri/slave).
>> Our all the traffic comes to Server 1 (pgpool) and does the load balancing etc. but
>> when we run pg_terminate either on Server 1  or Server 2(primary) it triggers the
>> failover.
>> 
>> Basically, Server 1 is having 5432 tried with 9898 didn't work.  Unless we connect to this
>> server 1 our load balancing or failover will not work.  Can  you pl. clarify, what you meant to
>> say and how that can be achieved.
>> 
>> Your help on the above will be highly appreciated.
>> 
>> 
>> Thanks in advance,
>> Sushil Shirodkar....
>> 
>> Re: Pgpool is crashing when terminating user session
>> From:   Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
>> To:     garajamohan(at)gmail(dot)com
>> Cc:     pgsql-general(at)lists(dot)postgresql(dot)org
>> Subject:        Re: Pgpool is crashing when terminating user session
>> Date:   2020-05-16 11:55:15
>> Message-ID:     20200516.205515.2045846881954825697.t-ishii at sraoss.co.jp<https://www.postgresql.org/message-id/20200516.205515.2045846881954825697.t-ishii%40sraoss.co.jp>
>> Views:  Raw Message<https://www.postgresql.org/message-id/raw/20200516.205515.2045846881954825697.t-ishii%40sraoss.co.jp> | Whole Thread<https://www.postgresql.org/message-id/flat/20200516.205515.2045846881954825697.t-ishii%40sraoss.co.jp> | Download mbox<https://www.postgresql.org/message-id/mbox/20200516.205515.2045846881954825697.t-ishii%40sraoss.co.jp> | Resend email<https://www.postgresql.org/message-id/resend/20200516.205515.2045846881954825697.t-ishii%40sraoss.co.jp>
>> Thread:
>> 2020-05-16 11:20:05 from RAJAMOHAN <garajamohan(at)gmail(dot)com>
>> 
>>  2020-05-16 11:55:15 from Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
>> 
>> Lists:  pgsql-general<https://www.postgresql.org/list/pgsql-general/since/202005161155>
>> 
>>> Hello all,
>>>
>>> We are having master-slave setup with pgpool pointing, only to master
>>> server. Whenever i tried to terminate the long running session on db end
>>> using SELECT pg_terminate_backend(pid), pgpool is getting crashed.  Many
>>> blogs are saying this is the expected behaviour of pgpool, but my question
>>> is there anyway to terminate unwanted sessions on db without loosing other
>>> connections.
>>>
>>> Because restarting entire system every time for a single trouble causing
>>> session is a big hectic for us.  Please share you ideas on this.
>> 
>> Those blogs are incorrect. From Pgpool-II 3.6 Pgpool-II supports
>> pg_terminate_backend(). Are you sure that you use "SELECT
>> pg_terminate_backend(pid)" from Pgpool-II session, not from a session
>> directly connecting to PostgreSQL?
>> 
>> Port 11000 is the port Pgpool-II is listening on.
>> 
>> [killing session]
>> 
>> $ psql -p 11000 test
>> psql (12.2)
>> Type "help" for help.
>> 
>> test=# select pg_terminate_backend(13877);
>>  pg_terminate_backend
>> ----------------------
>>  t
>> (1 row)
>> 
>> [killed session]
>> 
>> $ psql -p 11000 test
>> psql (12.2)
>> Type "help" for help.
>> 
>> test=# select pg_sleep(600);
>> FATAL:  terminating connection due to administrator command
>> ERROR:  unable to forward message to frontend
>> DETAIL:  FATAL error occured on backend
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Succeeded.
>> test=#
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>> 
>> 
>> ________________________________
>> From: Sushil Shirodkar
>> Sent: Wednesday, January 13, 2021 10:08 AM
>> To: pgpool-general at pgpool.net <pgpool-general at pgpool.net>
>> Subject: Failover triggered when DB connections killed.
>> 
>> Hello friends,
>> 
>> Was looking for some help on the following scenario and issue we are having.
>> 
>> Postgres 12.5 / Ubuntu 20.04 / Pgpool 4.1.4
>> 
>> With the above combination, we are running primary/secondary
>> and the third server as Pgpool for load balancing and automatic
>> failover.
>> 
>> When the below mentioned query run on Pgpool server, it triggered
>> failover to secondary and it became Primary.  Couldn't figure
>> out as why killing of database connections triggered failover.
>> 
>> SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
>> WHERE pg_stat_activity.datname = 'DBNAME' AND pid <> pg_backend_pid();
>> 
>> Thanks,
>> Sushil...
>> 
>> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general