Re: Logical replication data lag

Hi ,

pglogical.show_subscription_status() provides useful information, it
doesn't directly indicate replication lag. You can estimate replication lag
using the apply_delay column, which shows the delay in applying the
received changes.

The following view exists on the primary server and provides details about
the state of replication to each connected replica. It includes the time
difference between the current time and the last replayed transaction on
the replica.
SELECT now() - pg_last_xact_replay_timestamp() AS time_lag FROM
pg_stat_replication;

Regards,

*Muhammad Imtiaz*

*PostgreSQL Technical Support Lead *
*/ Pakistan R&D*
*Mobile: +923345072521*
*Email: imtiaz(dot)m(at)bitnine(dot)net <imtiaz(dot)m(at)bitnine(dot)net>*

On Wed, Jul 31, 2024 at 9:40 AM Siraj G <tosiraj(dot)g(at)gmail(dot)com> wrote:

> Sorry, just wanted to make a little correction:
> I get the status report in the target as replicating when I query this:
>
> SELECT * FROM pglogical.show_subscription_status();
>
> But I do not know how to check the stats for this.
>
> Appreciate any help.
>
> On Wed, Jul 31, 2024 at 8:49 AM Siraj G <tosiraj(dot)g(at)gmail(dot)com> wrote:
>
>> Hello Admins!
>>
>> I am trying to find the replication lag in the target that is being
>> replicated using pglogical. I do not see any data in pg_stat_subscription.
>> We are replicating data from GCP cloud SQL to a compute instance within the
>> same VCN.
>>
>> I get the status report in the target as replication, but not sure why
>> pg_stat_subscription does not show anything.
>>
>> Should I do it programmatically by creating a heartbeat table on the
>> source, add it to the replication and monitor the data in this to get the
>> report?
>>
>> Thanks in advance!
>> Regards
>> Siraj
>>
>