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
