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

[Tatsuo Ishii]

> 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?

Yes.

> 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.

Of course.

> 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.

I don't understand this. You could have two pgpool-II instances at a
same time if you enable "watchdog" functionality of pgpool-II. What
kind of problems do yo see in this configuration?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> 
> 
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general