[pgpool-general: 6846] Re: Online recovery with replication mode

[Tatsuo Ishii]

Hi,

I will look into this.  Just out of curiosity, why are you going to
use native replication mode in Pgpool-II, rather than streaming
replication mode? Nowadays most of users of Pgpool-II use the
streaming replication mode.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: Láznička Vladimír <Vladimir.Laznicka at cca.cz>
Subject: [pgpool-general: 6845] Online recovery with replication mode
Date: Wed, 5 Feb 2020 13:49:45 +0000
Message-ID: <735cde689d1b477086179cb83e0011cc at mailer-2.cca.cz>

> Hello,
> 
> I am trying to figure out the way to implement the scripts needed for the online recovery feature, while running pgpool in replication mode (as opposed to master/slave streaming mode). I have found several examples of those scripts for streaming replication mode (such as this one - https://www.pgpool.net/docs/latest/en/html/example-cluster.html#EXAMPLE-CLUSTER-PGPOOL-CONFIG-ONLINE-RECOVERY), but have yet to come across some example for the replication mode.
> 
> Now - from what I gather, replication mode is the only mode, where the 2nd stage of online recovery is used, but from what I saw in the sample script distributed with YUM package, only thing I am supposed to do here is to flush sequence values to WAL and then switch the WAL file. Based on that, here is what I think each stage should do in replication mode:
> 
> 
>   1.  1st stage - restore the PGDATA data directory (pg_basebackup) and start streaming changes (via a replication slot) from the source DB to the target DB.
>   2.  2nd stage - switch WAL file on the source DB, so any pending changes get streamed to the target DB.
>   3.  Remote start - promote the target DB from standby to read/write mode (and drop the replication slot on the source DB).
>   4.  Target DB is attached to pgpool.
> 
> I have some question however as to what will happen in between these stages:
> 
> 
>   1.  After the 1st stage, are all clients disconnected automatically or pgpool just waits for them to disconnect on their own - so in order for the online recovery to proceed, I need to force the disconnection from their side? Documentation seems to imply the second option, but I would rather be sure about that...
>   2.  From the start of the 2nd stage, does pgpool prevent the clients from connecting until the online recovery finishes?
>   3.  Is there some ideal way to check the status of the replication and not let the online recovery continue, until the target DB fully caught up to the source DB (probably during the 2nd stage...)?
>   4.  After performing the remote start script (which needs to called pgpool_remote_start and be placed in the same PGDATA directory as the scripts for other stages) and the automatic node attach, does pgpool immediately allow clients to connect again?
> 
> As an attachment I am sending my test versions of those 3 scripts - I have not used them yet, I only did their steps manually to see if they can recover the database that has fallen behind. Can you please review them if they perform correct steps in order for any outage to be absolutely minimal? Let me know if you need some additional explanation about the scripts...
> 
> Thank you for any advice.
> 
> With regards,
> Vladimír Láznička, CCA Group a.s.