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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Feb 11 20:00:13 JST 2020


> Ok, so I should change the query to something like this (against primary)...
> 
> SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS wal_diff FROM pg_stat_replication WHERE usename='$REPL_USER';
> 
> ... and leave some margin in bytes in the 1st stage, so it can actually get to the 2nd stage, where I will perform this test again, but this time it will have to reach 0 bytes of difference (as no changes should be occurring on the primary at this point).

Looks good to me.

> I will test my scripts during the next two weeks and let you know the results or if I encounter any problems I will need some help with.
> 
> Thanks for your help.

You are welcome.

> Vladimír Láznička
> 
> -----Original Message-----
> From: Tatsuo Ishii <ishii at sraoss.co.jp> 
> Sent: Tuesday, February 11, 2020 1:22 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
> 
>> Hello,
>> 
>> So, if I stick with my original idea of using streaming replication, I basically need to ensure that all WAL changes from the source DB are replayed on the target DB before I start the 2nd stage of online recovery... I slightly modified my original script for the 1st stage so that after starting target DB in HOT-STANDBY mode it checks whether it replayed all the changes by using this query:
>> 
>> SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() 
>> THEN 0 ELSE 1 END AS log_delay;
> 
>> I am sending the modified scripts for review.
> 
> - I think you need to compare the LSN on the standby and the primary
>   to know if the standby catches up the primary.
> 
> - You need to pause in the while loop.
> 
> - It is possible that standby never catches up the primary if updating
>   to the database keeps going on. Probably you need to have some
>   "margin" when comparing those two LSN to allow that the standby is a
>   little bit behind the primary. In this case you need to keep on
>   streaming replication in the 2nd stage so that the standby catches
>   up the primary.
> 
>> I also have a follow-up questions - can I use the online recovery (and these scripts) in the situation, when I want to update the minor version of PostgreSQL server and basically perform some sort of rolling upgrade? Do I need to change anything in the scripts?
> 
> If "rolling upgrade" means 1) stop postmaster 2) update PostgreSQL binary 3) on line recovery the database, then I think you can.
> 
>> If I want to utilize the online recovery to update the major version of PostgreSQL server, I need to rewrite the scripts so that they use pg_dump/pg_dumpall instead of pg_basebackup and logical replication instead of physical replication, but the steps should remain largely the same (with more complicated setup involved due to the nature of logical replication), correct? 
> 
> Yes, I think correct.
> 
>> I imagine a solution, where I have two sets of scripts - first for general disaster recovery scenario and upgrading between minor versions and second for upgrading between major version, and I would change the script names in pgpool.conf to what I need at the moment. Would you recommend using the online recovery for upgrade purposes, if I want to, again, ensure that the downtime will be near zero?
> 
> You need to be careful in some cases: PostgreSQL sometimes changes API, including function names which might be necessary in your script. Other than that, I agree with you.
> 
>> Thank you for your advice,
>> Vladimír Láznička
>> 
>> 
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii <ishii at sraoss.co.jp>
>> Sent: Saturday, February 8, 2020 3:01 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
>> 
>>> Well, the reason for the streaming replication was that it finishes as much changes as possible before the 2nd stage starts and clients are disconnected - the goal here is to finish the online recovery as fast as possible after the 2nd stage starts. If this takes just a few seconds, it should be fine - application will probably recover from that without end users knowing there was a short outage on the backend. However if the synchronization between DB nodes after the 1st stage takes a minute or more, then we will have a problem. The system is supposed to be very busy 24/7.
>> 
>> Sounds like a good idea. With pitr all changes happen in the first stage produce WAL and are kept till the 2nd stage. With streaming replication the changes also produce WAL as well but they will be consumed by the standby server and remaining WAL before the 2nd stage will be less than pitr.
>> 
>>> But, if you say I can do that just with PITR recovery, then that would be preferable as it is less complex. So if I go over individual steps, I should alter my scripts to do something like this:
>> 
>> Yeah, but I now think your idea to use streaming replication is worth 
>> to try. Anyway,
>> 
>>> 1st_stage:
>>> 
>>> 1) Create a replication slot on the SOURCE (active) database.
>>> 2) Clean the PGDATA directory of the TAGRET (dead) database.
>>> 3) Perform a restore of the TARGET database via pg_basebackup (with the same command as I have in the script now).
>>> 4) In PGDATA directory on the TARGET database create the recovery.conf file as before, but now omitting the clause "standy_mode = on".
>>> 
>>> 2nd_stage:
>>> 
>>> 1) Clients are disconnected (with client_idle_limit_in_recovery = -1).
>>> 2) Force WAL logs on SOURCE database to rotate as before? I am not sure if this now needed...
>> 
>> I do not remember the exact reason why we need to do that.
>> 
>>> Remote_start:
>>> 
>>> 1) Start postmaster on the TARGET database. Basically the same pg_ctl command I have now in the script for the 1st stage. Do I have a guarantee at this point that the TARGET database will have all the changes from the SOURCE database and it does not have to replay those changes over the course of a potentially unacceptable time?
>>> 2) Drop the replication slot on the SOURCE database.
>>> 3) TARGET database node gets attached to the pgpool service.
>>> 3) Clients are now able to connect again.
>>> 
>>> Are these steps correct or do I need to change/add something to satisfy the goal for high availability I described so far?
>> 
>> Yes, they seem to be correct to me.
>> 
>>> Vladimír Láznička
>>> 
>>> -----Original Message-----
>>> From: Tatsuo Ishii <ishii at sraoss.co.jp>
>>> Sent: Thursday, February 6, 2020 11:34 PM
>>> 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
>>> 
>>> Ok, now going back to the original question...
>>> 
>>> First of all in your script, you seem to use streaming replication. I think using point in time recovery (pitr) instead is enough for your purpose. Also by using pitr you don't need to promote the standby server in step 3.
>>> 
>>>>>>   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...
>>> 
>>> By default configuration pgpool just waits for them to disconnect on their own. However if you set client_idle_limit_in_recovery to non 0, they are foced to disconnect.
>>> 
>>>>>>   2.  From the start of the 2nd stage, does pgpool prevent the clients from connecting until the online recovery finishes?
>>> 
>>> Yes.
>>> 
>>>>>>   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...)?
>>> 
>>> You can use pg_current_wal_lsn() on primary and
>>> pg_last_wal_replay_lsn() on standby to compare LSN so that you could check if the standby catches up the primary. However as I said earlier, with pitr you don't need to do that.
>>> 
>>>>>>   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?
>>> 
>>> Yes.
>>> 
>>>> No, both SELECT and DML commands will be send through pgpool as far as our applications are concerned. As far as I know, customer doesn't want any failover happening, when one of the DB nodes goes offline (whether it will affect any functionality from pgpool-side of things is another thing, I know that pgpool basically resets all sessions when one of the nodes goes down - applications will have to cope with that situation). Not sure if he plans to do any modifications to individual databases outside of pgpool, but he was informed that he can break the synchronization from pgpool if he is not careful with that approach.
>>>> 
>>>> -----Original Message-----
>>>> From: Tatsuo Ishii <ishii at sraoss.co.jp>
>>>> Sent: Thursday, February 6, 2020 9:44 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
>>>> 
>>>> 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.


More information about the pgpool-general mailing list