Re: How to measure replication lag in logical replication setup.

Thank you Igor.

Ram

On Wed, Nov 8, 2017 at 7:39 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> *From:* pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner@
> postgresql.org] *On Behalf Of *rammohan ganapavarapu
> *Sent:* Tuesday, November 07, 2017 9:52 PM
> *To:* pgsql-admin(at)postgresql(dot)org
> *Subject:* [ADMIN] How to measure replication lag in logical replication
> setup.
>
>
>
> HI,
>
>
>
> How to measure replication lag time in case of logical replication?
>
>
>
> this query is returning empty
>
>
>
> SELECT now() - pg_last_xact_replay_timestamp() AS time_lag;
>
> time_lag
>
> ----------
>
>
>
> (1 row)
>
>
>
> this is my slot details
>
>
>
> SELECT * FROM pg_replication_slots;
>
> slot_name | plugin | slot_type | datoid | database | active | xmin |
> catalog_xmin | restart_lsn
>
> -----------+--------+-----------+--------+----------+-------
> -+------+--------------+-------------
>
> slot_1 | | physical | | | t | 1983 |
> | 0/50805C8
>
>
>
>
>
> In my case slot_type is physical, does it look ok or for logical
> replication the slot_type should be logical?
>
>
>
> Thanks,
>
> Ram
>
>
>
> I compare confirmed_flush_lsn in PG_REPLICATION_SLOTS on the Publisher
> with pg_current_wal_lsn():
>
>
>
> SELECT confirmed_flush_lsn, pg_current_wal_lsn(), (pg_current_wal_lsn() -
> confirmed_flush_lsn) AS lsn_distance
>
> FROM pg_catalog.pg_replication_slots
>
> WHERE slot_name = 'subscriber_1';
>
>
>
> slot_type for logical replication is logical.
>
> Slot was created “automatically”, when I created “subscriber_1”
> subscription.
>
>
>
> Regards,
>
> Igor Neyman
>
>
>
>
>