Обсуждение: Replication Testing- How to introduce a Lag
Hello,
I have set up streaming replication in a our test environment and it is working OK .
- Is there a way to force a lag between the source and the stand-by in an environment where there is NO load?
- 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.
______________________ ALH Gruppe Alte Leipziger Lebensversicherung a. G., Alte Leipziger-Platz 1, 61440 Oberursel 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 (1) Nr. 5 b VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
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
Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG
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
Hello
Thank you for your inputs. I did a set recovery_target_action=pause on the standby and stopped and restarted it.. now
Isee that the delay
But Obviously my understanding of the Last replayed WAL at the target is wrong.. how can the last replayed be more
thanthe 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.),
FrankKettnaker, 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.),
FrankKettnaker, 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
forkedoff 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
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
> 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 fromB) 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.), FrankKettnaker, 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.), FrankKettnaker, 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
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
Yes, but if no WAL gets generated and replicated for a while, the columns will become NULL, because then delay measured
intime 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.),
FrankKettnaker, 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.),
FrankKettnaker, 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
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
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 doesnot 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.), FrankKettnaker, 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.), FrankKettnaker, 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
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 doesnot 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
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 introduceddelay 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 replayis 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.), FrankKettnaker, 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.), FrankKettnaker, 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
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
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 Ishould 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.), FrankKettnaker, 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.), FrankKettnaker, 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. Thedifference 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
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
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/Healthcheck 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.), FrankKettnaker, 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.), FrankKettnaker, 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 yousaid you are setting. The point of the parameter is to *prevent* WAL replay from catching up. Yours, Laurenz Albe
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