PostgreSQL High-Availability has been one of the most dominant topics in the field for a long time. While there are many different approaches out there, Patroni seems to have become one of the most dominant solutions currently in use out there. Many database clusters run on Linux. However, we have seen some demand for a Microsoft Windows-based solution as well. The Patroni Environment Setup provides high availability for Windows.
Table of Contents
While installing Patroni on Linux has become pretty simple, it is still an issue on Microsoft operating systems. There are simply too many parts that have to be in place to run Patroni and many people have found it hard to deploy Patroni.
PES is a graphical installer for Patroni on Windows which makes it really easy to deploy Patroni High-Availability. It takes care of all relevant components including:
During the initialization step PES will try to find every instances already running over the network. Or the admin can skip this step and specify nodes and their roles manually.
PES will stall what is already installed and what is not. All missing parts will be deployed with minimal effort.
One of the key issues is to configure more than one server at a time. PES makes it easy to solve this problem. Simply start PES on all nodes - the installer will then search for other running installers on the network and automatically exchange information.
This greatly reduces the chances of configuration mistakes. If you want to build a reliable cluster it is essential that the etcd and Patroni configuration is not just correct but also consistent. By automatically exchanging information the risk of failure is reduced significantly.
PES is automatically going to configure all relevant services for you. It will make sure that ...
… are automatically started after a Windows reboot. The cluster is therefore ready for production use within minutes.
PES performs all necessary steps to provide you with a turnkey solution for PostgreSQL High-Availability and fault tolerance on Windows.
A database cluster consists of more than one machine. How does the application, therefore, know who is the current leader and which ones happen to be the replicas? The easiest solution to the problem is the introduction of service IPs. The core idea is to ensure that there is simply one IP all applications can connect to. The cluster itself will make sure that the service IP always points to the right server.
The tool capable of doing that on Windows and Linux is CYBERTEC vipmanager.
It checks etcd to figure out who the current leader is and makes sure the desired IPs are removed from a failed master and bound to the current leader.
The configuration is simple and easy to understand.
If you want to try out PES check out our GitHub repo or directly download our Windows installer (binary) here .
If you are looking for professional consulting services to setup PostgreSQL high-availability please check out our services.
+43 (0) 2622 93022-0
office@cybertec.at
Hello,
I want to implement PostgreSQL High Availability for Windows. I have two Windows Server 2019 machines and two different PostgreSQL instances installed in them. The use case scenario is that I need a master-slave configuration for the two postgresql servers, I managed to do a streaming replication and configurated the postgresql files for primary and standby servers. But I need an auto failover mechanism to make my hot_standby server to promote itself to primary etc. and that's very hard to find in Windows Server environment. I came accross Patroni and PES in my research and tried to install it to achieve my goal. But I'm stuck 🙁 I couldn't deploy Patroni Environment correctly. I kept getting an error called "Socket Error", I looked into the patroni log file and it says : "Installing service 'Patroni HA Windows Service (patroni)'.... Failed to find the account. No mapping between account names and security IDs was done." I suspect that PES couldn't install patroni service to the Windows Services. I manually installed the patroni service but it didn't change anything. I don't know what to do now. Is there a comprehensive guide for installation in Windows? I see there is a Linux one but I couldn't find a great guide for Windows Server.
Can you help me with that? Thank you.
Hi, we are sorry to hear you're stuck. We have pretty detailed manual for this case: https://github.com/cybertec-postgresql/patroni-windows-packaging/blob/master/doc/setup.md
From what I see, I would check user permissions and/or firewall settings. Hope you can solve that.
Let us know if we can somehow improve the installation process.
Regards!
Hello again, I have a question. Let's say I have 3 PC's in the cluster. One is master, one is slave and the other one is a witness for odd number etcd. (3 etcd) , What happens if my all servers including witness goes down? Does it continue to work seamlessly after I boot them up again? And if not, what should I do then?
Hey there. Your questions is specifically
patronirelated so I would suggest to read answers in thepatronimanual: https://patroni.readthedocs.io/en/latest/index.htmlIn short: yes, after you boot them up again they should work. They will detect the state of the system, try to sync and proceed.
If something really bad happens then you use Pause mode to fix database issues: https://patroni.readthedocs.io/en/latest/pause.html
Hello,
Thanks for your article, I have a question. I installed PES on my two Windows Server VPS, but they do not connect on first tab. I entered two nodes manually, but etcd demands odd number. I do not understand it. So why does it need minimum three nodes? I am satisfied with two. Or what else can I do? MSSQL Web costs money each month supporting failover cluster.
Hey Dmitry,
I'd like to recommend that you read about the requirement for an odd number of nodes over here:
https://www.cybertec-postgresql.com/introduction-and-how-to-etcd-clusters-for-patroni/
It boils down to this:
There is no advantage in having two nodes over a single etcd node, you don't gain any resiliency, as all two nodes would need to be online to achieve quorum.
For a proof of concept cluster you can try it with one etcd member and two patroni members.
Just keep in mind that patroni can't work if etcd cannot achieve quorum. For a one node cluster, if that node is offline, nothing will work. For a two node and a three node cluster two nodes need to be available for patroni to work etc. .
Hope this explains the constrain in PES a little bit.
Best regards
Julian
Thanks Julian. So it does not suit for me because I don't want to rent one more VPS. I suppose to make double server where one server is enough to work.
Best regards,
Dmitry
well, at least the third machine could be considerably smaller and cheaper compared to the machines that will contain the databases.
Etcd on its own should be fine with 2 vCPU, 4GB RAM, 60GB disk (those are number for a windows machine, for linux 1vCPU, 2GB, 10GB disk would be enough already...)