Re: Proposal: Implement failover on libpq connect level.

On Wed, Sep 2, 2015 at 9:00 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Sep 2, 2015 at 4:52 AM, Shulgin, Oleksandr
> <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
> > On Tue, Sep 1, 2015 at 8:12 PM, Andres Freund <andres(at)anarazel(dot)de>
> wrote:
> >>
> >> On 2015-09-01 14:07:19 -0400, Robert Haas wrote:
> >> > But I think it's quite wrong to assume that the infrastructure for
> >> > this is available and usable everywhere, because in my experience,
> >> > that's far from the case.
> >>
> >> Especially when the alternative is a rather short patch implementing an
> >> otherwise widely available feature.
> >
> > But that won't actually help in the case described by Robert: if the
> master
> > server A failed, the client has no idea if B or C would become the new
> > master.
>
> Sure it does. You just need to ensure that whichever of those is the
> new master accepts connections, and the other one doesn't. There are
> lots of ways to do this; e.g. give the machine a second IP that
> accepts connections only when the machine is the designated master,
> and have read-write clients connect to that IP, and read-only clients
> connect to the machine's main IP.
>

Well, I see how that can help, but still sounds like a lot of hassle.

What if you have 5 servers: A..F, listed in client's connection settings in
that order, and after failing over from A, now F is the new master (for
whatever reason: I don't think it would be realistic to assume that you can
and always will fail over to the next host in the list). So suddenly, the
read-write clients need to make 5 connection attempts before arriving at
the master (add name resolution to the picture for even more latency).
Connection pooling can probably mitigate this to some degree, of course.

I believe that having a floating IP for the master is much more practical
approach and it doesn't require any patch to libpq or modification of the
client connection settings.

Andres's point is the same as mine: we ought to accept this feature,
> in some form, because it's really quite useful.
>

Even if someone is keen on implementing the multiple connection strings
approach, nothing stops them from doing that on top libpq, and I really
think it will be ever more flexible than anything we can build into libpq
itself.

--
Alex