Re: Failover happening when kill idle sessions

You're welcome

12 Tem 2021 Pzt 14:47 tarihinde postgres dba <dpostgres(at)gmail(dot)com> şunu
yazdı:

> Thank you for your support Amine..!!
>
> On Mon, Jul 12, 2021, 3:29 PM Amine Tengilimoglu <
> aminetengilimoglu(at)gmail(dot)com> wrote:
>
>> This problem is actually a bad feature of pgpool. You probably killed the
>> sessions directly on the master server. You should connect to the database
>> via pgpool and terminate the session.
>>
>> As for the reason when pgpool sees that the it's connections are
>> suddenly killed, it concludes that the master server is failing and
>> performs a failover.
>>
>> postgres dba <dpostgres(at)gmail(dot)com>, 12 Tem 2021 Pzt, 12:34 tarihinde
>> şunu yazdı:
>>
>>> Hi Folks,
>>>
>>> In our environment have setup A(Master)-->B(Slave) along with pgpool
>>> auto-failover.
>>>
>>> No of connections hits are coming from application more than
>>> 3000..however we are finding 2000 idle connection and 500 is active.
>>>
>>> With this we are frequntly getting cpu utilization alerts..to prevent
>>> cpu utilization we are suppose to kill idle sessions..however when we fired
>>> :"select pg_terminate_backend(pid) from pg_stat_activity where pid <>
>>> pg_backend_pid() AND state in ('idle') and usename NOT IN ('replication')
>>> and state_change >= current_timestamp - interval '10 minutes' "
>>>
>>> automatic failover is happening and getting below message in pgpool
>>> logs..
>>>
>>>
>>> 2021-07-08 12:16:25: pid 205234: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 114585: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 114585: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 205234: LOG: received degenerate backend
>>> request for node_id: 0 from pid [205234]
>>> 2021-07-08 12:16:25: pid 243969: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 243969: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 114585: LOG: received degenerate backend
>>> request for node_id: 0 from pid [114585]
>>> 2021-07-08 12:16:25: pid 243969: LOG: received degenerate backend
>>> request for node_id: 0 from pid [243969]
>>> 2021-07-08 12:16:25: pid 133861: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 133861: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 133861: LOG: received degenerate backend
>>> request for node_id: 0 from pid [133861]
>>> 2021-07-08 12:16:25: pid 183402: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 183402: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 244375: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 244375: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 115724: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 115724: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>> 2021-07-08 12:16:25: pid 244375: LOG: received degenerate backend
>>> request for node_id: 0 from pid [244375]
>>> 2021-07-08 12:16:25: pid 183402: LOG: received degenerate backend
>>> request for node_id: 0 from pid [183402]
>>> 2021-07-08 12:16:25: pid 243911: LOG: reading and processing packets
>>> 2021-07-08 12:16:25: pid 243911: DETAIL: postmaster on DB node 0 was
>>> shutdown by administrative command
>>>
>>> Why pgpool is performing automatic failover when we run pg_terminate in
>>> backend to kill idle connession..?
>>>
>>> Is anyone faced this issue..
>>>
>>> -Best Regards,
>>> Dinesh Postgres DBA
>>>
>>