From: Piotr Andreassen Blasiak <piotr@attendium.com>
Message-Id: <E9D10E18-9D37-4A36-AE72-F79B64907C7A@attendium.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_B8E0C44D-42F7-456E-B50C-F88287DB4B9E"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\))
Subject: Re: Logical replication slots on slaves/replicas?
Date: Thu, 1 Aug 2024 16:18:54 +0200
In-Reply-To: <CAKkG4_n+TTJS9RAH5B_Hzjbwedb+j4hOqqZyuTaqPTAgz4g9xA@mail.gmail.com>
Cc: pgsql-general@lists.postgresql.org
To: =?utf-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
References: <AB145716-132B-4BB5-AC69-32EA9FDE4633@attendium.com>
 <CAKkG4_n+TTJS9RAH5B_Hzjbwedb+j4hOqqZyuTaqPTAgz4g9xA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/E9D10E18-9D37-4A36-AE72-F79B64907C7A%40attendium.com>
Precedence: bulk


--Apple-Mail=_B8E0C44D-42F7-456E-B50C-F88287DB4B9E
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

That=C2=B4s a great point. Thank you.

Piotr Andreassen Blasiak


> On 1 Aug 2024, at 12:03, Torsten F=C3=B6rtsch <tfoertsch123@gmail.com> =
wrote:
>=20
> A COMMIT record in the WAL has an LSN. I don't know much about =
debezuim but wal2json can give you this LSN. Then there is this =
function, pg_last_wal_replay_lsn(). I think you could run it on the =
replica to see if you are after the point of commit. If you are, you =
should be able to see the changes made by the transaction.
>=20
> BTW, I am sure I can construct a situation where you see your =
transaction in the logical wal stream before it becomes visible on the =
master. In particular, this is possible with a sync replication setup. =
So, I would not trust to always be able to see the transaction on the =
master as soon as it is reported committed in the wal stream.
>=20
> All the best,
> Torsten
>=20
> On Thu, Aug 1, 2024 at 10:42=E2=80=AFAM Piotr Andreassen Blasiak =
<piotr@attendium.com <mailto:piotr@attendium.com>> wrote:
>> Hi,
>>=20
>> I know that currently logical replication slots are available only =
for primary servers. Is there any plan to add this feature to read =
slaves as well? My problem is this:
>>=20
>> I want to use debezium to stream changes from postgresql. But, if I =
stream changes from the master I can not query my read slaves for =
related data to these changes - I need to always query the master which =
is not scalable. So either I need a way to be able to know when the =
change has been propagated to my read replica so that I can reliably =
query it, or I am hoping I can simply read all the changes from the read =
replica which will mean it is already up to date when I query it.
>>=20
>> Piotr Andreassen Blasiak
>>=20
>>=20
>>=20
>>=20


--Apple-Mail=_B8E0C44D-42F7-456E-B50C-F88287DB4B9E
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">That=C2=B4s a =
great point. Thank you.<br id=3D"lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"font-family: Helvetica; font-size: 12px; =
font-style: normal; font-variant-caps: normal; font-weight: 400; =
letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; caret-color: =
rgb(0, 0, 0); color: rgb(0, 0, 0); overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><div><br =
class=3D"Apple-interchange-newline">Piotr Andreassen =
Blasiak</div><div><br></div></div><br class=3D"Apple-interchange-newline" =
style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant-caps: =
normal; font-weight: 400; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none;"><br class=3D"Apple-interchange-newline">
</div>
<div><br><blockquote type=3D"cite"><div>On 1 Aug 2024, at 12:03, Torsten =
F=C3=B6rtsch &lt;tfoertsch123@gmail.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div dir=3D"ltr">A COMMIT =
record in the WAL has an LSN. I don't know much about debezuim&nbsp;but =
wal2json can give you this LSN. Then there is this =
function,&nbsp;pg_last_wal_replay_lsn(). I think you could run it on the =
replica to see if you are after the point of commit. If you are, you =
should be able to see the changes made by the =
transaction.<div><br></div><div>BTW, I am sure I can construct a =
situation where you see your transaction in the logical wal stream =
before it becomes visible on the master. In particular, this is possible =
with a sync replication setup. So, I would not trust to always be able =
to see the transaction on the master as soon as it is reported committed =
in the wal stream.</div><div><br></div><div>All the =
best,</div><div>Torsten</div></div><br><div class=3D"gmail_quote"><div =
dir=3D"ltr" class=3D"gmail_attr">On Thu, Aug 1, 2024 at 10:42=E2=80=AFAM =
Piotr Andreassen Blasiak &lt;<a =
href=3D"mailto:piotr@attendium.com">piotr@attendium.com</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div>Hi,<div><br></div><div>I know =
that currently logical replication slots are available only for primary =
servers. Is there any plan to add this feature to read slaves as well? =
My problem is this:</div><div><br></div><div>I want to use debezium to =
stream changes from postgresql. But, if I stream changes from the master =
I can not query my read slaves for related data to these changes - I =
need to always query the master which is not scalable. So either I need =
a way to be able to know when the change has been propagated to my read =
replica so that I can reliably query it, or I am hoping I can simply =
read all the changes from the read replica which will mean it is already =
up to date when I query it.<br><div>
<div dir=3D"auto" style=3D"font-family: Helvetica; font-size: 12px; =
font-style: normal; font-variant-caps: normal; font-weight: 400; =
letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
text-decoration: none;"><div><br>Piotr Andreassen =
Blasiak</div><div><br></div></div><br style=3D"font-family: Helvetica; =
font-size: 12px; font-style: normal; font-variant-caps: normal; =
font-weight: 400; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; text-decoration: none;"><br>
</div>
<br></div></div></blockquote></div>
</div></blockquote></div><br></body></html>=

--Apple-Mail=_B8E0C44D-42F7-456E-B50C-F88287DB4B9E--