Can someone please suggest what is one (Patroni vs PGPool II) is best for achieving HA/Auto failover, Load balancing for DB servers. Along with this, can you please share the company/client names using these tools for large PG databases?
Having used pgpool in multiple production deployments I swore to never use it again, ever.
The first reason is that you need a doctorate degree to try to understand how it actually works, what the pcp commands do in each scenario and how to correctly write the failover scripts.
It is basically a daemon glued together with scripts for which you are entirely responsible for. Any small mistake in failover scripts and cluster enters a broken state.
Even once you have it set up as it should, yes, it will fail over correctly but it won't autoheal without manual intervention.
You also often end up in weird situation when backends are up, pgpool reports down and similar scenarios and then you need to run the precise sequence of pcp commands to recover
or destroy your whole cluster in the process if you mistype.
I haven't used patroni yet but it surely can't be worse.
Best regards, cen