[pgpool-general: 7478] Re: Promote specific backend to primary in streaming replication

[Nathan Ward]

On Mon, Apr 5, 2021 at 6:13 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Ah - I had not thought of this option. However, doesn't failover_command
> > get told what the new primary will be, rather than being able to decide
> > this itself (%m %H %r %R)?
>
> No. Actually failover_command always decides what the new primary will
> be.
>
> "New main node" means the live node (not in down status) which has the
> youngest node id. Usually (and our sample script) just chooses the new
> main node as the candidate of new primary because it's the easiest
> way. (otherwise the script must check whether new primary candidate is
> actually up or not).
>

Oh! I see! So, does this mean that pgpool checks pg_is_in_recovery() to
find out which node is primary after failover_command has run?


> >> With this change you can do:
> >>
> >> 1) explicitly specify the new primary node
> >>
> >> 2) run pcp_detach_node against the current primary and the current
> >>    primary nodes goes into down status
> >>
> >> 3) failover_command will run to make the specified standby node to be
> >>    new primary
> >>
> >> 4) follow_primary_command will run on rest of standbys and the old
> >>    primary to follow the new primary
> >
> >
> > I think this would work yes - which is very close to what I was
> suggesting
> > with a flag on pcp_promote_node. If this requires 2 commands
> > (pcp_set_next_primary or something, then pcp_detach_node) I would wrap
> them
> > in a single script which runs both in order to keep things simple and
> fast
> > for our operations team.
>
> Wrapping pcp_set_next_primary or something, and pcp_detach_node looks
> nice idea because it's flexible. Even you could look for a node which
> has the least replication delay and let the node be the next primary.
>

OK - this sounds great. I can have a stab at getting something like that
written.

So, in terms of implementation this sounds like:
1) New pcp command to set a next primary backend ID. I'm not sure how this
would be stored, perhaps update the parsed configuration state (is this
pool_config), so it could also be in the configuration file? Or, it could
update a new flag on bkinfo?
2) Modify get_next_main_node to include a check for the above, and check
that it is a valid backend and not the current (failed/detached) primary
backend.

Is modifying get_next_main_node() appropriate here? My thinking is that
doing this means we can continue to use the existing scripts, which use %m
and %H and so on.
Perhaps this is best implemented as a backend "primary-ship" priority -
i.e. set a priority for each backend (via config and pcp) to become the
primary, as a uint8 on bkinfo, then in get_next_main_node sort first by
priority then by node id.

--
Nathan Ward
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210405/20c184f9/attachment.htm>