Re: Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 09:21:50 +0000, Albe Laurenz wrote:

> > > Yes, but that will only work reliably if the (read-only) standby does not
> > > allow connections before it is promoted.
> >
> > It would just take a bit more time for client and a bit more load for
> > server - to make sure that this connection is read-write by
> > issuing
> >
> > show transaction_read_only
> >
> > statement before considering connection useful.
>
> That's not very comfortable, and a lot of middleware software won't easily
> learn the trick.

It shouldn't be left to middleware. It should be hidden into
PQConnectPoll. This function already handle very complicated state
transition diagram, including authentication, SSL negotiation and so on.
If we just add couple of new states such as CONNECTION_ASK_RW_STATUS
and CONNECTION_RW_STATUS_OK it should be fine.

Application would just call PQConnectPoll repeatedly (either via
PQconnectdb or explicitely when readable/writable condition detected on
the socket integrated into app even loop) until success or
unrecoverable error would be achieved. How many interaction with server
it would take, it is not middleware problem.

> > It seems to me that in most cases last host in the connect string would
> > be only host which accepts connections, so it wins anyway
>
> I'm not saying that it is particularly wide-spread and useful; it could
> happen through careless editing of connection strings or by using a
> connection service file entry
> (http://www.postgresql.org/docs/current/static/libpq-pgservice.html)
> and overriding the host parameter on the command line.

I don't think that host definition from all possible sources
(service file, environment, command line) should be collected together.
Although it is essential to be clear when host list is appended and when
- replaced.

If we implement sequential trial of all hosts, then we can start with
last one, to provide compatibility with existing behavior. In this case
if last host is online, no change occur.

Another idea - is to enable multiple host connection only if special
option (loadbalance or failover) present in the connect string.

> > Other idea - allow to specify host-port pair as argument of host
> > parameter.
> >
> > host=db1.myorg.com:5432
> >
> > It is consistent with URL syntax and system administrators are used to
> > it. And with long list of hosts there is less chances to made an error
> > as host and corresponding port come together.
>
> I don't think that is very attactive as it confuses the distinction between
> "host" and "port". What would you do with
>
> host=db1.myorg.com:2345 port=1234

I don't think that it does add any more confusion than simultaneous
existence of host and hostaddr, or ability to specify host and port both
in host part of URL and query parameters

postgresql://user(at)host:5432/dbname?host=otherhost&port=2345

Bot really, you've convinced me that syntax with two or three (host, port
and hostaddr) parallel lists is the best. Although we'll need to allow
empty entries in the lists such as

host=master.db.com,standby1.db.com.standby2.db.com port=,2345, hostaddr=,192.168.0.4,192.160.1.8

with evident semantic:
get port, host and hostaddr elements with same number, and if some of
them are empty, behave as it was only host and corresponding parameter
not specified at all.

--
Victor Wagner <vitus(at)wagner(dot)pp(dot)ru>