Failover happening when kill idle sessions

From: postgres dba <dpostgres(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org, pgpool-general(at)pgpool(dot)net
Subject: Failover happening when kill idle sessions
Date: 2021-07-12 09:34:03
Message-ID: CABOYEjkkomBXBTN52fpTU6XLbGF2Wf3cZceUpFzicyc5ZEcgrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Amine Tengilimoglu 2021-07-12 09:59:04 Re: Failover happening when kill idle sessions
Previous Message Laurenz Albe 2021-07-12 05:29:14 Re: Lc_collate & lc_type? whitespace and nbsp unique index...