Re: Proposal: Implement failover on libpq connect level.

On 2015.08.18 at 08:32:28 +0000, Albe Laurenz 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.

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.

It seems that most people discussing in this thread think in millisecond
time intervals (failure and immediate reconnect).

I was thinking about much longer time intervals - it would probaly take
seconds to cluster management software to notice server failure and
promote backup server to master, it might be possible for application to
spend minute or so trying to reconnect, but it would take
hours to change connect string on clients - it would require visit of
support enginer to each client terminal, if we are thinking of
distributed OLTP system such as point-of-sale network with thick
clients.

>
> > "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.

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.

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