[Pgpool-general] pgPool HA set up

[Guillaume Lelarge]

On Wed, 2011-10-05 at 10:13 +0100, Thom Brown wrote:
> On 5 October 2011 10:05, Guillaume Lelarge <guillaume at lelarge.info> wrote:
> > On Wed, 2011-10-05 at 09:38 +0100, Thom Brown wrote:
> >[...]
> >> 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).
> 
> So no-one has ever configured pgPool to have true HA?  Isn't there any
> kind of black magick trickery one can do with the existing config
> options to achieve this?
> 

I guess true HA really depends on your context.

Say we have two data centers (DC1 and DC2), two PostgreSQL servers (PG1
and PG2), and two pgpool servers (PGP1 and PGP2). I would have all users
access to the database through PGP1. Say that DC1 isn't available
anymore, what I would do is change the IPVS so that all users go through
PGP2, change PGP2 configuration so that it cannot access to PG1, and
make sure that PG2 is the new master. Which of course is not automatic,
there are a few steps that need manual work.

But actually, what you are asking for is quite hard to have.

> >> 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).
> 
> Ah okay, I'll look into that.
> 

I'm interested in knowing any issues you can found while working on
this.

> >> 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.
> 
> Yes, you're right.  I had 3 clients connecting, mainly calling
> pg_sleep so I could monitor it in pg_stat_activity on both servers.
> 
> >> 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.
> 
> At least you didn't spot anything horribly wrong with these options.
> 

Nope, seems good to me.

> So in load balancing mode, is backend_*0 regarded by pgPool to be the
> primary, or is pgPool clever and polls the servers to figure this out?
> 

You mean, in master_slave mode. AFAIR, yes, it is. But I'll check.


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