Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

soumitra
Hi Pavan , 

Please try below query , this should give you retention lag bytes in case of logical replication  . 

SELECT
      slot_name, database, active,
      pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes
    FROM pg_replication_slots;

Thanks , 
Soumitra 

From: pavan95 <[hidden email]>
Sent: Tuesday, October 9, 2018 11:29 AM
To: [hidden email]
Subject: Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
 
Hi Community,

I configured Logical Replication in Postgres 10.5 using 2 nodes(Publisher
and Subscriber). I'm in my way to find lag in logical replication.

From the documentation I found that, pg_current_wal_lsn() on the publisher
and pg_last_wal_receive_lsn() on the subscriber helps us to find the lag in
which the subscriber is falling behind.

But, the function pg_last_wal_receive_lsn() is continuously returning NULL
value. In fact, all the Recovery Information
Functions(pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp())
are returning null. 

From the Subscriber:

subscriber_db=# select pg_last_wal_receive_lsn();
 pg_last_wal_receive_lsn
-------------------------

(1 row)


But according to documentation, the above 3 functions will return NULL only
if streaming replication is disabled or if it has not yet started. But in my
case, the logical replication is working fine. So I wanted to know if I
could get a value by enabling any settings recommended.

Also, is there a query to find the lag in bytes between publisher and
subscriber?

Thanks in Advance.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Soumitra Bhandary Postgres Database Administrator
Reply | Threaded
Open this post in threaded view
|

Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

pavan95
Hi Soumitra,

Could you please explain the reason behind taking the
pg_current_xlog_insert_location() & restart_lsn?


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

pavan95
Community,

Any help on this will be of great help!  

I am not able to understand why these functions return NULL values. Please
let me know if I'm missing something.

Thanks in Advance

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

Mark Kirkwood-2
In reply to this post by pavan95
I think those functions really only make sense for physical replication.

Looking at the docs e.g:
https://www.postgresql.org/docs/10/static/logical-replication-monitoring.html 
they appear to *suggest* that monitoring should be similar...some
clarification there is probably in order!

However they also say to use the pg_stat_subsubscription view to monitor
your logical rep, e.g: here's what my pgbench dataset subscription looks
like:

bench=# SELECT * FROM pg_stat_subscription;
  subid | subname | pid  | relid | received_lsn |
last_msg_send_time       |     last_msg_receipt_time     |
latest_end_lsn |        latest_end_time
-------+---------+------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
  16409 | pgbench | 8840 |       | 0/51EB3A20   | 2018-10-15
15:16:38.501061+13 | 2018-10-15 15:16:38.501103+13 | 0/51EB3A20     |
2018-10-15 15:16:38.501061+13
(1 row)

Cheers

Mark


On 11/10/18 02:43, pavan95 wrote:

> Hi Soumitra,
>
> Could you please explain the reason behind taking the
> pg_current_xlog_insert_location() & restart_lsn?
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>


Reply | Threaded
Open this post in threaded view
|

Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

soumitra
Hi Pavan , 

Yes as Mark explained , in logical replication no db server is in recovery mode and replication is happening through Replication slot . So no log shipping is involved here . 

Hope this clarified your queries . 


Thanks , 
Soumitra 

From: Mark Kirkwood <[hidden email]>
Sent: Monday, October 15, 2018 7:52 AM
To: [hidden email]
Subject: Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
 
I think those functions really only make sense for physical replication.

Looking at the docs e.g:
https://www.postgresql.org/docs/10/static/logical-replication-monitoring.html
they appear to *suggest* that monitoring should be similar...some
clarification there is probably in order!

However they also say to use the pg_stat_subsubscription view to monitor
your logical rep, e.g: here's what my pgbench dataset subscription looks
like:

bench=# SELECT * FROM pg_stat_subscription;
  subid | subname | pid  | relid | received_lsn |
last_msg_send_time       |     last_msg_receipt_time     |
latest_end_lsn |        latest_end_time
-------+---------+------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
  16409 | pgbench | 8840 |       | 0/51EB3A20   | 2018-10-15
15:16:38.501061+13 | 2018-10-15 15:16:38.501103+13 | 0/51EB3A20     |
2018-10-15 15:16:38.501061+13
(1 row)

Cheers

Mark


On 11/10/18 02:43, pavan95 wrote:
> Hi Soumitra,
>
> Could you please explain the reason behind taking the
> pg_current_xlog_insert_location() & restart_lsn?
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>


Soumitra Bhandary Postgres Database Administrator