[Pgpool-hackers] Promoting any node as master with SR slaves follow

[Gilles Darold]

Le 20/01/2011 21:37, Guillaume Lelarge a écrit :
> Le 20/01/2011 20:15, Gilles Darold a écrit :
>> Hi,
>>
>> PgPool is a very cool project, when I start to use it this was in plan
>> of PostgreSQL high availability and scalability using Pg 9.0 streaming
>> replication. The objective is to have one PostgreSQL master with 10 or
>> more slaves where I can load balance huge read only queries.
>>
>> Everything works well until a failover happens. When the PostgreSQL
>> master is down for some reason, PgPool force a failover to the next node
>> in the replication line. The problem is not with PgPool but with
>> PostgreSQL as there's no cascading replication yet. When the failover
>> occurs, the next node is promoted as master by PgPool but all other
>> slaves are still trying to connect/replicate to the old master. That
>> mean that all these nodes have to be reconstructed from the new master
>> again.
>>
>> The other point is that PgPool always takes the next backend declared in
>> his configuration file but maybe an other slave node has less
>> replication lags and should be promoted instead of the next one.
>>
>> All this make me do some work on PgPool source code as I think it may
>> handle that easily. The patch do the following:
>>
>> Add a PCP command to promote any node in the pgpool line as the new
>> master : pcp_promote_node
>> Add a follow master configuration option to call an external command
>> when the failover is over.
>>
>> So that if I run :
>>
>>     pcp_promote_node 10 192.168.1.11 9898 postgres postgres 3
>>
>> Node 3 is promoted as master and all other nodes are degenerated.
>>
>> When the failover is ended PgPool fork a child to execute the command
>> given in the follow_master_command successively for each node
>> degenerated. The follow master command is simply a call to the
>> pcp_recovery_node.
>>
>> I've played some time with that patch and it works pretty well for me.
>>
>> Well before going further I need to know if this is something useful
>> that can be applied to PgPool? I also need help for testing under other
>> modes than streaming replication.
>>
> Am I wrong or you forgot to send the patch

I've not attached the patch as I want to have community advices before.
It also still need some sanity check that I will do today.

Regards,

-- 
Gilles Darold
http://dalibo.com - http://dalibo.org