On 2020/06/18 1:29, Robert Haas wrote:
On Wed, Jun 17, 2020 at 11:45 AM Jehan-Guillaume de Rorthais
<j...@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
