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

[Tatsuo Ishii]

> 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...
> 
>