[Pgpool-general] pgPool HA set up

[Thom Brown]

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

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?

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

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

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?

Thanks Guillaume.

Thom