[pgpool-general: 7155] Re: Long wait time when stopping Pgpool

[Tatsuo Ishii]

>  - I tried this option to stop and start pgpool but this affects load
> balancing. We I use systemctl to stop and start pgpool, then write
> statements are sent to primary only but when I use stopping and starting
> with "pgpool stop -mf" and "pgpool -f pgpool.conf -D" respectively, pgpool
> doesn't recognize write and sends to standby, this is resolved only after
> restarting the application

I am confused. How did you do when you say "I tried this option to
stop and start pgpool"? Pgpool's stop option is nothing related to
load balance.

Anyway I suspect there's something wrong with the systemd setting for
pgpool. Can you share /etc/sysconfig/pgpool?

> Because systemd kill -9 pgpool to not give a chance to remove the socket
> files.
> - This should not be the case right, because in production there will be
> connections all the time and after using systemctl these files should be
> removed

You can configure systemctl's parmeter for pgpool to shut down pgpool
properly even if there are connections from clients. That's why I am
asking /etc/sysconfig/pgpool.

> Thanks and Regards,
> Nikhil
> 
> 
> On Fri, Jul 17, 2020 at 3:49 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> First of all, you are asking questions about propriety
>> packages. Please ask questions regarding propriety products to the
>> vendor.
>>
>> > Hi Team,
>> >
>> > I am testing pgpool for my workload.We have three nodes of pgpool used
>> as a
>> > load balancer and connection pooler across three nodes of database
>> servers.
>> >
>> > All application connections to the database are through the pgpool.
>> Without
>> > stopping the application, if we want to stop the pgpool using "systemctl
>> > stop edb-pgpool-4.0" then it takes a long time because connections are
>> > still coming to the pgpool. Pgpool is stopped only after no new
>> connections
>> > appear + existing connections are terminated.
>> >
>> > Even after Pgpool is stopped, I can see that files(.s.PGSQL.9898 and
>> > .s.PGSQL.9999) under socket_dir which in our case is /tmp are not
>> removed.
>> > This does not allow the pgpool to start using  "systemctl start
>> > edb-pgpool-4.0". We have to manually remove the files under /tmp and then
>> > start pgpool.
>> >
>> > I have two questions:
>> > 1. Before stopping pgpool, how can we prevent new connections from coming
>> > into pgpool without stopping the application and terminate the
>> > existing connections so that the stop command does not wait.
>>
>> As other people already pointed out, there's an option to stop pgpool
>> without waiting for clients disconnected to pgpool. Probably you can
>> set the option to tweak systemectl related files. The way to do it
>> depends how the package is created, which we don't know. If you are
>> not sure, ask the vendor.
>>
>> > 2. After stopping pgpool, why are files under /tmp not removed?
>>
>> Because systemd kill -9 pgpool to not give a chance to remove the
>> socket files.
>>
>> > PS:Files are removed from /tmp if there are no connections via
>> pgpool(this
>> > will never be the case in production environment)
>>
>> Because if there's no connection from clients, pgpool can remove the
>> socket files before systemd kills pgpool.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>