MIME-Version: 1.0
References: <AB145716-132B-4BB5-AC69-32EA9FDE4633@attendium.com>
In-Reply-To: <AB145716-132B-4BB5-AC69-32EA9FDE4633@attendium.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Thu, 1 Aug 2024 12:03:41 +0200
Message-ID: <CAKkG4_n+TTJS9RAH5B_Hzjbwedb+j4hOqqZyuTaqPTAgz4g9xA@mail.gmail.com>
Subject: Re: Logical replication slots on slaves/replicas?
To: Piotr Andreassen Blasiak <piotr@attendium.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000000e8ae061e9c54df"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_n%2BTTJS9RAH5B_Hzjbwedb%2Bj4hOqqZyuTaqPTAgz4g9xA%40mail.gmail.com>
Precedence: bulk

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

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.

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.

All the best,
Torsten

On Thu, Aug 1, 2024 at 10:42=E2=80=AFAM Piotr Andreassen Blasiak <
piotr@attendium.com> wrote:

> Hi,
>
> 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:
>
> I want to use debezium to stream changes from postgresql. But, if I strea=
m
> changes from the master I can not query my read slaves for related data t=
o
> 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 propagate=
d
> 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.
>
> Piotr Andreassen Blasiak
>
>
>
>
>

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

<div dir=3D"ltr">A COMMIT record in the WAL has an LSN. I don&#39;t know mu=
ch about debezuim=C2=A0but wal2json can give you this LSN. Then there is th=
is function,=C2=A0pg_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 shoul=
d be able to see the changes made by the transaction.<div><br></div><div>BT=
W, 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 particul=
ar, this is possible with a sync replication setup. So, I would not trust t=
o always be able to see the transaction on the master as soon as it is repo=
rted committed in the wal stream.</div><div><br></div><div>All the best,</d=
iv><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 Andreass=
en 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:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv>Hi,<div><br></div><div>I know that currently logical replication slots a=
re available only for primary servers. Is there any plan to add this featur=
e to read slaves as well? My problem is this:</div><div><br></div><div>I wa=
nt to use debezium to stream changes from postgresql. But, if I stream chan=
ges from the master I can not query my read slaves for related data to thes=
e changes - I need to always query the master which is not scalable. So eit=
her 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 simpl=
y 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-spa=
cing:0px;text-decoration:none;color:rgb(0,0,0)"><div><br>Piotr Andreassen B=
lasiak</div><div><br></div></div><br style=3D"color:rgb(0,0,0);font-family:=
Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-we=
ight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transf=
orm:none;white-space:normal;word-spacing:0px;text-decoration:none"><br>
</div>
<br></div></div></blockquote></div>

--00000000000000e8ae061e9c54df--