Re: Lag clarification with Sync Replication

Поиск
Список
Период
Сортировка
От Raj kumar
Тема Re: Lag clarification with Sync Replication
Дата
Msg-id CACxU--VkZYb3Mqt0p4h0vghHMVyxQsh50ZnqCL=0pZsLv1YtMQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Lag clarification with Sync Replication  (Rui DeSousa <rui@crazybean.net>)
Список pgsql-admin
Thanks Rui, Keith and Scott for nice explanation and blog :)

Thanks,
Raj


On Fri, May 22, 2020 at 10:18 PM Rui DeSousa <rui@crazybean.net> wrote:


On May 22, 2020, at 12:36 AM, Raj kumar <rajkumar820999@gmail.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
;

В списке pgsql-admin по дате отправления:

Предыдущее
От: Rui DeSousa
Дата:
Сообщение: Re: Lag clarification with Sync Replication
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: Lag clarification with Sync Replication