Re: Automatic Client Failover

On Mon, 2008-08-04 at 22:08 +0100, Simon Riggs wrote:
> When primary server fails, it would be good if the clients connected to
> the primary knew to reconnect to the standby servers automatically.
>
> We might want to specify that centrally and then send the redirection
> address to the client when it connects. Sounds like lots of work though.

One way to do it is _outside_ of client, by having a separately managed
subnet for logical DB addresses. So when a failover occurs, then you
move that logical DB address to the new host, flush ARP caches and just
reconnect.

This also solves the case of inadvertent failover in case of unrelated
network failure.

> Seems fairly straightforward to specify a standby connection service at
> client level: .pgreconnect, or pgreconnect.conf
> No config, then option not used.
>
> Would work with various forms of replication.
>
> Implementation would be to make PQreset() try secondary connection if
> the primary one fails to reset. Of course you can program this manually,
> but the feature is that you wouldn't need to, nor would you need to
> request changes to 27 different interfaces either.
>
> Good? Bad? Ugly?
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>