How to make a postgres cluster?

Question

PostgreSQL

Hello! There is a service working on the basis of postgress....
The service communicates with the database via API only through stored procedures.
That is, there are no direct queries to the database of the SELECT type, and so on. All requests are sewn up in procedures.
How to set up a failover cluster? master-master or which is better in this case?
does it make sense to separate procedures for reading and writing,
stored procedures have read and write queries

Answer 1

To begin with, ask yourself and answer the question "why?"
Because there are a lot of hemorrhoids, problems and rakes. Bonuses are doubtful. The main layer of problems is how to decide that it is time to switch the master to another host, and the old master will not return due to a short (or even planned) network lag? This is an organizational issue and it is not solved by automation. The happiest thing with automation is to get a split brain and get a long and exciting quest "as if the scattered data is now making friends together"
master-master = permanent headache. Because the fundamental CAP theorem, which no one has yet clearly solved. Or you have problems with consistency or slow writing due to distributed transactions.
Automation for failover, which I did not figure out how to provoke on split brain - patroni . It seems to work. But I've seen it in production only once so far.
It makes sense to divide procedures (and requests in general):
- writing. Any that write something in the database (including create temporary table). This should be routed strictly to master
readers. They go to replicas
- they read for a long time. They go to separate slow replicas, which may noticeably lag behind the master, but which will not interfere with the project's activities