How to make a PostgreSQL failover cluster?

Question

PostgreSQL

Hello!
Need some advice from a PostgreSQL guru.
4 servers dedicated for postgre. It is necessary to combine them all into a failover cluster.
This is my first time dealing with clustering. What should you pay attention to? What are the solutions? In which direction should you dig?
Thank you very much in advance!

Answer 1

The DBMS multimaster is such a wonderful thing that everyone wants and no one has succeeded in yet.
Because there is such a thing as the CAP theorem, which makes it very difficult to make a silver bullet.
Okay, more to the point.
The biggest problem with automatic DBMS failover is deciding it's time to switch. Why it is necessary to find out what is happening in general, whether the network has collapsed, whether the master will return in a couple of seconds - if there are two masters in the network as a result, then there is no reasonable way to determine where the data is relevant. The worst thing you can imagine is a split brain: when some applications go to one master, some to another, and the data quickly diverge in different directions. There is such a bike about Googledisk "everything is fault-tolerant, all data is stored in 4 copies. True, we had an accident and now we have no idea which of the copies is up-to-date"
Switching the master to 90% is an administrative and human decision. Automation also adds a headache. Often - more headache than without it. Especially if the iron is adequate server, which "suddenly" drops out very rarely.
Usually there is no automation, and there is a detailed instruction for switching the database master, according to which you can switch even at night.
If you still want to be like a rake - check out patroni. There are a couple of clusters with it on support, I don’t remember any special problems with monitoring. At least I didn’t figure out how to bring it to split brain, like all options are covered. In the worst case, it goes to readonly.

Answer 2

Do not invent and use the standard, repeatedly tested option - master, slaves + WAL archive + backups.

Answer 3

Look at patrony