Search Postgresql Archives

Repmgr + pgbouncer - Notification of master promotion to application level ...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux