[pgpool-hackers: 1225] Re: Preventing disconection to clients on failover

[Muhammad Usama]

Hi


On Wed, Dec 16, 2015 at 2:15 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Currently pgpool-II always disconnects to clients whenever fail over
> happens because all the pgpool-II child process restart in the case.
> This is annoying and it's been one of the tops 3 compliants from
> users.
>
>
I think it would be a very good feature to have and will enhance the user
experience of pgpool-II.



> I think we could mitigate it in a certain condition:
>
> 1) Pgpool-II runs in the streaming replication mode
>
> 2) The failed node is one of standbys (not primary)
>
> 3) We accept that clients connecting to the standby cannot continue
>    the processing thus it will be disconnected from pgpool-II
>
> If all of the conditions above are satisfied, by changing pgpool-II in
> the way below, some of clients will not be suffered by the
> disconnection problem on fail over.
>
> 1) Let pgpool-II connects to the primary node and the load balancing
>    node only (currently pgpool-II connects to all live nodes).
>
> 2) When a fail over happens, lucky clients connecting to other standby
>    servers will not be affected by the fail over.
>
>
Pardon me if I am missing anything obvious here as I am just saying out
what's currently being on top of my head after reading this. What if change
the failover behavior of child and even when the connected node fails, the
child instead of restarting itself keeps the frontend connection alive and
cancels out the current query ( if query is currently running) and return
the ERROR back to front-end client for currently executing query, and
re-initialize the backend connections by keeping the frontend connection.
Or if there is no active query on the connection we just silently shift to
new backend server and do not disturb the front-end connection.  Yet if
there are scenarios where there is no easy way out we just disconnect the
front-end just the way we currently do

Thanks
Best regards
Muhammad Usama




> Question is, this is worth to implement or not (are users getting
> happier?). In a common configurations such as two DB node
> configuration, probability for the occurrence of the disconnecting
> problem would be 50% in average, because it is expected that half of
> clients are likely connecting to the failing standby (Of course the
> more we add standby servers, the smaller the probability of
> disconnection is).
>
> Maybe users could change the standby's weight parameter to 0, to
> prevent clients connect to the failing standby.
>
> Comments, thoughts?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20151216/d221703a/attachment.html>