Re: Monitoring logical replication

On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque <shaheedhaque(at)gmail(dot)com>
wrote:

> Hi all,
>
> Is there an "official" pairing of LSN values on the publication and
> subscription sides that should be used to track the delta between the two
> systems? I ask because Google is full of different pairs being used. I
> tried to identify the highest level interface points exposed, i.e. what is
> documented on
> https://www.postgresql.org/docs/current/replication-origins.html, the
> pg_stat_subscription table, the pg_stat_publication table and the
> pg_current_wal_lsn() function on the publisher, but these seem to be barely
> used.
>

The attached scripts (whose guts I took from a Stack Exchange post) might
be a good starting point. It certainly works for physical replication!

> P.S. On a related note, I see a (stalled?) discussion on providing LSN ->
> timestamp conversion
> <https://www.postgresql.org/message-id/flat/CAAKRu_bw7Pgw8Mi9LJrBkFvPPHgvVjPphrT8ugbzs-2V0f%2B1Rw%40mail.gmail.com#8540282228634ecd061585867c6275ca>,
> I'd just like to say that something like that would be very useful.
>

Out of curiosity, how does that work? Is an instance's initial LSN really
based on Epoch?