Re: Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 9:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> That sort-of ties into what seems to me the main objection to this
> proposal, namely that there is already a way to do this sort of thing:
> DNS-based load balancing. All the clients think they connect to
> db.mycompany.com, but which server they actually get is determined by
> what IP address the DNS server tells them to use.

But that kinda sucks. I mean, suppose I have three servers, A, B, and
C. I point db.mycompany.com to A, which is the master; then A dies.
Under your proposal, whatever script I use to control failover now has
to change the DNS records to repoint db.mycompany.com to B, my new,
and newly-promoted, new master. It's quite possible that some
machines on the network, or some processes, will have the old IP
address cached, and it may be several minutes before those caches time
out. In the meantime, I'm down: even if I bounce the application
servers, they may just try to reconnect to A.

Victor's proposal is far more convenient. When A goes offline, the
servers automatically begin trying to connect to B and C. Let's
suppose I use iptables or something like that to prevent connections
to B and C as long as A is online. Or pg_hba.conf or whatever. But
once I'm sure A is dead, I can promote B and reconfigure it to allow
connections *and I'm done*. At most, I need to restart my application
servers. I don't need access to the DNS server - which the guys in IT
are unlikely to provide to a lowly DBA anyhow. I don't have to worry
about stale caches. Everything just works.

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