Re: 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

Interesting. Does Patroni stop the current streaming replication
leader node1 and promote node2, then let node1 start as the standby to
connect to node in this case? If so, pcp_promote_node switchover could
do the same thing.

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

I agree 1-3.

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

Yes, it seems the definition was just added without any caller.
WD_COMMAND_REELECT_LEADER was brought in when pcp_stop_pgpool was
implemented in commit 87e6e11c1931555e90a1cc65cc3ee79759ebaaab. I am
not the author of the commit and don't know what the intention for
this.

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

I am not a watchdog expert but 1-6 look reasonable design to me.

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

I am fine with pcp_switch_leader.

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

+1 for synchronous. It will make the operator's life easier.

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

I think falling back to a normal election is ok since in the situation
it would be hard for the former primary to relcaim the leadership.

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

Yes, I think so.

> 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. I am looking forward to seeing your patch.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp