Could anyone please help to implement High availability, replication and failover for postgresql.
We are using postgreSQL 14 on VMs (planning to upgrade soon).
We have a single server and planning to achieve:
i) High availability (1st priority)
ii) Automatic Failover (1st priority)
iii) Data Replication. (1st priority)
iv) Fault detection (2nd priority)
v) Monitoring and Alerts (2nd priority)
vi) GUI (2nd priority)As far as I know , postgres does support different replication options but it does NOT support automatic failover and load balancing.
As per my research, I found the below list of tools but all of the required features are NOT provided by any one tool. Every tool has some pros and cons.
pgpool-II: it provides pooling and load balancing but it does not provide automatic failover.
Patroni: it provides automatic failover but it is not good for load balancing.
ClusterControl: Another candidate with free community addition but failover is only in paid version. Not sure how efficient it is.
EnterpriseDB: Another one fully paid. Exploring each and every tool to understand its capabilities and limitations would be a very time consuming task.
I want to know which tool or set of tools are being used mostly so that I can narrow down my research to make an efficient decision quickly. Open source tools are our preference but if some paid tools provide almost all of the features, we would think of it.
Thanks,
Arif