[Pgpool-general] pgPool HA set up

[Guillaume Lelarge]

On Wed, 2011-10-05 at 12:22 +0200, Guillaume Lelarge wrote:
> 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.
> 

And I was wrong. At least when you first start pgpool.


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