Promoting a standby node
Pgpool-II manages a streaming replication primary node and multiple standby nodes. Suppose we shutdown the primary node. Pgpool-II detects the event and starts a command called "failover command". The failover command is a user supplied script (usually written in shell or other scripting language), and it choose one of standby nodes to be promoted. Typical script chooses the "next main node", which is the next live node to the former primary node: e.g. if the former primary node is 0, and there are node 1 and 2 standby nodes, node 1 will chosen.
Another way to promote a standby node is to use "pcp_detach_node". The command is one of the control commands for Pgpool-II. pcp_detach_node can "detach" a node so that Pgpool-II changes the internal status of the specified node to be down (but the node is actually up and running). In this case the failover command is also called and the next node - in the example above node 1 - will be chosen.
Promoting specified node
What if we want to promote node 2, instead of node 1? Actually there had been no way to achieve this until today. The next version of Pgpool-II 4.3, supposed to be released in this winter will allow you do it.
Existing pcp_promote_node command now has extra argument: --switchover or -s.
pcp_promote node --switchover 2
will do followings:
- detach the current primary node. This makes the current primary node to be in "down" status (but the node is actually up and running).
- failover command is triggered. Node 2 is passed to the failover command as the "new main node".
- failover command promotes node 2.
- "follow primary command" runs against node 0 and node 1. Follow primary command is a user supplied script to make a node to be standby node, "following" current primary.
- As a result, node 0, 1 are standby following new primary node 2.
Conclusion
Pgpool-II 4.3 will have one of the long awaited features: promoting specified node. I hope this gives admins more flexible way to manage PostgreSQL clusters.
