What is the best way to organize a PostgreSQL failover cluster?

Question

linux

It was required to deploy postgres 9.6 under 1C. I took their distribution kit, deployed it on a VM under centos7, set up replication for the second one, and now I'm thinking about how to properly make a cluster out of this. There are only two hosts in the system (hypervisors), stonith is unavailable (or I don’t know how to cook it, but in a two-host cluster, when the network breaks, either stonith deathmatch or split brain occurs, so it doesn’t solve much), so for now, thoughts are only about some some configuration with a remote third node on another network. At the same time, a Microsoft cluster is assembled from the hosts, on which VMs can be raised, that is, you can formally make a VM with postgres a cluster one and solve the issues of resource drops through the cluster service (assuming that it will not be postgres that will fall, but the hardware). Do not put the third host in the cluster - there are not enough FC connections to the storage, You can put it separately, but it's expensive (c) boss. Is it worth it in this scenario to toil with fault tolerance at all, and if you do it, then how exactly? It is desirable that the loss of one VM or one hypervisor does not disable postgres entirely and does not lead to data loss.

Answer 1

As a result, I did this:
Three VMs, one clustered on storage with the master role, two on local disks of hypervisors with hot standby roles, set up streaming replication via wal sender in the "write one copy, then complete the transaction" mode (parameter synchronous_standby_names = '1 ( *)' ), and got approximately what you were looking for - in the event of a fall of one of the hypervisors, one replica falls down and possibly the master, but the master rises on the second node and saves data quite successfully with the remaining replica. The master itself remains the bottleneck, if there is any damage in it, the postgres will fall as a whole, but at least it will be possible to pull the data from the replica and raise the master again.

Answer 2

I solve this issue using pgpool on the third virtual machine. All connections go through it.

Answer 3

Worth it or not depends on the requirements.
Without knowing these requirements, it is impossible to answer which is more correct.
The cluster has very specific goals and it is quite possible that you are engaged in overengineering and it will be enough for you to just take backups with the correct frequency without any clusters.

Answer 4

Here is an excellent article on Habré for solving your problem.