We are designing a migration from SQL cluster to a PostgreSQL.
Currently we have set-up a SQL HA cluster where we have automatic fail-over of the DB Servers.
We want to have this also in our PostgreSQL setup.
Does anyone have experience with one of these products or is there a reference architecture for this?
Thank you in advance
I dont have experience with this but i heard that VMware in future would like publish in some next version some separate appliance for PostgreSQL i think in this place willbe some HA functionality.
Maybe somebody from VMware can confirm this
Im migrated from MSSQL to PostgreSQL a few weeks ago and looks like this process is clear.
Unfortunately I do not know these two products. We have enough Linux know-how in-house to build Postgres clusters ourselves. With the help of the standard solutions that Postgres already has.
There is a chapter about HA, Load Balancing and Replication in the official Postgres documentation. Maybe this will help you:
There is also a page that lists the common solutions for Postgres Clustering because the cluster support and cluster administration is not really in the scope of the main postgres project. Citus Data is also listed there, so it's an official supported third party solution for postgres HA. If you don't have the know how or want to build it up, I would therefore take a closer look at Citus Data and test it.
We are currently using Patroni, etcd and haproxy/keepalived, which works very well for us. Pretty straight forward to set up and gives you the necessary 9's in the production environment.
Just follow their documentation provided with Patroni making sure that you have at least two postgres and haproxy nodes, and three etcd nodes. The minimum set up providing you with N+1 would be three VMs: VM1 (running postgres/etcd/haproxy/keepalived), VM2 (running postgres/etcd/haproxy/keepalived) and VM3 (running etcd), however I would strongly suggest splitting the load-balancing (haproxy/keepalived) from the postgresql/etcd lot, which means you will end up with 5 VMs.