Re: Proposal: Implement failover on libpq connect level.

On Tue, Sep 8, 2015 at 9:29 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> I'm not saying we shouldn't have something like this; but we need a
> clear definition of that common problem we are solving. I don't
> think I've seen that yet. I've seen various spins on solutions
> described, from which I can infer various possible problems; but to
> pick the best version of this as *the* solution I think we need a
> clear statement of the problem itself.

I think the problem we should be trying to solve is: Given a set of
server IPs, connect to one that is up.

I believe this comes up in several different scenarios.

Example #1: I need to move my database server to a different IP
address. I could have clients connect by name, but using DNS sucks
for the reasons already discussed. I could move the server and then
change all the client connect strings afterwards, but then I'm bound
to be down for longer than necessary. Instead, I'll change all of my
connect strings FIRST, to specify that either IP is acceptable. Then
I'll move the server to the new IP, and all of them will quickly find
that the old IP is down and the new IP is up and start using that
instead. At leisure, I can remove the old IP from connect strings
(and then re-purpose that IP).

Example #2: I am using EnterpriseDB's xDB multi-master replication, or
2ndQuadrant's BDR, to replicate among geographically distributed
database servers. I want clients to connect to their local server,
but if it is down, I want them to connect to one of the other masters.
Connecting to the local server first minimizes replication conflicts,
since most transactions on a given application server will be for data
local to that geography, but being willing to fall back to some other
server maximizes availability when my local server goes down.

Example #3: I have a master and and 3 SR standbys, all on different
subnets. Periodically, I fail over, so that the master role moves
around. Each server has an IP which can be used for read-only
connections. Each also has a virtual IP which is up when it is the
write master and down when it is a standby. Read-only queries are 90%
of my traffic, and eventual consistency is fine. So, for a read
query, I want to pick among the IPs that are up; for write IPs, I want
to find the one place that writes can be performed, but that might be
any of 4 virtual IPs.

I'm sure there are more.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company