Thread: Failover happening when kill idle sessions

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

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@gmail.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

Thank you for your support Amine..!!

On Mon, Jul 12, 2021, 3:29 PM Amine Tengilimoglu <aminetengilimoglu@gmail.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@gmail.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