Hello.
Currently we're running all our PostgreSQL databases on ESX Stretch
Clusters, but our company is looking at moving away from those to a
private cloud solution.
I'm informed that, going forward, new servers provisioned for us to
install PostgreSQL databases on will be entirely isolated, using no
network/clustered storage of any kind but only local storage - so
stretch clusters, VCS etc are out.
To this end we're looking at using standard PostgreSQL
replication/multiple nodes to provide continued availability in the
event of a primary server failure, and I've been directed to set-up some
three node testbeds.
It was originally suggested we use Pacemaker/Corosync for this, but then
our Linux sysadm team advised that Pacemaker/Corosync hadn't been
certified for our particular cloud solution, and a VIP couldn't be done
as the database servers may exist on different networks/availability zones.
(BTW, I apologize if somebody reading this is going 'What nonsense! Of
course it will work! Here's how ...' - I'm no sysadm, I'm just going on
what I was told)
As a backup/contingency plan I'm now using repmgr to handle things - I'm
very happy with it (after getting it compiled :) ) it seems nice and
simple, and repmgrd is working nicely. A number of test failovers have
worked perfectly.
BUT ...
repmgr doesn't provide a mechanism to notify upstream connections that a
failover has occurred and that the master is now on server y instead of
server x.
I'm currently looking at pgbouncer, as that's proposed as a STONITH
mechanism in the repmgr git documentation.
The new master's repmgr promote script will execute commands to pause
pgbouncer, reconfigure pgbouncer to point to the new database address,
and then resume.
This seems like a straightforward and viable option, except that if we
only have one pgbouncer we've just introduced a new single point of
failure, and if we have multiple pgbouncers how best to handle that? A
pgbouncer on every application server, two or three pgbouncers in the
middle with some kind of load balancer, a pgbouncer on every db server?
Each option appears to have advantages and drawbacks.
So I was just wondering if the community had any
recommendations/suggestions/gotchas for this? Is pgbouncer a decent
choice, or are there better solutions out there? Has anyone tried
pgbouncer and found it worked well, or tried it and found it was a
disaster and moved onto something else?
Basically, I'm wondering if I'm on the right path or whether there's
something out there that everyone is now screaming 'Why on earth is this
idiot not using X instead?' :)
Any input would be greatly appreciated.
Regards,
Martin.
--
Martin Goodson
"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."
