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

[Tatsuo Ishii]

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

1) There seems to be a window between pcp_promote_node and subsequent
   pcp_recovery_node calls. Do you have any idea that is safe or not?

2) Your opinion that the node to be promoted should be chosen by more
   intelligent rules(for example, the least lagging node), rather than
   just the physical position in the configuration file seems to be
   valid IMO. I wonder if we could implment automatic failover which
   is based on the rule, rather than manually promoting a node by
   using pcp_promote_node. Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp