Re: Failover happening when kill idle sessions

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
>