Re: Proposal: Implement failover on libpq connect level.

Victor Wagner wrote:
>> I wonder how useful this is at the present time.
>>
>> If the primary goes down and the client gets connected to the standby,
>> it would have read-only access there. Most applications wouldn't cope
>> well with that.

> It is supposed that somebody (either system administrator or some
> cluster management software) have noticed failure of master and promoted
> one of the standbys to master.
>
> So, clients have only to find out which cluster node serves as master
> just now.
>
> Idea is that we don't need any extra administration actions such as IP
> migration to do it. Clients have list of alternate servers and discover
> which one to work with by trial and error.

Yes, but that will only work reliably if the (read-only) standby does not
allow connections before it is promoted.

> I consider in my proposal following situations:
>
> 1. Warm standby - doesn't accept client connection at all unless
> promoted to master.
>
> 2. Hot standby - we have two types of clients - one for which readonly
> access is sufficient, and other that need to connect only to master.
> In this case intention to write is explicitely stated in the connect
> string (readonly=false) and connect procedure would check if node it
> tries to connect allowed write.

I think that these are both valid use cases.

And as Robert said, there are people out using BDR or other proprietary
multi-master solutions, so there might well be an audience for this feature.

>>> "host=main-server host=standby1 host=standby2 port=5432 dbname=database"

>> It seems a bit arbitrary to require that all servers use the same port.
>> Maybe parameters like host2, port2, host3, port3 etc. might be better.

> I've thought about this approach. But PostgreSQL administration guide
> insists that all servers in the cluster should have as identical
> configuration as possible to simplify administration.
>
> Moreover I've seldom have seen configurations where postgresql is
> accepting connection on non-default port.

We do it all the time.

> Using host1, host2 etc would have unintended connotations, such is "this
> is first, main server". I think that client should treat all given
> servers as equal and let cluster administration to choose which one
> would accept connection.

I don't think that my idea of "host", "host3" is very appealing myself,
but I still don't like your original proposal of having multiple "host"
parameters.

One problem with that is that this syntax is already allowed, but
your proposal would silently change the semantics.
Today, if you have multiple "host" parameters, the last one wins.
So with your modification in place, some connect strings that work today
would start behaving in unexpected ways.

Maybe a better idea would be:
host=db1.myorg.com,db2.myorg.com port=5432,2345

Yours,
Laurenz Albe