PostgreSQL High Availability (Replication, Failover, Virtual IP)

[ykdhan]

Hi, I am trying to implement streaming replication with automatic failover on my two PostgreSQL servers.
I want to have a single Virtual IP pointing to the currently-Master DB server.
Could anyone please suggest what applications to use for the above structure? I tried using different things but cannot come up with a perfectly-working combination of all features above.
HAProxy, Pacemaker (PAF), Patroni, Repmgr, Keepalived, Pgpool II are the ones I have looked up and tried using. Do I even need another witness server for this?
Thank you in advance!

[Zdenek_OMNISENSUIT]

Hello.

You can use HAproxy, target your primary DB, use with healthcheck and in case of not responding for some time HAproxy will redirect to your standby DB for example.
I have a good experience with this and 3node MariaDB Galera cluster behind this.

-------------------------
omnisensuit.com

[Colttt]

maybe you can take a look at citus: http://docs.citusdata.com/en/latest/

[dbacar]

cyber Why would you need keepalived, if you could, in haproxy, check which postgresql is active and route accordingly. I guess what you mean is using them alternatively, right?

[cyber]

HAProxy/keepalived cluster in front of 2 Postgres instances, which keep themselves in sync (pg_auto_failover, this might not work with newer versions of PG). HAProxy checks, which one is primary and targets everything there...
This post is in a bit wrong place... should be up there 3rd or 4th..:P

[cyber]

for DB VIP.. haproxy is 2 nodes also... but client sees only VIP, keepalived moves it between 2 hosts...

[themactech]

I have this working, but it only is one way, i.e., if the Master fails, everything toggles properly to the standby server, but it does not fail back to the master if the standby then fails. So we get alerted immediately after a failover so we can manually put the master back online (as a new standby).
Here are the steps we implemented:

- Setup Primary/Standby PostgreSQL cluster with streaming replication
- Setup Pacemaker with 2 Virtual IPs (one pointing to the master for R/W operations, one pointing to the slave for Read-only operations)
- Setup automatic failover with repmgr
- Replaced the failover commands in repmgr.conf with a bash script that:
- Launches the PostgreSQL failover
- Launches Pacemaker failover of the first VirtualIP
- Checks if PostgreSQL cluster still has an online standby:
- yes: Move 2nd VirtualIP to it
- no: Leave 2nd VirtualIP on newly promoted node
- Send SNMP trap that a failover occurred so we can quickly put the failed node back online

We also run a 'sanity_check' script every 5 minutes that verifies everything is running where it should be and send SNMP traps if it isn't

We also wrote some SNMP extensions to gather some Pacemaker/PostgreSQL metrics every SNMP polling session