[pgpool-general: 1888] Re: running pgpool on every web server

[Tatsuo Ishii]

> Hey,
> 
> I'm trying to set-up a ha PostgreSQL installation where there is 4 or more
> web servers and 2 or more PostgreSQL servers.
> 
> The PostgreSQL servers will run in master-salve replication mode and I
> would like pgpool to do failover and auto-recovery.
> 
> My knowledge of pgpool is limited so I have been doing alot of reading and
> looking through examples however every example I look at seems to have only
> 1 pgpool which to me just moves the point of failure onto pgpool.
> 
> My current thinking is I will install pgpool on each web server which will
> allow me to easily do connection pooling however I'm not sure if this will
> allow me to do auto-recovery and auto-failover as from what i read pgpool
> needs to be a "master" and with each web server having pgpool installed
> they will conflict when trying to change a slave to master or recover a new
> server.
> 
> Is this true or would I be able to install pgpool on each webserver without
> problems ?

As for failover, as long as each pgpool agrees on which PostgreSQL
fails, it should not be a big problem. However, for example, if the
network between pgpool #1 and PostgreSQL #1 fails but the network
between pgpool #2 and PostgreSQL #1 is healthy, it will be a problem.

Enabling "watchdog" somewhat mitigates the problem. For example, each
pgpool are forced to agree with the same PostgreSQL status.

However, some problems remain: on failover each pgpool initiates
failover process. You need to have a mechanism to prevent it, for
example using a lock file in the failover script.

Next version of pgpool-II (3.3), which is supposed to be released by
the end of this July, solves the problem. From the 3.3 release note:

    - Add interlocking mechanism of exclusive failover/failback command
      execution. (Yugo Nagata)
    
      When using multiple pgpool-IIs with watchdog enabled, failover commands
      (failover_command, failback_command, and follow_master_command) get
      executed only at one pgpool-II.
    
      Previously, these command got executed at all pgpool-IIs.

You can try out 3.3. The beta version has been already released.

http://www.pgpool.net/download.php?f=pgpool-II-3.3.0-beta1.tar.gz

> my webservers and postgresl span multiple subnets so I am unable to create
> a virtual ip and just change it when pgpool goes down :(.
> 
> Currently have postgresql version 9.1 install but can upgrade to 9.2
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp