Re: Failover happening when kill idle sessions

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