[pgpool-general: 2862] How to deploy pgpool with no single point of failure and without virtual IP address failover?

[Juergen Brendel]

Hello!

Still very new to pgpool, so hopefully, this questions isn't too dumb.

What is the recommended solution to setup a cluster without a single
point of failure in an environment where virtual IP address failover
might not be possible (some VPC environments, for example)?


Here is the description of the scenario I am after:

I'm mostly interested in pgpool for failover and would probably use
Postgres' built-in streaming replication (but I'm totally open to
alternative suggestions).

My scenario is a small cluster, consisting of two DB servers
(master/slave) with two or more app-servers, connecting to the DB
cluster via pgpool. My idea was as follows:


       app-server-1  ->  pgpool-instance-1 ----+
                                               |
                                               |
                                               +----+--> DB master
                                               |    |
                                               |    +--> DB slave
       app-server-2  ->  pgpool-instance-2 ----+


So, on each app-server machine, I would also run a pgpool instance. Both
pgpool instances know about the master/slave setup of the DB and both of
them could independently arrive at the conclusion that a failover needs
to take place. This is to avoid making pgpool the single point of
failure. Is this sort of setup possible?

The problem is that now we have two pgpool instances, which need to have
the same idea about the state of the DB servers and whether a failover
is needed.

As far as I understand, this problem can normally be solved by having
pgpool-HA with virtual IP address failover, so that (as far as the app
servers are concerned) there really only is one pgpool instance and only
one pgpool instance at any time will really be used. However, I'm in a
hosted VPC environment (something like EC2 or DigitalOcean) and I can't
just quickly failover any IP addresses.


So, what's the best way to proceed here?

Could one solution be to have multiple pgpool instances themselves
behind a load-balancer, such as HA proxy? Maybe run HA proxy on each app
server, have a small cluster (2) of pgpool hosts and use HA proxy to
balance between the pgpools, where one pgpool is weighted heavily, so
that the other is only used if HA proxy notices that the connection to
it has timed out, or something? That way, under most circumstances, only
a single pgpool instance is used (almost like in the virtual IP address
scenario). Could this be made to work? 


I would be grateful for any suggestions or recommendations.


Thank you very much!

Juergen