[pgpool-general: 3572] Re: Out of sync with actual db state (master slave via streaming)

Mark Kirkwood mark.kirkwood at catalyst.net.nz
Thu Mar 26 11:48:51 JST 2015


On 25/03/15 18:32, Tatsuo Ishii wrote:
>> I've stumbled across a number of scenarios where pgpool's view of the
>> db nodes is out of sync with the actual state of them:
>>
>> 1/ Restart
>>
>> If a standby is restarted (or stopped and started) then it will
>> usually continue streaming ok, but pgpool things it is still down, and
>> the health check does not seem to realize it is back and ok"
>>
>> => SHOW pool_nodes;
>>  node_id | hostname | port | status | lb_weight |  role
>> ---------+----------+------+--------+-----------+---------
>>  0       | db1      | 5432 | 2      | 0.333333  | primary
>>  1       | db2      | 5432 | 3      | 0.333333  | standby
>>  2       | db3      | 5432 | 2      | 0.333333  | standby
>> (3 rows)
>>
>> but directly from the primary:
>>
>> =# SELECT client_addr,state,sent_location,replay_location ,sync_state
>> FROM pg_stat_replication;
>>   client_addr | state | sent_location | replay_location | sync_state
>> ----------------+-----------+---------------+-----------------+------------
>>  192.168.122.72 | streaming | 0/E5F68C98    | 0/E51095E8      | async
>>  192.168.122.73 | streaming | 0/E5F68C98    | 0/E581FFF0      | async
>> (2 rows)
>>
>> Is there some config I'm missing to get the health check to realize
>> the standby is back and ok?
> 
> It's an expected behavior. Pgpool-II never automatically reattaches
> any DB node.
> 

Right - I did wonder, but figured I'd ask in case I'd missed something.
Thanks!


>> 2/ Failover/Recovery
>>
>> If a failover is initiated (say by stopping postgres on the primary),
>> but with pgpool sessions still active then this can happen:
>>
>> => SHOW pool_nodes;
>>  node_id | hostname | port | status | lb_weight |  role
>> ---------+----------+------+--------+-----------+---------
>>  0       | db1      | 5432 | 2      | 0.333333  | primary
>>  1       | db2      | 5432 | 2      | 0.333333  | standby
>>  2       | db3      | 5432 | 1      | 0.333333  | standby
>> (3 rows)
> 
> Not sure what happend here. Maybe I can chase the cause by looking at
> the pgpool log.
> 

Ok - I'll see if I can reproduce again.


Cheers

Mark



More information about the pgpool-general mailing list