Subject: Re: [patch] demote
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <20200617174451.222078b4@firost>
 <CA+TgmoaZ28PXLC1j3SBm=NCNOnS0ieThZJ7vKVp4yfdSvBKnZA@mail.gmail.com>
 <2eb2fd8f-c9a0-ac31-66dc-2c56dd048540@oss.nttdata.com>
 <CA+TgmoagXhAERpYG=Kc1keEtg_2xj8ySN+A4Yv1EMYTm3w5Bdw@mail.gmail.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <e0c986b5-921f-4dff-97c0-eb298aa9196c@oss.nttdata.com>
Date: Fri, 19 Jun 2020 01:55:35 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+TgmoagXhAERpYG=Kc1keEtg_2xj8ySN+A4Yv1EMYTm3w5Bdw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 2020/06/19 0:22, Robert Haas wrote:
> On Thu, Jun 18, 2020 at 8:41 AM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>> 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.
> 
> There's something to that idea. I think it somewhat depends on how
> invasive the various operations are. For example, I'm not really sure
> how feasible it is to demote without a full server restart that kicks
> out all sessions. If that is required, it's a significant disadvantage
> compared to ASRO.

Even with ASRO, the server restart is necessary and RO sessions are
kicked out when demoting RO primary to a standby, i.e., during a clean
switchover?

Regards,

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