3 ms·
> Can I have a two-node cluster of PostgreSQL machines where a failure in the first results in failing over to the second automatically? It's okay if I have to
by snuxoll 10y ago
> Can I have a two-node cluster of PostgreSQL machines where a failure in the first results in failing over to the second automatically? It's okay if I have to run something manual on the first just to bring that back up to speed and act as the new secondary.
You can do this with repmgr from 2ndquadrant, see the documentation [0] for details on the configuration. Since repmgrd doesn't require anything other than postgres to be running, it doesn't add any new failure points.
One thing you need to make sure you do right in such a setup is fencing the failed node, pgBouncer or pgPool is a good way to go about this - in your failover script you can modify your configuration on the pgBouncer server to point at the new master before the failover takes place, this will prevent clients from talking to the failed master and prevent a split-brain.
Alternatively, you can use something like keepalived and STONITH, but I'm not comfortable relying on somehow shutting down or cutting off the other machine from the network as it is less reliable than modifying a configuration file if things are going really wrong.
[0]: https://github.com/2ndQuadrant/repmgr https://github.com/2ndQuadrant/repmgr
- arjie 10y agoAh ha, thank you for your advice and summary of the current state of things. It looks like I shouldn't be attempting what I said.
- snuxoll 10y agoYou can do it, the problems start coming when you then try to automatically go back to the failed master. PostgreSQL 9.6 makes this easier, and we should see some more automated tooling around it - but in general I have learned that unless you are using shared storage automatic fail-over clusters can be hairy and generally avoided.