[Pgpool-general] pgPool HA set up

[Guillaume Lelarge]

On Wed, 2011-10-05 at 09:38 +0100, Thom Brown wrote:
> 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?

Yes.

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

That would be a total mess :)

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

Right.

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

No, it's not possible right now, and there's no discussion between
developers to do something like this (at least on pgpool-hackers).

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

pgpool doesn't know which one to promote. It just calls a script when a
failover is needed. Now, you can have a "smart" script that will try to
understand which slave is the most uptodate, and promote only this one.
Keep in mind that the script is really executed when a node is detached,
meaning every node, even slaves one. So your script needs to check if
the detached node is the master one.

In your example, for standby2 to follow standby1 (if standby1 is the new
master), pgpool executes a script (see follow_master_command option).

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

Setting load balancing? and having enough clients hitting your database?

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

You almost surely did this with one or two clients only, which won't
work. Use pgbench with 10 or more clients.

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

So you set the load balancing mode. I guess you didn't have enough
clients.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com