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?
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