On 14/06/2017 19:54, Rory Campbell-Lange wrote:
> On 14/06/17, Martin Goodson (kaemaril@googlemail.com) wrote:
>> 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.
>
> You could just move the service ip address at the new postgresql master
> to which the pgbouncer instances on each app server could reconnect.
>
> I assume, with reference to https://github.com/2ndQuadrant/repmgr, that
> you could use repmgrd to trigger a script to do just that at
> "switchover", possibly something you would need to monitor the
> "repl_events" table to achieve.
>
> Rory
>
Well, yes. That's pretty much the plan. See the repmgr documents:
https://github.com/2ndQuadrant/repmgr/blob/master/docs/repmgrd-node-fencing.md
-
In a failover situation, repmgrd promotes a standby to master by
executing the command defined in promote_command. Normally this would be
something like:
repmgr standby promote -f /etc/repmgr.conf
By wrapping this in a custom script which adjusts the pgbouncer
configuration on all nodes, it's possible to fence the failed master and
redirect write connections to the new master.
I'm just wondering how people may have implemented this. Do people setup
pgbouncer nodes on the database servers themselves, on application
servers, in the middle tier between the application and database, and so
forth, or some combination of the three? I can think of some advantages
or drawbacks for each. Or do people find that repmgr works better with
other tools to handle the promotion notification outside the database
cluster?
Basically I'm new to this, and I'm wondering how folks have handled this
issue. I'm basically looking for the community's wisdom :)
--
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."
