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

[Gilles Darold]

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.

Thanks for your comments.

Best regards,

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