[Pgpool-general] pgPool HA set up

[Thom Brown]

Hi,

I want to be able to have a primary/standby PostgreSQL setup with
streaming replication and using pgPool to deal with the load balancing
and auto failover.  One question I have is can I have 2 instances of
pgPool pointing to the same database servers?  In addition to this,
what if pgPool1 and the primary were in one data centre, pgPool2 and
the standby were in another data centre, then connectivity went down
between the data centres.  On top of this would be a connection load
balancer to map incoming connections to one of the pgPool instances
(in case one of the pgPool instances dies).  It looks like pgPool2
would promote the standby to a primary, then when connectivity was
restored, there'd be a split brain issue where write queries would
randomly hit one of the servers, making them instantly out of sync.
This seems like it should be a very common setup, so does pgPool have
provisioning for this, like having a 3rd pgPool to manage them
somehow?

Also, if there was a primary and 2 standbys, and the primary goes
down, which promotes standby1, how does standby2 then subscribe to
standby1 automatically?  And how does pgPool then know how to trigger
failover on the next standby if the new primary fails?

And when I set up pgPool today with a primary and standby using
streaming replication, queries only seemed to hit the primary.  Any
idea what I could have missed?  Aren't SELECT queries supposed to be
randomly sent to one of the servers?  I ran queries several times and
never did any hit the standby.

Just to note, the config changes were something like this:

failover_command = '<command to put trigger file on 1st standby'
backend_* = <these ones are all commented out as I'm only using pgPool
for load balancing>
replication_mode = false
load_balance_mode = true
replicate_select = false
backend_hostname0/port0... = <this was set to the primary>
backend_hostname1/port1... = <this was set to the first standby>

Thanks

Thom