Re: Replication lag due to lagging restart_lsn - Mailing list pgsql-performance

From milist ujang
Subject Re: Replication lag due to lagging restart_lsn
Date
Msg-id CACG9ogw+Naq08+osrYxc-6Skbp3QMeQ6aZ2=mFcy5HJ1Hr2U0g@mail.gmail.com
Whole thread Raw
In response to Re: Replication lag due to lagging restart_lsn  (Kiran Singh <kiranjanarthan24@gmail.com>)
Responses Re: Replication lag due to lagging restart_lsn
List pgsql-performance
I have a case that master has been restarted many times, restart_lsn not moved since nov 2022 till today apr 2023.
I have tried pg_replication_slot_advance() but no luck :-(

postgres 12.8
1 master (4 publisher) many SR slaves, and 1 logical replication (4 subscribers) 

Is there a chance to edit the state file under the pg_replslots folder?


On Wed, Aug 19, 2020 at 8:16 PM Kiran Singh <kiranjanarthan24@gmail.com> wrote:
When logical replication is setup, any wal generation on any tables will result in replication lag. Since you are running a long running transaction on the master, the maximum number of changes kept in the memory per transaction is 4MB. If the transaction requires more than 4MB the changes are spilled to disk. This is when you will start seeing 

1. Replication lag spiking 
2. Storage being consumed
3. Restart lsn stops moving forward

You can confirm if the heavy write that you are talking about is spilling to disk or not by setting log_min_messges to debug 2. Try to find if the changes are spilled to disk. 

To answer your question: 

1. As long as the write heavy query is running on the database, you will not see restart lsn moving. 
2. You will have to have smaller transactions
3. When the query is completed, you will see restart_lsn moving forward  
 
--
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

pgsql-performance by date:

Previous
From: kunwar singh
Date:
Subject: Re: Is there any tool which will help me run and explain analyze about 150 queries?
Next
From: Michael Christofides
Date:
Subject: Re: Is there any tool which will help me run and explain analyze about 150 queries?