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

[Nathan Ward]

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

> > Hi all,
> >
> > I'm busy upgrading to postgres 13 and pgpool 4.1.4, and as part of that
> > reworking some of our scripts and improving some of our operational
> > procedures.
> >
> > I'd like to have a way to promote a specific backend to primary, and have
> > the old primary use pg_rewind, then connect to the new primary.
> >
> > I'm using scripts largely based on the samples, though without
> > pg_basebackup as a fallback to pg_rewind - our thinking is if pg_rewind
> > fails, we want to leave it in the current state and investigate, then
> > restore manually.
> >
> > I can run pcp_detach_node to almost achieve this controlled primary
> > failover, but it doesn't let me specify which primary to fail over to.
> > pcp_promote_node explicitly says it's only about internal pgpool state,
> and
> > it runs follow_master_command, but not failover_command.
> >
> > Is there a way to achieve this in pgpool at the moment?
>
> I think what you can do for that today is:
>
> 1) create a file telling which node to be promoted when currect primary
> goes down.
>
> 2) failover_command reads the file and promotes the specified node.
>
> Of course this is kind of a quick and dirty way. I think we would want
> to have better way.
>

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)? Or, are you thinking that failover_command can
run pcp_promote_node and override whatever failover_command is told to do?
Can you run pcp_promote_node etc. while a backend failover is in progress?


> > If not, could we
> > put together a new feature / command to achieve this? (Happy to
> contribute
> > code, if this is something that would be useful).
> > Thinking:
> > 1) Detach old primary
> > 2) Run failover_command on the new primary
> > 3) Run follow_master_command on the standbys (including the old primary,
> > which gets reattached by the script)
> >
> > Perhaps this is an extra flag on pcp_promote_node which causes
> > failover_command to run?
>
> I think an ability to tell failover_command to promote specific
> standby is more flexible because this way, both pcp_detach_node and
> failover (caused by node down) can use the information.  For this
> purpose we could have a new pcp command or new pgpool.conf
> parameter.
>
> 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.

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