Thread

1. Replication Testing- How to introduce a Lag

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-20T06:33:32Z

   Hello,
   
   
      I have set up streaming replication in a our test environment and it is working OK .
   
   
   
   
     1.  Is there a way to  force a lag between the source and the stand-by in an environment where there is NO load?
   
   
     1.  During the set up process as I was thrashing around from server to server I received this message in the stand-by if I make the standby follow a new server B after it had been following a server A.  what seems to work is, additionally clearing out the archive logs dir of the standby server along with the data dir, before running the pg_basebackup from the standby.  I am extremely new to postgres and  I want to make sure that what I am doing is not a dangerous practice.
   
   
   
   FATAL: requested timeline 2 is not a child of this server's history
   
   LG
   
   Ram
   
   
   Freundliche Grüße
   
   i. A. Ramachandran Subramanian
   Zentralbereich Informationstechnologie
   
   Alte Leipziger Lebensversicherung a.G.
   
   
   Hallesche Krankenversicherung a.G.
   
   
   
   
   
   Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
   Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
   
   
   
   
   
    
   Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
   Wiltrud Pekarek, Udo Wilcsek
   Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
   Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
    
   
   
   
    
   Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
   
   
   
   
   
   ______________________
   
   ALH Gruppe
   Alte Leipziger-Platz 1, 61440 Oberursel
   Tel.: +49 (6171) 66-4882
   Fax: +49 (6171) 66-800-4882
   E-Mail: ramachandran.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   

2. Re: Replication Testing- How to introduce a Lag

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-20T07:40:43Z

   On Fri, 2026-03-20 at 06:33 +0000, Subramanian,Ramachandran wrote:
   > I have set up streaming replication in a our test environment and it is working OK .
   > 
   > 1. Is there a way to  force a lag between the source and the stand-by in an environment where there is NO load?
   
   Yes, set "recovery_min_apply_delay" on the standby.
   
   > 2. During the set up process as I was thrashing around from server to server I received this message
   >    in the stand-by if I make the standby follow a new server B after it had been following a server A.
   >    what seems to work is, additionally clearing out the archive logs dir of the standby server along
   >    with the data dir, before running the pg_basebackup from the standby.  I am extremely new to
   >    postgres and  I want to make sure that what I am doing is not a dangerous practice.
   > 
   > FATAL: requested timeline 2 is not a child of this server's history
   
   You have "recovery_timeline = 'latest'" on the standby, right?
   
   Then the standby that gives you that error probably followed the original primary to
   a point past where the new primary forked off timeline 1.
   
   You need to shutdown the standby that gives you the error message and run "pg_rewind"
   to reposition it to follow the new primary.  Prerequisite: "wal_log_hints = on" or
   data checksums enabled.
   Otherwise you need to rebuild the standby with a new pg_basebackup.
   
   Yours,
   Laurenz Albe
   
   
   
   

3. AW: Replication Testing- How to introduce a Lag

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-20T10:47:13Z

   Hello 
   
    Thank you for your inputs.  I did a set recovery_target_action=pause on the standby and stopped and restarted it.. now I see that the delay
   
   But Obviously  my understanding of the Last replayed WAL at the target is wrong.. how can the last replayed be more than the last received?
   
   
   LG
   
   ram
   
   
   
   LSN at Source for  AUFGABEN-VERWAL  is  0/48000148
    LSN Received at Stand-By for  AUFGABEN-VERWAL  is  0/48000000
    LSN Replayed at Stand-By for  AUFGABEN-VERWAL  is  0/48000148
    Current Lag between SOURCE and STAND-BY for  AUFGABEN-VERWAL  is  328
   
   
   
     # Get the current(newest)  Log Sequence number from the remote SOURCE
      SOURCE_LSN=$(psql -h $SOURCE_HOST -p $SOURCE_PORT -t  -c "select * from pg_current_wal_lsn()" | sed -e 's/ //')
      echo ' LSN at Source for ' $DIR_NAME ' is ' $SOURCE_LSN
      
   
      # Get the current(newest)  Log Sequence number from the STAND-BY
      STANDBYRC_LSN=$(psql -p $PORT_NO -t  -c "select  pg_last_wal_receive_lsn()" | sed -e 's/ //')
      STANDBYRP_LSN=$(psql -p $PORT_NO -t  -c "select  pg_last_wal_replay_lsn()" | sed -e 's/ //')
      echo ' LSN Received at Stand-By for ' $DIR_NAME ' is ' $STANDBYRC_LSN
      echo ' LSN Replayed at Stand-By for ' $DIR_NAME ' is ' $STANDBYRP_LSN
      
   
      # Calculate the Lag between SOURCE and STAND-BY
      CURRENT_LAG=$(psql -p $PORT_NO -t  -c "select  pg_wal_lsn_diff('$SOURCE_LSN','$STANDBYRP_LSN')")
      echo ' Current Lag between SOURCE and STAND-BY for ' $DIR_NAME ' is ' $CURRENT_LAG
      
   
   
   LSN at Source for  AUFGABEN-VERWAL  is  0/48000148
    LSN Received at Stand-By for  AUFGABEN-VERWAL  is  0/48000000
    LSN Replayed at Stand-By for  AUFGABEN-VERWAL  is  0/48000148
    Current Lag between SOURCE and STAND-BY for  AUFGABEN-VERWAL  is  328
   
   
   
   
   
   Freundliche Grüße
   
   i. A. Ramachandran Subramanian
   Zentralbereich Informationstechnologie
   
   Alte Leipziger Lebensversicherung a.G.
   
   
   Hallesche Krankenversicherung a.G.
   
   
   
   
   
   
   
   Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
   Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
   
   
   
   
   
    
   Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
   Wiltrud Pekarek, Udo Wilcsek
   Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
   Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
    
   
   
   
    
   Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
   
   
   
   
   
   ______________________
   
   ALH Gruppe
   Alte Leipziger-Platz 1, 61440 Oberursel
   Tel.: +49 (6171) 66-4882
   Fax: +49 (6171) 66-800-4882
   E-Mail: ramachandran.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   
   
   -----Ursprüngliche Nachricht-----
   Von: Laurenz Albe <laurenz.albe@cybertec.at> 
   Gesendet: Freitag, 20. März 2026 08:41
   An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org
   Betreff: Re: Replication Testing- How to introduce a Lag
   
   On Fri, 2026-03-20 at 06:33 +0000, Subramanian,Ramachandran wrote:
   > I have set up streaming replication in a our test environment and it is working OK .
   > 
   > 1. Is there a way to  force a lag between the source and the stand-by in an environment where there is NO load?
   
   Yes, set "recovery_min_apply_delay" on the standby.
   
   > 2. During the set up process as I was thrashing around from server to server I received this message
   >    in the stand-by if I make the standby follow a new server B after it had been following a server A.
   >    what seems to work is, additionally clearing out the archive logs dir of the standby server along
   >    with the data dir, before running the pg_basebackup from the standby.  I am extremely new to
   >    postgres and  I want to make sure that what I am doing is not a dangerous practice.
   > 
   > FATAL: requested timeline 2 is not a child of this server's history
   
   You have "recovery_timeline = 'latest'" on the standby, right?
   
   Then the standby that gives you that error probably followed the original primary to a point past where the new primary forked off timeline 1.
   
   You need to shutdown the standby that gives you the error message and run "pg_rewind"
   to reposition it to follow the new primary.  Prerequisite: "wal_log_hints = on" or data checksums enabled.
   Otherwise you need to rebuild the standby with a new pg_basebackup.
   
   Yours,
   Laurenz Albe
   

4. Re: AW: Replication Testing- How to introduce a Lag

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-20T13:38:31Z

   On Fri, 2026-03-20 at 10:47 +0000, Subramanian,Ramachandran wrote:
   > how can the last replayed be more than the last received?
   
   It shouldn't be - but I don't understand your script, in particular
   which query is issused on which server.
   
   Look at "pg_stat_replication" on the primary server.
   
   Yours,
   Laurenz Albe
   
   
   
   

5. AW: AW: Replication Testing- How to introduce a Lag

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-21T07:49:32Z

   > how can the last replayed be more than the last received?
   
   It shouldn't be - but I don't understand your script, in particular which query is issused on which server.
   
   [Subramanian,Ramachandran IT-md-db] 
   100% sure that I have screwed up some variables in my script
   
   Look at "pg_stat_replication" on the primary server.
   
   [Subramanian,Ramachandran IT-md-db] 
   
   Thank you so much!!  So I do not have to go across multiple tables to collect the information. 
   
   It is all there in one table pg_stat_replication at the Primary server and we can look at it from A) Primary and also from B) Stand-by !! 
   
   If I just look at the write_lag columns, it would give me a good idea of the situation --  Is my understanding correct ?
   
   LG
   
   Ram
   
   
   Details from the Source (Remote Primary)
   ----------------------------------------------------------
   
   pid   | usesysid |  usename   | application_name | client_addr | client_hostname | client_port |         backend_start
     | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn |    write_lag    |    flush_lag    |   replay
   _lag    | sync_priority | sync_state |          reply_time
   --------+----------+------------+------------------+-------------+-----------------+-------------+-----------------------------
   --+--------------+-----------+------------+------------+------------+------------+-----------------+-----------------+---------
   --------+---------------+------------+-------------------------------
    188833 |    25200 | replicator | walreceiver      | 10.10.10.10  |                 |       43330 | 2026-03-21 08:11:37.010895+0
   1 |              | streaming | 0/49072590 | 0/49072590 | 0/49072590 | 0/49042F98 | 00:00:00.000463 | 00:00:00.001693 | 00:00:27
   .042262 |             0 | async      | 2026-03-21 08:12:19.239535+01
   (1 row)
   
   The same details from the Local Stand-by Server -- They match !!!!
   ----------------------------------------------------------------------------------
   
   pg_last_wal_receive_lsn
   -------------------------
    0/49072590
   (1 row)
   
    pg_last_wal_replay_lsn
   ------------------------
    0/49042F98
   (1 row)
   
   
   Freundliche Grüße
   
   i. A. Ramachandran Subramanian
   Zentralbereich Informationstechnologie
   
   Alte Leipziger Lebensversicherung a.G.
   
   
   Hallesche Krankenversicherung a.G.
   
   
   
   
   
   Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
   Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
   
   
   
   
   
    
   Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
   Wiltrud Pekarek, Udo Wilcsek
   Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
   Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
    
   
   
   
    
   Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
   
   
   
   
   
   ______________________
   
   ALH Gruppe
   Alte Leipziger-Platz 1, 61440 Oberursel
   Tel.: +49 (6171) 66-4882
   Fax: +49 (6171) 66-800-4882
   E-Mail: ramachandran.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   

6. Re: AW: AW: Replication Testing- How to introduce a Lag

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-21T08:43:10Z

   On Sat, 2026-03-21 at 07:49 +0000, Subramanian,Ramachandran wrote:
   > > Look at "pg_stat_replication" on the primary server.
   > 
   > If I just look at the write_lag columns, it would give me a good idea of the situation --  Is my understanding correct ?
   
   Yes, but if no WAL gets generated and replicated for a while, the columns will
   become NULL, because then delay measured in time makes no sense.
   
   Therefore, I prefer to use "replay_lsn" and friends and measure the lag in bytes.
   
   Yours,
   Laurenz Albe
   
   
   
   

7. AW: AW: AW: Replication Testing- How to introduce a Lag

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-23T05:55:34Z

   
   
   Yes, but if no WAL gets generated and replicated for a while, the columns will become NULL, because then delay measured in time makes no sense.
   
   Therefore, I prefer to use "replay_lsn" and friends and measure the lag in bytes.
   
   Yours,
   Laurenz Albe
   [Subramanian,Ramachandran IT-md-db] 
   
   Thank you so much for your guidance,  Today morning I noticed the columns being null .  
   
   | streaming | 0/491179E8 | 0/491179E8 | 0/491179E8 | 0/491179E8 |           |           |            |
   
   Does it make sense to use the below SQL as a primary health check ?
   
   [postgres@Source_server~]$ psql -p 5432 -c " select  pg_wal_lsn_diff(sent_lsn,replay_lsn) from pg_stat_replication"
    pg_wal_lsn_diff
   -----------------
                  0
   (1 row)
   
   LG
   
   Ram 
   
   
   Freundliche Grüße
   
   i. A. Ramachandran Subramanian
   Zentralbereich Informationstechnologie
   
   Alte Leipziger Lebensversicherung a.G.
   
   
   Hallesche Krankenversicherung a.G.
   
   
   
   
   
   Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
   Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
   
   
   
   
   
    
   Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
   Wiltrud Pekarek, Udo Wilcsek
   Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
   Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
    
   
   
   
    
   Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
   
   
   
   
   
   ______________________
   
   ALH Gruppe
   Alte Leipziger-Platz 1, 61440 Oberursel
   Tel.: +49 (6171) 66-4882
   Fax: +49 (6171) 66-800-4882
   E-Mail: ramachandran.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   

8. Re: AW: AW: AW: Replication Testing- How to introduce a Lag

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-23T14:33:29Z

   On Mon, 2026-03-23 at 05:55 +0000, Subramanian,Ramachandran wrote:
   > Thank you so much for your guidance,  Today morning I noticed the columns being null .  
   > 
   > > streaming | 0/491179E8 | 0/491179E8 | 0/491179E8 | 0/491179E8 |           |           |            |
   > 
   > Does it make sense to use the below SQL as a primary health check ?
   > 
   > [postgres@Source_server~]$ psql -p 5432 -c " select  pg_wal_lsn_diff(sent_lsn,replay_lsn) from pg_stat_replication"
   >  pg_wal_lsn_diff
   > -----------------
   >                0
   > (1 row)
   
   No; you want to measure the lag between the primary's current WAL position and
   what was already replayed on the standby:
   
     SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
     FROM pg_stat_replication;
   
   Yours,
   Laurenz Albe
   
   
   
   

9. AW: AW: AW: AW: Replication Testing- How to introduce a Lag

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-23T16:36:22Z

   
   No; you want to measure the lag between the primary's current WAL position and what was already replayed on the standby:
   
     SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
     FROM pg_stat_replication;
   
   [Subramanian,Ramachandran IT-md-db] 
   
   Thank you yet again for your guidance...  
   
   I noticed that  RBAs are not incremented one for one . i.e  1 row inserted does not mean RBA=RBA+1 . 1 row updated  does not mean RBA=RBA+1 
   
   I have ALTER SYSTEM SET recovery_min_apply_delay=300000 ; ( on the stand by side ) 
   
   On the Source side 
   A simple create table results in a RBA difference of 108328 
   
   A simple insert of 1 row results in a RBA difference of 296   sometimes 96 
   
   Is there a way to estimate roughly the amount of data that remains to be transfered ?  
   
   
   LG
   
   Ram 
   
   
   Freundliche Grüße
   
   i. A. Ramachandran Subramanian
   Zentralbereich Informationstechnologie
   
   Alte Leipziger Lebensversicherung a.G.
   
   
   Hallesche Krankenversicherung a.G.
   
   
   
   
   
   Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
   Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
   
   
   
   
   
    
   Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
   Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
   Wiltrud Pekarek, Udo Wilcsek
   Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
   Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
    
   
   
   
    
   Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
   
   
   
   
   
   ______________________
   
   ALH Gruppe
   Alte Leipziger-Platz 1, 61440 Oberursel
   Tel.: +49 (6171) 66-4882
   Fax: +49 (6171) 66-800-4882
   E-Mail: ramachandran.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   

10. Re: AW: AW: AW: AW: Replication Testing- How to introduce a Lag

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-23T21:59:54Z

    On Mon, 2026-03-23 at 16:36 +0000, Subramanian,Ramachandran wrote:
    > I noticed that  RBAs are not incremented one for one . i.e  1 row inserted does not mean RBA=RBA+1 . 1 row updated  does not mean RBA=RBA+1 
    > 
    > I have ALTER SYSTEM SET recovery_min_apply_delay=300000 ; ( on the stand by side ) 
    > 
    > On the Source side 
    > A simple create table results in a RBA difference of 108328 
    > 
    > A simple insert of 1 row results in a RBA difference of 296   sometimes 96 
    > 
    > Is there a way to estimate roughly the amount of data that remains to be transfered ? 
    
    I don't know what an RBA is...
    
    If you are using recovery_min_apply_delay, don't measure the replication lag
    with regard to the replay_lsn, because replay is deliberately delayed.
    Instead, measure the difference to flush_lsn, the WAL position successfully
    transferred to the standby and persisted there.
    
    Yours,
    Laurenz Albe
    
    
    
    

11. AW: AW: AW: AW: AW: Replication Testing- How to introduce a Lag

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-24T06:14:23Z

    By force of habit I used a mainframe term. I am sorry . RBA = Relative Byte Address.  Used as Log Sequence Number. 
    
    I noticed that if I insert one row in a table at the source, the difference in LSNs is not 1 .  ( with a delibrately introduced delay on the apply side ), 
    
    It is sometimes 96, sometimes 296 ( for the same table two inserts  ) . 
    
    psql -h $SOURCE_HOST -p $SOURCE_PORT  -c "select  pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication"
    
    
    Is there a method to calculate the APPROXIMATE amount of data in ( Bytes ) that are yet to be transfered from Source to Standby ?
    
    
    LG
    
    Ram 
    [Subramanian,Ramachandran IT-md-db] 
    
    On Mon, 2026-03-23 at 16:36 +0000, Subramanian,Ramachandran wrote:
    > I noticed that  RBAs are not incremented one for one . i.e  1 row 
    > inserted does not mean RBA=RBA+1 . 1 row updated  does not mean 
    > RBA=RBA+1
    > 
    > I have ALTER SYSTEM SET recovery_min_apply_delay=300000 ; ( on the 
    > stand by side )
    > 
    > On the Source side
    > A simple create table results in a RBA difference of 108328
    > 
    > A simple insert of 1 row results in a RBA difference of 296   
    > sometimes 96
    > 
    > Is there a way to estimate roughly the amount of data that remains to 
    > be transfered ?
    
    I don't know what an RBA is...
    
    If you are using recovery_min_apply_delay, don't measure the replication lag with regard to the replay_lsn, because replay is deliberately delayed.
    Instead, measure the difference to flush_lsn, the WAL position successfully transferred to the standby and persisted there.
    
    Yours,
    Laurenz Albe
    
    
    Freundliche Grüße
    
    i. A. Ramachandran Subramanian
    Zentralbereich Informationstechnologie
    
    Alte Leipziger Lebensversicherung a.G.
    
    
    Hallesche Krankenversicherung a.G.
    
    
    
    
    
    Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
    Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
    
    
    
    
    
     
    Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
    Wiltrud Pekarek, Udo Wilcsek
    Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
    Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
     
    
    
    
     
    Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
    
    
    
    
    
    ______________________
    
    ALH Gruppe
    Alte Leipziger-Platz 1, 61440 Oberursel
    Tel.: +49 (6171) 66-4882
    Fax: +49 (6171) 66-800-4882
    E-Mail: ramachandran.subramanian@alte-leipziger.de
    www.alte-leipziger.de
    www.hallesche.de
    
    

12. Re: Replication Testing- How to introduce a Lag

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-24T07:07:00Z

    On Tue, 2026-03-24 at 06:14 +0000, Subramanian,Ramachandran wrote:
    > I noticed that if I insert one row in a table at the source, the difference in LSNs
    > is not 1 .  ( with a delibrately introduced delay on the apply side ), 
    > 
    > It is sometimes 96, sometimes 296 ( for the same table two inserts  ) .
    
    Right, because the LSN is not a counter that increases with each new WAL
    record.  It is a position in the WAL stream.  The difference between the
    LSNs of two adjacent WAL records is not 1, but the byte count of the first
    WAL record.
    
    For example: if you insert a larger row, the LSN will advance more.
    Note also that not all inserts will produce the same kind of WAL:
    one insert might write a full page image to the WAL, while the next
    a normal insert record.
    
    > Is there a method to calculate the APPROXIMATE amount of data in ( Bytes )
    > that are yet to be transfered from Source to Standby ?
    
    That's exactly what pg_wal_lsn_diff() does.
    
    What is your worry?  What is your ultimate goal?
    
    Yours,
    Laurenz Albe
    
    
    
    

13. AW: Replication Testing- How to introduce a Lag

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-24T07:51:57Z

    Thank you for your reply. I am grateful for your knowledge. 
    
    My aim is to be able to see 
    
    1. Is replication caught up  -->  No problems here
    2. If not caught up , what is the LSN difference --> No problems here , you have already helped me 
    3. If not caught up, how many bytes / KB worth of data needs to be replicated 
    
    So far my replication runs like a dream, I am just preparing for when we transfer our mainframe tables to Postgres and I should be able to answer such questions. 
    
    I am just preparing myself.
    
    LG
    
    Ram 
    
    
    
    
    Freundliche Grüße
    
    i. A. Ramachandran Subramanian
    Zentralbereich Informationstechnologie
    
    Alte Leipziger Lebensversicherung a.G.
    
    
    Hallesche Krankenversicherung a.G.
    
    
    
    
    
    
    
    Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
    Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
    
    
    
    
    
     
    Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
    Wiltrud Pekarek, Udo Wilcsek
    Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
    Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
     
    
    
    
     
    Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
    
    
    
    
    
    ______________________
    
    ALH Gruppe
    Alte Leipziger-Platz 1, 61440 Oberursel
    Tel.: +49 (6171) 66-4882
    Fax: +49 (6171) 66-800-4882
    E-Mail: ramachandran.subramanian@alte-leipziger.de
    www.alte-leipziger.de
    www.hallesche.de
    
    
    
    -----Ursprüngliche Nachricht-----
    Von: Laurenz Albe <laurenz.albe@cybertec.at> 
    Gesendet: Dienstag, 24. März 2026 08:07
    An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org
    Betreff: Re: Replication Testing- How to introduce a Lag
    
    On Tue, 2026-03-24 at 06:14 +0000, Subramanian,Ramachandran wrote:
    > I noticed that if I insert one row in a table at the source, the 
    > difference in LSNs is not 1 .  ( with a delibrately introduced delay 
    > on the apply side ),
    > 
    > It is sometimes 96, sometimes 296 ( for the same table two inserts  ) .
    
    Right, because the LSN is not a counter that increases with each new WAL record.  It is a position in the WAL stream.  The difference between the LSNs of two adjacent WAL records is not 1, but the byte count of the first WAL record.
    
    For example: if you insert a larger row, the LSN will advance more.
    Note also that not all inserts will produce the same kind of WAL:
    one insert might write a full page image to the WAL, while the next a normal insert record.
    
    > Is there a method to calculate the APPROXIMATE amount of data in ( 
    > Bytes ) that are yet to be transfered from Source to Standby ?
    
    That's exactly what pg_wal_lsn_diff() does.
    
    What is your worry?  What is your ultimate goal?
    
    Yours,
    Laurenz Albe
    

14. Re: Replication Testing- How to introduce a Lag

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-24T16:41:51Z

    On Tue, 2026-03-24 at 07:51 +0000, Subramanian,Ramachandran wrote:
    > 3. If not caught up, how many bytes / KB worth of data needs to be replicated
    
    Please define "caught up", in particular how you understand the term
    in the presence of recovery_min_apply_delay, which you said you are setting.
    The point of the parameter is to *prevent* WAL replay from catching up.
    
    Yours,
    Laurenz Albe
    
    
    
    

15. AW: Replication Testing- How to introduce a Lag

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-03-24T17:01:07Z

    I am setting the recovery_min_apply_delay ONLY to simulate/force a delay , because I cannot test my process otherwise. 
    
    Without this parameter the replication is happening in near real time and I can never test the part of the monitor/Health check that is supposed to wake up and report a delay.
    
    Once I have tested my monitoring process, I will run it with no delay. 
    
    My question is , can the difference in LSN from this query approximated to the amount of data that is yet to be transferred
    
    select  pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication
    
    LG
    
    Ram 
    
    
    
    Freundliche Grüße
    
    i. A. Ramachandran Subramanian
    Zentralbereich Informationstechnologie
    
    Alte Leipziger Lebensversicherung a.G.
    
    
    Hallesche Krankenversicherung a.G.
    
    
    
    
    
    
    
    Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
    Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
    
    
    
    
    
     
    Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
    Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
    Wiltrud Pekarek, Udo Wilcsek
    Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
    Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
     
    
    
    
     
    Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 
    
    
    
    
    
    ______________________
    
    ALH Gruppe
    Alte Leipziger-Platz 1, 61440 Oberursel
    Tel.: +49 (6171) 66-4882
    Fax: +49 (6171) 66-800-4882
    E-Mail: ramachandran.subramanian@alte-leipziger.de
    www.alte-leipziger.de
    www.hallesche.de
    
    
    
    -----Ursprüngliche Nachricht-----
    Von: Laurenz Albe <laurenz.albe@cybertec.at> 
    Gesendet: Dienstag, 24. März 2026 17:42
    An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org
    Betreff: Re: Replication Testing- How to introduce a Lag
    
    On Tue, 2026-03-24 at 07:51 +0000, Subramanian,Ramachandran wrote:
    > 3. If not caught up, how many bytes / KB worth of data needs to be 
    > replicated
    
    Please define "caught up", in particular how you understand the term in the presence of recovery_min_apply_delay, which you said you are setting.
    The point of the parameter is to *prevent* WAL replay from catching up.
    
    Yours,
    Laurenz Albe
    

16. Re: AW: Replication Testing- How to introduce a Lag

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-25T06:04:14Z

    On Tue, 2026-03-24 at 17:01 +0000, Subramanian,Ramachandran wrote:
    > My question is , can the difference in LSN from this query approximated
    > to the amount of data that is yet to be transferred
    > 
    > select  pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication
    
    That query measures the replication lag in bytes, that *is* the amount of data.
    
    Problem solved!
    
    Yours,
    Laurenz Albe