[pgpool-general: 4027] Re: potential work-around to safely issue pg_terminate_backend()

[Pablo Sanchez]

[ Comments below, in-line ]


On 09/01/2015 07:19 PM, Tatsuo Ishii wrote:

Hi Tatsuo,

> [ trimmed ]
>
> BTW, there was an idea when pgpool-II was born. Flexible control
> over the number of child process like apache. The idea was,
> pgpool-II star(t)s with num_init_children (that's why "init" is in
> the name),

Ah, that explains /num_init_children/.

> [ trimmed how /Thundering Herd/ problem can be solved ]

> Going back to the original problem. Probably pgpool-II parent could
> occasionally check the number of child process (maybe in the health
> check loop?) and if it is less than num_init_children, it would fork
> a new one.

Yes, I think the above makes a lot of sense to do.  Is that an easy
patch to provide?  :)

Below are my instructions on how to terminate a backend DB process
without having PGPool degenerate.  All feedback welcomed.

---8-<--8-<--8-<--8-<--8-<--8-<--8-<--8-<--8-<-
::: Recipe :::

Problem
=======
At times the only way to terminate a wayward connection is to use
/pg_terminate_backend()/.  If the connection was initiated by PGPool, a
very unfortunate side-effect is it will cause PGPool to degenerate:
fail-over.

Below are instructions on how to /disconnect/ a connection from PGPool
so it can be terminated - see the Disclaimer below.

Disclaimer
==========
With PGPool 3.4.2, killing a connection reduces the /Connection Pool/
by one.  If a sufficient number of Connections are killed, PGPool
should be restarted - see the 'smart' option so a Live Failover can be
performed.

Issue pg_cancel_backend()
=========================
Try /pg_cancel_backend()/ first.  If this fails, continue ...

Log on to the DBMS (Master or Slave) and Find the Query
=======================================================
$ psql postgres postgres

SELECT pid, client_addr, client_port, query
FROM   pg_stat_activity
WHERE  UPPER(query) LIKE '%SOME_STRING%';

On the Master PGPool, identify the PGPool Process
=================================================
Determine the corresponding PGPool process:

    lsof -i :<<client_port from above>>

Kill the PGPool Process on the PGPool Master
============================================
As /root/ terminate the PGPool connection:

    kill -INT <<pid from lsof>>

Kill the dangling connection on Postgres
========================================
By issuing the /kill/ in the previous step, the process is disconnected
from PGPool. Now a /pg_terminate_backend()/ can be safely be issued
without affecting PGPool.

    psql postgres postgres

    SELECT pg_terminate_backend(<<the original pid from above>>);
---8-<--8-<--8-<--8-<--8-<--8-<--8-<--8-<--8-<-
--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926         Blog:  http://pablo-blog.blueoakdb.com
iNum:  883.5100.0990.1054