pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Null value returned by function pg_last_wal_receive_lsn() in Logical Replication
6+ messages / 3 participants
[nested] [flat]
* Re: Null value returned by function pg_last_wal_receive_lsn() in Logical Replication
@ 2018-10-09 05:59 pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: pavan95 @ 2018-10-09 05:59 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
@ 2018-10-09 07:10 soumitra bhandary <soumitra.bhandary@hotmail.com>
2018-10-10 13:43 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: soumitra bhandary @ 2018-10-09 07:10 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; pgsql-admin
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 <pavan.postgresdba@gmail.com>
Sent: Tuesday, October 9, 2018 11:29 AM
To: pgsql-admin@postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
2018-10-09 07:10 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
@ 2018-10-10 13:43 ` pavan95 <pavan.postgresdba@gmail.com>
2018-10-11 11:32 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-10-15 02:22 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 2 replies; 6+ messages in thread
From: pavan95 @ 2018-10-10 13:43 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
2018-10-09 07:10 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
2018-10-10 13:43 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
@ 2018-10-11 11:32 ` pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: pavan95 @ 2018-10-11 11:32 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
2018-10-09 07:10 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
2018-10-10 13:43 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
@ 2018-10-15 02:22 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-10-15 05:48 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Mark Kirkwood @ 2018-10-15 02:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication
2018-10-09 07:10 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
2018-10-10 13:43 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-10-15 02:22 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2018-10-15 05:48 ` soumitra bhandary <soumitra.bhandary@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: soumitra bhandary @ 2018-10-15 05:48 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <mark.kirkwood@catalyst.net.nz>
Sent: Monday, October 15, 2018 7:52 AM
To: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-10-15 05:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-09 05:59 Re: Null value returned by function pg_last_wal_receive_lsn() in Logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-10-09 07:10 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
2018-10-10 13:43 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-10-11 11:32 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-10-15 02:22 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-10-15 05:48 ` Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication soumitra bhandary <soumitra.bhandary@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox