Re: [patch] demote

On 2020/06/18 1:29, Robert Haas wrote:
> On Wed, Jun 17, 2020 at 11:45 AM Jehan-Guillaume de Rorthais
> <jgdr@dalibo.com> wrote:
>> As Amul sent a patch about "ALTER SYSTEM READ ONLY"[1], with similar futur
>> objectives than mine, I decided to share the humble patch I am playing with to
>> step down an instance from primary to standby status.
> 
> Cool! This was vaguely on my hit list, but neither I nor any of my
> colleagues had gotten the time and energy to have a go at it.
> 
>> Main difference with Amul's patch is that all backends must be disconnected to
>> process with the demote. Either we wait for them to disconnect (smart) or we
>> kill them (fast). This makes life much easier from the code point of view, but
>> much more naive as well. Eg. calling "SELECT pg_demote('fast')" as an admin
>> would kill the session, with no options to wait for the action to finish, as we
>> do with pg_promote(). Keeping read only session around could probably be
>> achieved using global barrier as Amul did, but without all the complexity
>> related to WAL writes prohibition.
>>
>> There's still some questions in the current patch. As I wrote, it's an humble
>> patch, a proof of concept, a bit naive.
>>
>> Does it worth discussing it and improving it further or do I miss something
>> obvious in this design that leads to a dead end?
> 
> I haven't looked at your code, but I think we should view the two
> efforts as complementing each other, not competing. With both patches
> in play, a clean switchover would look like this:
> 
> - first use ALTER SYSTEM READ ONLY (or whatever we decide to call it)
> to make the primary read only, killing off write transactions
> - next use pg_ctl promote to promote the standby
> - finally use pg_ctl demote (or whatever we decide to call it) to turn
> the read-only primary into a standby of the new primary

ISTM that a clean switchover is possible without "ALTER SYSTEM READ ONLY".
What about the following procedure?

1. Demote the primary to a standby. Then this demoted standby is read-only.
2. The orignal standby automatically establishes the cascading replication
    connection with the demoted standby.
3. Wait for all the WAL records available in the demoted standby to be streamed
    to the orignal standby.
4. Promote the original standby to new primary.
5. Change primary_conninfo in the demoted standby so that it establishes
    the replication connection with new primary.

So it seems enough to implement "demote" feature for a clean switchover.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION