Re: Logical Replication Sync Validation

On Tue, 2023-04-18 at 10:20 +0200, Robert Sjöblom wrote:
> At the moment where we would switch to pg15 being the primary/lead we
> will stop writes to pg10; at that point we will validate that we are
> fully in sync, tear down pg10 and send writes to pg15. Our question is
> how we can validate our sync status. Given that there have been no DDL
> changes on pg10 (new tables, for instance), would the following check be
> sufficient?
>
> Compare byte diff between WAL LSNs:
> SELECT abs(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS lag FROM
> pg_stat_replication;
>
> If the byte diff is 0, I would assume that we're fully in sync. Is this
> understanding correct?

I would compare the last replayed log sequence number with the current
position on the primary:

SELECT abs(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

If that is 0, you are good.

Yours,
Laurenz Albe