[pgpool-general: 7535] Re: Strange behavior on switchover with detach_false_primary enabled

[Emond Papegaaij]

On Fri, Apr 30, 2021 at 8:36 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > Yeah, we need to protect follow_child from detach_false_primary
> > (actually executed in separate process: pgpool_worker). For this
> > purpose I think we could use another shared memory variable
> > Req_info->switching). The variable is set to true while failover
> > procedure is running. detach_false_primary will not be executed if
> > Req_info->switching is true. I will implement this in the next patch
> > set.
>
> Attached is v2 patch for this. I confirmed that in 3 PostgreSQL nodes
> system (no watchdog), detach_false_primary works with following
> scenario:

I'll have a look at the patch later today.

> > Looking at logs again, I'm starting to think the original problem may
> >> be more complicated. In the attached logging I do not see the node
> >> being marked invalid. Instead, I see this:
> [snip]
> >> This makes me think the sequence of events involves other pgpool nodes:
> >> * The instruction to detach primary node 0 is performed on node 0 and
> >> forwarded to node 1.
> >> * pgpool node 1 starts the failover, promoting backend node 1
> >> * pgpool node 2 wrongfully detects a false primary and requests to
> >> detach backend node 1
> >> * pgpool node 1 accepts this request and starts to detach node 1,
> >> while it is in the middle of instructing node 0 and 2 to follow this
> >> node as primary
> >
> > Thanks for the info. I will look into this.
>
> I started to think that detach_false_primary should not active on
> other than leader watchdog node because standby watchdog node can be
> interrupted by other watchdog node and causes unexpected failover. I
> will investigate more.

I was in the middle of typing this, it seems we are in agreement: I'm
wondering why a node that is not the pgpool leader makes this decision
at all. Shouldn't the view of the database cluster be the same from
all pgpool nodes? I'm not that much at home in how the watchdog makes
its decisions, but to me it seems only the leader can make autonomous
decisions. A node may request the cluster status to be reviewed, but
for an automated detach, I would at least expect a quorum is needed.

> I agree that pgpool should not change the primary node while follow
> primary command is executing. That's the aim of the proposed patch.
>
> On the other hand failover command could change the primary node in
> the middle of failover script because it may need to elect new primary
> node. We should allow this. Otherwise no new primary node will be
> chosen.

Yes, that's what I meant.

Best regards,
Emond