Re: Lag clarification with Sync Replication

Thanks Rui, Keith and Scott for nice explanation and blog :)

Thanks,
Raj

On Fri, May 22, 2020 at 10:18 PM Rui DeSousa <rui(at)crazybean(dot)net> wrote:

>
>
> On May 22, 2020, at 12:36 AM, Raj kumar <rajkumar820999(at)gmail(dot)com> wrote:
>
> . But, ideally for sync replication, lag should be always 0 right.
>
>
> Incorrect. Synchronous replication means that a commit will not return
> until it has been safely written to disk on the primary and the replica.
> That means the transaction is written to WAL file on both primary and
> replica. On the primary, the transaction is also visible to transactions
> with a later xmin. On the replica, the transaction has been recorded in
> the WAL; it still needs to get applied to the database for it to become
> visible to read transactions.
>
> Also, changes could occur faster on the primary where the replica could be
> lagging behind — either by lagging in receiving the updates or writing the
> information to the WAL. What will happen is when the commit is iussed; the
> session will hang until replica catches up and writes the all the data need
> safely to the WAL.
>
> I would look at pg_stat_replication view on the primary/upstream
> provider. Here’s a query that make it easier.
>
> Sent_lag — data that still needs to be sent to the replica
> Flush_lag —data that still has not been fsync’d to WAL files yet
> Replay_lag — data that still need to be applied to the database so that it
> can be queried
>
> select pg_stat_replication.client_addr
> , pg_stat_replication.application_name
> , pg_stat_replication.sync_priority
> , pg_stat_replication.sync_state
> , pg_stat_replication.state
> , case pg_is_in_recovery()
> when true then
> pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), sent_lsn))
> else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))
> end as sent_lag
> , case pg_is_in_recovery()
> when true then
> pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), flush_lsn))
> else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> flush_lsn))
> end as flush_lag
> , case pg_is_in_recovery()
> when true then
> pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), replay_lsn))
> else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> replay_lsn))
> end as replay_lag
> from pg_stat_replication
> ;
>
>