Re: Proposal: Implement failover on libpq connect level.

On 19 August 2015 at 14:46, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-08-19 09:41:32 -0400, Tom Lane wrote:
> > In fact, they'd still need to use DNS balancing for Postgres,
> > because not everything connects with libpq (think JDBC for instance).
>
> It already does support this though.
>
> https://jdbc.postgresql.org/documentation/head/connect.html :
>
> > Connection Fail-over
> >
> > To support simple connection fail-over it is possible to define multiple
> > endpoints (host and port pairs) in the connection url separated by
> > commas. The driver will try to once connect to each of them in order
> > until the connection succeeds. If none succeed, a normal connection
> > exception is thrown.
> >
> > The syntax for the connection url is:
> >
> > jdbc:postgresql://host1:port1,host2:port2/database
>

When we discussed this feature at the Dev Meeting in 2014, I thought we
agreed that allowing multiple hosts in the connection string would be OK.

+1 for bringing the jdbc driver URI syntax into libpq, so that all
interfaces can be optionally specified this way. This doesn't preclude the
use of ipfailover, in fact it might be work well together. If you don't
like it, don't use it.

I think we do need some way of saying that a readonly connection is OK. So
the default would be to connect to each in turn until we find the master.
It should keep retrying for a period of time since for a short period it is
possible there is no master. If you specify readonly, then a connection to
a standby is acceptable and it will stop there.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services