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

[Tatsuo Ishii]

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

> 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

What do you think?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp