[pgpool-general: 7200] Re: Blocked queries upon pgpool smart shutdown

[Bo Peng]

Hi,

On Thu, 13 Aug 2020 09:34:13 +0200
Michal Jemala <michal.jemala at gmail.com> wrote:

> Hi guys,
> 
> I deployed Bitnami's pgPool
> <https://github.com/bitnami/bitnami-docker-pgpool> as a Kubernetes
> container sidecar to proxy database queries from our API server to a
> Postgres DB. The DB is run and managed by Google
> <https://cloud.google.com/sql/docs/postgres>and has a master and 1 replica.
> The primary aim of this setup is to load-balance SQL statements between the
> Postgres master and its replica and the config pretty much follows pgpool's
> Aurora guide
> <https://www.pgpool.net/docs/latest/en/html/example-aurora.html>.
> 
> Upon updating the API server deployment, the pod gets terminated by
> Kubernetes by sending a SIGTERM signal. Thus our API server and pgpool
> processes running in separate containers within a single pod, will receive
> SIGTERM at the same time. API server handles SIGTERM and starts
> a graceful shutdown and so does pgpool. The pgpool's smart shutdown ensures
> child processes end gracefully. i.e. it waits for all clients to finish up
> and disconnect. However, I still see errors in my API server logs
> indicating the connection was closed by the pgpool.
> 
> Upon investigation it seems pgpool was not able to gracefully shutdown
> within a defined period (30s) and it was SIGKILLed by Kubernetes. API

Yes. Pgpool handles SIGTERM as a smart shutdown same as PostgreSQL.

> server logs show few hanging (> 20s) requests doing coupole of DB read
> queries. Usually these requests finish within a few ms. pgpool logs show me
> nothing (using default log config). Do you have any suggestion on what
> could be going on and how to best troubleshoot this? Could I get detailed
> logs from pgpool to follow the smart shutdown sequence? Or is there maybe
> pgpool misconfiguration which prevents the correct and quick shutdown.

I think you can start pgpool using "pgpool -n ..." 
and configure pgpool log as "log_destination = 'stderr'".

- config paramater
log_destination = 'stderr'

- start pgpool
pgpool -n -f <path to pgpool.conf> -F <path to pcp.conf> -a <path to pool_hba.conf> 

With the above configuration, all of the pgpool logs will be
redirected to the container's standard output,
then you can display logs using "kubectl logs" command.

If you share the logs, I can help you to confirm the reason.

> Many thanks,
> -Michal


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan