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

[Tatsuo Ishii]

So clients directly access and modify PostgreSQL servers, not through
Pgpool-II. Am I correct? But also clients send SELECTs via Pgpool-II
(because you are referring connection pooling)?

> Hello,
> 
> Well, if it were up to me, I  would use the streaming replication mode as it seems more reliable and with less shortcomings. But the whole system is for a customer who explicitly wants both DB nodes to run in read/write mode (pgpool is there to solve the need for high availability and everything else, like load balancing or connection pooling is just a bonus).
> 
> Also - forgot to list the versions of pgPool a PostgreSQl we are using on this:
> 
> pgPool - 4.1.0
> PostgreSQL - 11.5 (planning on a 11.6 patch, but I don't think that will change anything for the online recovery scripts).
> 
> Thank you for your time.
> 
> Vladimír Láznička
> 
> -----Original Message-----
> From: Tatsuo Ishii <ishii at sraoss.co.jp> 
> Sent: Thursday, February 6, 2020 9:17 AM
> To: Láznička Vladimír <Vladimir.Laznicka at cca.cz>
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 6845] Online recovery with replication mode
> 
> 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.