[pgpool-general: 9151] Pgpool shutdown on terminating a pid in database.

[Aviral Singh]

Hello,

I have a docker swarm architecture of 2 PostgreSQL nodes in Repmgr and a
connection pooling method, pgool.

PostgreSQL Version: 12.4
PgPool Version: 4.2.5
Cluster-Management of Database: Repmgr (1 Primary + 1 Standby)

*Issue:* There was constant increase in CPU Usage by postgresql container.
On checking docker stats of postgresql primary node, it is consuming 328%.
On normal days, it is below 50%.
On investigation, I found one query whose running time was around 20 hours
and its state was idle in transaction.

Resolution: To control the CPU Usage, I terminated the pid in database
using below query:
>  SELECT pg_terminate_backend(4037086);

But immediately after the pid was killed. The pgpool container  was shut
down.

*Pgpool-II Logs*:

> 2024-06-21 12:25:05: pid 809743: LOG:  reading and processing packets,
> 2024-06-21 12:25:05: pid 809743: DETAIL:  postmaster on DB node 0 was
> shutdown by administrative command,
> 2024-06-21 12:25:05: pid 809743: LOG:  received degenerate backend request
> for node_id: 0 from pid [809743],
> 2024-06-21 12:25:05: pid 809743: LOG:
>  signal_user1_to_parent_with_reason(0),
> 2024-06-21 12:25:05: pid 1: LOG:  Pgpool-II parent process received
> SIGUSR1,
> 2024-06-21 12:25:05: pid 1: LOG:  Pgpool-II parent process has received
> failover request,
> 2024-06-21 12:25:05: pid 1: LOG:  starting degeneration. shutdown host
> pg-0(5432),
> 2024-06-21 12:25:05: pid 1: LOG:  Restart all children,
> 2024-06-21 12:25:06: pid 1: LOG:  execute command: echo ">>> Failover -
> that will initialize new primary node search!",
> >>> Failover - that will initialize new primary node search!
>

*PostgreSQL Logs*:

> 2024-06-21 12:25:05.992 GMT [4037086] FATAL:  terminating connection due
> to administrator command
> 2024-06-21 12:25:06.218 GMT [15488] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.287 GMT [15653] LOG:  could not receive data from
> client: Connection reset by peer
> 2024-06-21 12:25:06.288 GMT [15653] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.351 GMT [13183] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.355 GMT [14050] LOG:  could not receive data from
> client: Connection reset by peer
> 2024-06-21 12:25:06.355 GMT [14050] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.356 GMT [13189] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.357 GMT [15155] LOG:  could not receive data from
> client: Connection reset by peer
> 2024-06-21 12:25:06.357 GMT [15155] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.361 GMT [14736] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.362 GMT [15193] LOG:  could not receive data from
> client: Connection reset by peer
> 2024-06-21 12:25:06.370 GMT [15193] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.409 GMT [14504] LOG:  unexpected EOF on client
> connection with an open transaction
> 2024-06-21 12:25:06.444 GMT [13380] LOG:  unexpected EOF on client
> connection with an open transaction
>

In pgpool logs, it is mentioned that the postmaster was shut down by
administrative command. But I specifically mentioned the pid of the
query whose state was idle in transaction.

Please help me understand what caused pgpool to shut down.



-- 
*Thanks & Regards*
Aviral Singh
Contact No: 8765789951
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240625/c118531e/attachment.htm>