How best to organize failover clustering PostgreSQL?

It took to deploy Postgres 9.6 for 1C. Took them a distribution deployed on a VM under centos7, set up replication on the second the same, now think about how this thing is to make the cluster. Host system (hypervisor) only two, stonith is not available (or I don't know how to cook, but dvuhmetrovom the cluster when the disconnection occurs or stonith deathmatch or split brain, so it solves little), so while thoughts of just about any configuration from a remote third Noda on another network. At the same time host assembled Microsoft cluster on which to raise Vmci, that is, one can formally make a VM with Postgres cluster and resolve issues falling through the cluster resource service (on the assumption that the fall will not Postgres and hardware). The third host in the cluster not to supply - not enough FC connections to storage, put it is possible, but expensive (with) head. Whether in such a scenario, do care about fault tolerance, and if it is to do, how? It is desirable that the loss of one VM or one hypervisor is not disconnected, Postgres as a whole and did not lead to data loss.
June 10th 19 at 15:44
5 answers
June 10th 19 at 15:46
Solution
Eventually did this:
Three VM, one cluster on the storage with the role of master, two on the local disks of the hypervisors with roles hot standby, set up streaming replication using wal sender in the "to burn one copy, then complete the transaction" (setting synchronous_standby_names = '1 (*)' ), and received about a search - in case one hypervisor goes one line and maybe the master, but the master rises at the second node and with the remaining replica is successfully saves the data. The bottleneck remains the master, if there is any damage, Postgres will fall as a whole, but at least you can get the data from the replica and to raise the master again.
If you have the time look the same stolon, it is possible to achieve automatic operation when changing masters.

I have 4 nodes, 3 in one DC and 1 DC in the second. 3 nodes, one master, 1 synchronous replica of the asynchronous one. All automatic switching occurs.
Everything runs on top of Docker swarm that allows almost zero administration, sworn himself teetering containers on the node. - marlene_Tromp69 commented on June 10th 19 at 15:49
I came that all VMS should be equal, plus a minimum of three is necessary - and I host a maximum of two, for no money (C). But Yes, they considered it as an option Stolon control postgresol. - sadie_Hop commented on June 10th 19 at 15:52
June 10th 19 at 15:48
There is a range of solutions. The problem is that you need to choose the most suitable for you. Here briefly are painted options postgresql.leopard.in.ua/html
The customer had specific requirements. He had to make the decision worked in docker swarm.
Considered RepMgr, Patroni, Stolon. Liked https://github.com/sorintlab/stolon.
The floor of the flight base is 30 gig. about 50 users. The normal flight.
June 10th 19 at 15:50
I this problem is solved by using pgpool on a third dev. All connections through it.
June 10th 19 at 15:52
Worth or not depends on the requirements.
Without these requirements you cannot answer that correctly.
The cluster has well-defined objectives, and it is possible that you are overengineering and you can simply remove the backups with the correct frequency without any clusters.
June 10th 19 at 15:54
Now here 's a great article on habré under the solution to your problem.

Find more questions by tags clustersPostgreSQLSystem administrationLinux