AW: Replication Testing- How to introduce a Lag

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(dot)subramanian(at)alte-leipziger(dot)de
www.alte-leipziger.de
www.hallesche.de

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Gesendet: Freitag, 20. März 2026 08:41
An: Subramanian,Ramachandran IT-md-db <ramachandran(dot)subramanian(at)alte-leipziger(dot)de>; pgsql-novice(at)lists(dot)postgresql(dot)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