All we need is an easy explanation of the problem, so here it is.
I successfully started logical replication on a testing environment. I generate
insert on the master for some 60K records using procedure. While the insert process is running, i monitor the slave using "
SELECT count(*)" query. But the records are not adding. If I run the query after the insert has finished on the primary, the
SELECT count works fine, all records between slave and primary are equal.
Is this normal behaviour ? I am expecting the slave and primary to be exactly the same at almost instantly.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
It sounds like you expect to see the counts progressively change for one or a few records at a time. But as a_horse_with_no_name mentioned, if the 60k rows that are being inserted are all happening within the same transaction, then you won’t see the changes until that transaction completes.
Even on the primary server, you wouldn’t see the counts change individually, only the total count change at the end once the transaction is committed. This is the nature of transactions, one of their properties is Atomicity (of the ACID database principles), loosely meaning either all of the changes occur or none occur, there is no in-between.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂