MIME-Version: 1.0
References: <CAHAc2jdAHvp7tFZBP37awcth=T3h5WXCN9KjZOvuTNJaAAC_hg@mail.gmail.com>
 <fd211d7c53452d0c8ae2168039e6d55e@pernau.at> <CAHAc2jeDbVLz4UON4e-UdYA+5JCzrVZYN99E0SDk9FRgZWXu+Q@mail.gmail.com>
 <CAHAc2jccg964PHjxkmyD6XgknS2umxGsuNtVULo2P76N+uvvHQ@mail.gmail.com>
In-Reply-To: <CAHAc2jccg964PHjxkmyD6XgknS2umxGsuNtVULo2P76N+uvvHQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 18 Jun 2024 09:32:43 -0400
Message-ID: <CANzqJaA-s+y7dPUWKkbUXKBu3_WmvU4+ZOmWEzH7su83AT8ohg@mail.gmail.com>
Subject: Re: Monitoring logical replication
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000008d0405061b2a1ecf"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA-s%2By7dPUWKkbUXKBu3_WmvU4%2BZOmWEzH7su83AT8ohg%40mail.gmail.com>
Precedence: bulk

--0000000000008d0405061b2a1ecf
Content-Type: multipart/alternative; boundary="0000000000008d0403061b2a1ecd"

--0000000000008d0403061b2a1ecd
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Jun 18, 2024 at 5:03=E2=80=AFAM Shaheed Haque <shaheedhaque@gmail.c=
om>
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 i=
s
> 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 bare=
ly
> 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_bw7Pgw8Mi9LJrBkFvPPHgv=
VjPphrT8ugbzs-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?

--0000000000008d0403061b2a1ecd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jun 18, 2024 at 5:03=E2=80=AFAM S=
haheed Haque &lt;<a href=3D"mailto:shaheedhaque@gmail.com">shaheedhaque@gma=
il.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Hi all,</div><div><b=
r></div><div>Is there an &quot;official&quot; pairing of LSN values on the =
publication and subscription sides that should be used to track the delta b=
etween the two systems? I ask because Google is full of different pairs bei=
ng used. I tried to identify the highest level interface points exposed, i.=
e. what is documented on <a href=3D"https://www.postgresql.org/docs/current=
/replication-origins.html" target=3D"_blank">https://www.postgresql.org/doc=
s/current/replication-origins.html</a>,=C2=A0the pg_stat_subscription table=
, the pg_stat_publication table and the=20
pg_current_wal_lsn() function on the publisher, but these seem to be barely=
 used.</div></div></blockquote><div><br></div><div>The attached scripts (wh=
ose guts I took from a Stack Exchange post) might be a good starting point.=
=C2=A0 It certainly works for physical replication!</div><div>=C2=A0</div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>P.S.=
 On a related note, I see a (stalled?) discussion on providing <a href=3D"h=
ttps://www.postgresql.org/message-id/flat/CAAKRu_bw7Pgw8Mi9LJrBkFvPPHgvVjPp=
hrT8ugbzs-2V0f%2B1Rw%40mail.gmail.com#8540282228634ecd061585867c6275ca" tar=
get=3D"_blank">LSN -&gt; timestamp conversion</a>, I&#39;d just like to say=
 that something like that would be very useful.</div></div></blockquote><di=
v>=C2=A0</div><div>Out of curiosity, how does that work?=C2=A0 Is an instan=
ce&#39;s initial LSN really based on Epoch?</div></div></div>

--0000000000008d0403061b2a1ecd--
--0000000000008d0405061b2a1ecf
Content-Type: text/x-sh; charset="US-ASCII"; name="physical_backlog.sh"
Content-Disposition: attachment; filename="physical_backlog.sh"
Content-Transfer-Encoding: base64
Content-ID: <f_lxkfxq8d0>
X-Attachment-Id: f_lxkfxq8d0

IyEvYmluL2Jhc2gNCg0KcmVhZG9ubHkgUHJpbWU9Zm9vMS5leGFtcGxlLmNvbQ0KcmVhZG9ubHkg
UmVwbGljYT1mb28yLmV4YW1wbGUuY29tDQoNCndhbF9sb2NhdGlvbl90b182NGJpdHMoKQ0Kew0K
ICAgIGxvY2FsIGlkPSIkezElJS8qfSINCiAgICBsb2NhbCBvZmZzZXQ9IiR7MSMjKi99Ig0KICAg
IGVjaG8gJCgoMHhGRjAwMDAwMCAqIDB4JGlkICsgMHgkb2Zmc2V0KSkNCn0NCg0KZGVjbGFyZSBQ
cmltZV9TU0UgUHJpbWVfTFNODQpkZWNsYXJlIFNlY29uZF9TU0UgU2Vjb25kX1JlY3ZfTFNOIFNl
Y29uZF9SZXBsX0xTTg0KDQpJRlM9JCdcdCcgcmVhZCAtciBQcmltZV9MU04gXA0KICAgICAgICA8
PDwkKHBzcWwgLS1ob3N0PSRQcmltZSAtWEF0IC1GJCdcdCcgXA0KICAgICAgICAgICAgICAgIC1j
ICJzZWxlY3QgcGdfY3VycmVudF93YWxfbHNuKCk7IikNCklGUz0kJ1x0JyByZWFkIC1yIFNlY29u
ZF9UaW1lc3RhbXAgU2Vjb25kX1JlY3ZfTFNOIFNlY29uZF9SZXBsX0xTTiBcDQogICAgICAgIDw8
PCQocHNxbCAtLWhvc3Q9JFJlcGxpY2EgLVhBdCAtRiQnXHQnIFwNCiAgICAgICAgICAgICAgICAt
YyAic2VsZWN0IHBnX2xhc3Rfd2FsX3JlY2VpdmVfbHNuKCkNCiAgICAgICAgICAgICAgICAgICAg
ICAgICwgcGdfbGFzdF93YWxfcmVwbGF5X2xzbigpOyIpDQoNClByaW1lX1NTRT0kKGRhdGUgKyIl
cy4lTiIgLWQgIiRQcmltZV9UaW1lc3RhbXAiKQ0KU2Vjb25kX1NTRT0kKGRhdGUgKyIlcy4lTiIg
LWQgIiRTZWNvbmRfVGltZXN0YW1wIikNCmRlY2xhcmUgUXVlcnlfTGFnPSQoZWNobyAiJFNlY29u
ZF9TU0UgLSAkUHJpbWVfU1NFIiB8IGJjIC1sKQ0KDQpwcmludGYgIlF1ZXJ5IExhZzogJWZcbiIg
JFF1ZXJ5X0xhZw0KDQplY2hvICJMU046Ig0KcHJpbnRmICIgIFByaW1lOiAgICAgICAgICAgID0g
JXNcbiIgJFByaW1lX0xTTg0KcHJpbnRmICIgIFJlcGxpY2EgUmVjZWl2ZWQ6ID0gJXNcbiIgJFNl
Y29uZF9SZWN2X0xTTg0KcHJpbnRmICIgIFJlcGxpY2EgUmVwbGF5ZWQ6ID0gJXNcbiIgJFNlY29u
ZF9SZXBsX0xTTg0KDQpkZWNsYXJlIC1pIFByaW1lX0J5dGVzPSQod2FsX2xvY2F0aW9uX3RvXzY0
Yml0cyAkUHJpbWVfTFNOKQ0KZGVjbGFyZSAtaSBTZWNvbmRfUmVjdl9CeXRlcz0kKHdhbF9sb2Nh
dGlvbl90b182NGJpdHMgJFNlY29uZF9SZWN2X0xTTikNCmRlY2xhcmUgLWkgU2Vjb25kX1JlcGxf
Qnl0ZXM9JCh3YWxfbG9jYXRpb25fdG9fNjRiaXRzICRTZWNvbmRfUmVwbF9MU04pDQoNCmVjaG8g
IkJhY2tsb2cgQnl0ZXM6Ig0KcHJpbnRmICIgIFJlY2VpdmVkID0gJScxOGRcbiIgJChlY2hvICIo
JFByaW1lX0J5dGVzIC0gJFNlY29uZF9SZWN2X0J5dGVzKSIgfCBiYyAtbCkNCnByaW50ZiAiICBS
ZXBsYXllZCA9ICUnMThkXG4iICQoZWNobyAiKCRQcmltZV9CeXRlcyAtICRTZWNvbmRfUmVwbF9C
eXRlcykiIHwgYmMgLWwp
--0000000000008d0405061b2a1ecf--