Re:It is done, the PostgreSQL community rocks
by feld on Wednesday April 05, 2017 @09:42AM
(#14042005)
No, use anycast or VRRP/CARP for Postgres servers. Use a watchdog script to monitor for master failure. When it fails, the secondary/slave IP becomes live. Watchdog script should take necessary action to make it recognize it is master/writable. Clients don't have to know what happened.
Bringing the master back live and making it primary is a more difficult task. I would not want to automate this anyway as it would be too easy to shoot your foot off.
Re:It is done, the PostgreSQL community rocks
by snuxoll on Wednesday April 05, 2017 @10:09AM
(#14042165)
Personally I think pgBouncer is a better solution for handling the switch to the correct master during a failure. I use keepalived (VRRP) for managing my HAProxy machines and while it's certainly reliable there's more issues with it that you have to be aware of than simply flipping the connection string in pgBouncer.
That would be awesome, but stock Postgres only provides some of the building blocks.
For past few days I've been investigating stolon [1]. Conceptually, it makes sense to me. And so far it seems to work. But it's not simple to set up and maintain. For example, for leader election it uses etcd or consul. So initially you had one service that must not go down, --now you have two! ;-)
[1] https://github.com/sorintlab/stolon