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

Tatsuo Ishii ishii at postgresql.org
Fri Jul 12 08:02:57 JST 2013

It's generally called "split brain". It is known that there's no
automated solution for that. There are some workarounds:

1) Make the connectivity between the two locations highly
   available. e.g. use two or more redundant connections.

2) Allow each location to update its own data freely. After the
   connection comes up, solve the data conflict in some way to recover
   data integrity. Unfortunately pgpool-II does not provide such
   conflict resolution functionality, you need to do that outside

3) Abandon one of the location or do not allow data update in one of
   the location. Problem is, how to decide the victim location. You
   have to decide yourself. Fortunately pgpool-II may help you. you
   specify a network switch or a server IP in
   "trusted_servers". Pgpool checks if it is reachable or not. If not
   reachable, the pgpool suicides itself.
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Thanks for the reply, I'll have a play with 3.3 with watchdog.
> I do however see an issue if I have 2 web servers at 1 location with 1 db
> and the other 2 at the other location with 1 db and they lose connection to
> each other they will they will both promote the db they can connect to
> master which would cause data integrity issues.
> Is there anyway of dealing with this ?
>  On Jul 11, 2013 9:16 PM, "Tatsuo Ishii" <ishii at postgresql.org> wrote:
>> > 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

More information about the pgpool-general mailing list