public inbox for pgsql-novice@postgresql.org
help / color / mirror / Atom feedReplication Testing- How to introduce a Lag
16+ messages / 2 participants
[nested] [flat]
* Replication Testing- How to introduce a Lag
@ 2026-03-20 06:33 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-20 06:33 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication Testing- How to introduce a Lag
@ 2026-03-20 07:40 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-20 07:40 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: Replication Testing- How to introduce a Lag
@ 2026-03-20 10:47 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-20 10:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: AW: Replication Testing- How to introduce a Lag
@ 2026-03-20 13:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-20 13:38 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-21 07:49 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-21 07:49 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-21 08:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-21 08:43 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-23 05:55 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-23 05:55 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: AW: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-23 14:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-23 14:33 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: AW: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-23 16:36 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-23 16:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: AW: AW: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-23 21:59 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-23 21:59 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: AW: AW: AW: AW: Replication Testing- How to introduce a Lag
@ 2026-03-24 06:14 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-24 06:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication Testing- How to introduce a Lag
@ 2026-03-24 07:07 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-24 07:07 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: Replication Testing- How to introduce a Lag
@ 2026-03-24 07:51 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-24 07:51 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication Testing- How to introduce a Lag
@ 2026-03-24 16:41 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2026-03-24 16:41 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* AW: Replication Testing- How to introduce a Lag
@ 2026-03-24 17:01 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-24 17:01 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: AW: Replication Testing- How to introduce a Lag
@ 2026-03-25 06:04 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 0 replies; 16+ messages in thread
From: Laurenz Albe @ 2026-03-25 06:04 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2026-03-25 06:04 UTC | newest]
Thread overview: 16+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-20 06:33 Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-20 07:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-20 10:47 ` AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-20 13:38 ` Re: AW: Replication Testing- How to introduce a Lag Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-21 07:49 ` AW: AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-21 08:43 ` Re: AW: AW: Replication Testing- How to introduce a Lag Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-23 05:55 ` AW: AW: AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-23 14:33 ` Re: AW: AW: AW: Replication Testing- How to introduce a Lag Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-23 16:36 ` AW: AW: AW: AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-23 21:59 ` Re: AW: AW: AW: AW: Replication Testing- How to introduce a Lag Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-24 06:14 ` AW: AW: AW: AW: AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-24 07:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-24 07:51 ` AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-24 16:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-24 17:01 ` AW: Replication Testing- How to introduce a Lag Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-25 06:04 ` Re: AW: Replication Testing- How to introduce a Lag Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox