Proposal: PCP command for watchdog leader switchover (TODO: "Pgpool-II leader node switchover")

Hello,

I would like to propose implementing the TODO item "Pgpool-II leader node
switchover", and get feedback on the interface before I write a patch.

Background
----------

I originally raised this as GitHub issue #92 ("Enhancement Request: command
to switch the pgpool leader without service restart"), and Bo Peng kindly
added it to the project TODO list.

https://github.com/pgpool/pgpool2/issues/92

Our use case is keeping the watchdog leader co-located with the PostgreSQL
primary. That makes operational monitoring much simpler (leader and primary
appear on the same dashboard), and avoids an extra network hop for write
traffic.

A concrete example from our environment. We run three Pgpool-II nodes
with watchdog, and three PostgreSQL nodes managed by Patroni. Normally
node1 is both the watchdog leader and the PostgreSQL primary, which is
our preferred configuration because it is the lowest latency path for
write traffic.

During a routine SSL certificate rotation we shut down Pgpool-II on
node3, then on node1. node2 is then the only node left, so it becomes
the leader. We restart Pgpool-II on node1 and node3, and both come back
as standbys. There is no way to hand leadership back to node1, so the
watchdog leader is now stranded on node2 while the PostgreSQL primary
is still node1.

The cost here is not only operational inconvenience. In our environment
the first VIP failover, including the DNS update, settles in about 40
seconds under low to moderate load. Moving leadership back from node2
to node1 requires a second VIP change within a short time window, and
that has taken us approximately seven to eight minutes, because the
updated VIP information has to propagate to all nodes as well as to the
application hosts that connect to the database. That is a significant
outage for what is logically a planned and reversible administrative
action, so in practice we avoid doing it at all.

To realign them we end up doing the opposite of what we would like: we
use Patroni to move the PostgreSQL primary from node1 to node2 so that
it follows the watchdog leader. On the Patroni side that is a single
command:

patronictl switchover --candidate node2

On the Pgpool-II side there is no equivalent. Moving leadership means a
sequence of shutdowns and restarts timed so that the intended node wins
the election, and part of that sequence leaves Pgpool-II running on a
single node, with no redundancy and no quorum.

The value of this enhancement is therefore twofold. It would replace
that sequence with one command that selects the intended leader
directly, and it would avoid ever having to reduce the cluster to a
single running node in order to relocate leadership. Patroni already
provides this for the database layer, and we would like the equivalent
for the Pgpool-II layer.

Problem
-------

Today there is no way to move watchdog leadership to a chosen node. The only
supported lever is to stop Pgpool-II on the current leader and let the
remaining nodes hold an election.

wd_priority is not sufficient for our case, for three reasons:

1. It is CFGCXT_INIT, so it cannot be changed without restarting the node.

2. A returning node does not preempt a healthy leader. In
watchdog_state_machine_initializing(), a node that finds an existing
leader unconditionally becomes a standby regardless of its priority:

if (WD_LEADER_NODE)
{
/*
* we found the coordinator node in network. Just join the
* network
*/
set_state(WD_STANDBY);
}

So leadership cannot be "failed back" to a preferred node by restarting it.

3. Most importantly, the PostgreSQL primary itself moves on failover. A
static priority cannot track a moving target, so following the primary
inherently requires a runtime command rather than configuration.

Observation
-----------

Pgpool-II already implements a graceful, voluntary leader step-down. When the
leader cannot build consensus for a primary backend failover, it does this
(src/watchdog/watchdog.c):

if (need_to_resign)
{
/* lower my wd_priority for moment */
g_cluster.localNode->wd_priority = -1;
send_cluster_service_message(NULL, NULL, CLUSTER_IAM_RESIGNING_FROM_LEADER);
set_state(WD_JOINING);
}

This is nearly the whole mechanism: it de-prioritizes the resigning node,
informs the cluster, and set_state(WD_JOINING) takes care of VIP
de-escalation. What is missing is only (a) an operator entry point and (b) a
way to say which node should take over.

There is also an unused placeholder that appears to anticipate this feature,
in src/include/watchdog/wd_ipc_defines.h:

#define WD_COMMAND_REELECT_LEADER "REELECT_LEADER"

As far as I can tell this string has no handler and no callers anywhere in
the tree; process_IPC_execute_cluster_command() accepts only
SHUTDOWN_CLUSTER, RELOAD_CONFIG_CLUSTER, LOGROTATE_CLUSTER and
APPLY_LOCK_ON_STANDBY. Please correct me if it is reserved for something else.

Proposed design
---------------

A new PCP command that takes a target watchdog node ID:

1. PCP worker validates use_watchdog is enabled and the target watchdog node
ID is in range.

2. It issues WD_EXECUTE_CLUSTER_COMMAND with REELECT_LEADER and the target
node as an argument.

3. The command can be issued on any node. If the receiving node is not the
leader it forwards the request, following the pattern already used by
process_IPC_online_recovery(), which accepts the request on a node in
WD_STANDBY or WD_COORDINATOR state, forwards it, and refuses when the
local node is not in a stable state. Requiring the operator to first
locate the current leader would defeat much of the purpose, since not
knowing which node is leader is part of what makes the present
situation awkward.

4. The request is ultimately processed on the leader (as
APPLY_LOCK_ON_STANDBY already is). The leader validates that the target
node is reachable and in WD_STANDBY state, and refuses otherwise.

5. The leader sends a new cluster service message to the target instructing
it to stand for coordinator, then resigns using the existing path above.

6. The target transitions to WD_STAND_FOR_COORDINATOR, wins the election
since the former leader is now WD_JOINING with wd_priority -1, declares
itself coordinator and escalates the VIP.

The intent is to reuse existing, proven primitives rather than add a parallel
election path.

Open questions
--------------

1. Command name. pcp_promote_node already exists but operates on PostgreSQL
backend node IDs, whereas this would operate on watchdog node IDs. Two
adjacent commands taking -n with different ID spaces seems like a
potential source of operator error. Candidates: pcp_promote_leader,
pcp_switch_leader, pcp_promote_watchdog. I like pcp_switch_leader, Do you have a preference?

2. Should the command be synchronous, i.e. wait until the target has actually
declared itself coordinator and escalated the VIP, and report failure on
timeout? I lean toward synchronous with a timeout, since an operator
performing a planned switchover needs to know whether it succeeded.

3. Failure handling if the target does not win the election, for example
because it becomes unreachable mid-handoff. Should the former leader
attempt to reclaim leadership, or is falling back to a normal election
acceptable?

4. Should this be refused when quorum is absent, to avoid inducing a VIP
flap or split-brain?

If the approach sounds reasonable, I am happy to prepare the patch, including
a documentation page and a regression test modeled on
011.watchdog_quorum_failover.

Thanks for considering it.

Best regards,
Dibyandu Roy