MIME-Version: 1.0
References: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
 <1628637f-419f-4f6a-9cb6-07af90cd0bc4@aklaver.com> <CAGbX52HTgb2jYYO5eVDu=xB5-bjAra+34=AUFg4Xk0NpCRjnyg@mail.gmail.com>
 <1c0273f5-a90a-48f6-b51f-fe15c16fa1c6@aklaver.com> <CAGbX52EMG+d-BR5SzDP+rQsrdxT32=CXD5XCT2YCmkTpr3jesw@mail.gmail.com>
 <490e8a5c-f1f5-40fe-8243-f1c8c18d03f2@aklaver.com> <CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@mail.gmail.com>
 <CALL-XeOZ36D2z5qcxhF0qf-m9+ArioRVXhN9KEUCgMM69UjMLA@mail.gmail.com> <CAGbX52F7goP9u+FojqVYFWuB=HxBbOPC-6F_n0WKgkVep2x1nw@mail.gmail.com>
In-Reply-To: <CAGbX52F7goP9u+FojqVYFWuB=HxBbOPC-6F_n0WKgkVep2x1nw@mail.gmail.com>
From: Justin <zzzzz.graf@gmail.com>
Date: Wed, 12 Jun 2024 13:01:20 -0400
Message-ID: <CALL-XePtmrXsKCXg6s_ApW3j9E5+QJqi8oOw+yTHeeC_RgjOsA@mail.gmail.com>
Subject: Re: Questions on logical replication
To: Koen De Groote <kdg.dev@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>, 
	Adrian Klaver <adrian.klaver@aklaver.com>
Content-Type: multipart/alternative; boundary="0000000000008ff832061ab455b5"
Archived-At: <https://www.postgresql.org/message-id/CALL-XePtmrXsKCXg6s_ApW3j9E5%2BQJqi8oOw%2ByTHeeC_RgjOsA%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jun 11, 2024 at 5:43=E2=80=AFPM Koen De Groote <kdg.dev@gmail.com> =
wrote:

> > If there are any errors during the replay of WAL such as missing indexe=
s
> for Replica Identities during an Update or Delete  this will cause the ma=
in
> subscriber worker slot on the publisher to start backing up WAL files
>
> And also if the connection breaks, from what I understand, is that
> correct? Anything that stops the subscription, including disabling the
> subscription, is that right?
>

Yes to all....


> > I suggest confirming all tables have replica identities or primary keys
> before going any further.
>
> Yes, I am aware of this. I made me a small script that prints which table=
s
> I have added to the publication and are done syncing, and which are
> currently not being replicated.
>


>
> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan o=
n
> the subscriber for PG 15 and earlier.
>
> I'm also aware of this. My plan is to create a publication with no tables=
,
> and add them 1 by 1, refreshing the subscriber each time.
>

Why?  what benefit does this provide you??   Add all the tables when
creating the publication and be done with it...  I get this when trying to
understand how this all works on test boxes, but for production NO idea
what you're trying to accomplish


> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>
Good

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Tue, Jun 11, 2024 at 5:43=E2=80=AF=
PM Koen De Groote &lt;<a href=3D"mailto:kdg.dev@gmail.com">kdg.dev@gmail.co=
m</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 dir=3D"ltr"><div>&gt;=20
 If there are any errors during the replay of WAL such as missing=20
indexes for Replica Identities during an Update or Delete=C2=A0 this will=
=20
cause the main subscriber worker slot on the publisher to start backing=20
up WAL files <br></div><div><br></div><div>And also if the connection break=
s, from what I understand, is that correct? Anything that stops the subscri=
ption, including disabling the subscription, is that right?</div></div></bl=
ockquote><div>=C2=A0</div><div>Yes to all....=C2=A0</div><div><br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br></=
div><div>&gt;=20
I suggest confirming all tables have replica identities or primary keys bef=
ore going any further. <br></div><div><br></div><div>Yes, I am aware of thi=
s. I made me a small script that prints which tables I have added to the pu=
blication and are done syncing, and which are currently not being replicate=
d.</div></div></blockquote><div>=C2=A0</div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div dir=3D"ltr"><div></div><div><br></div><div>&gt;
With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan on th=
e subscriber for PG 15 and earlier. <br></div><div><br></div><div>I&#39;m a=
lso aware of this. My plan is to create a publication with no tables, and a=
dd them 1 by 1, refreshing the subscriber each time.</div></div></blockquot=
e><div>=C2=A0</div><div>Why?=C2=A0 what benefit=C2=A0does this provide you?=
?=C2=A0 =C2=A0Add all the tables when creating the publication and be done =
with it...=C2=A0 I get this when trying to understand how this all works on=
 test boxes, but for production NO idea what you&#39;re trying to accomplis=
h=C2=A0</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div><br></div><div>I&#39;m not planning on using &quot=
;REPLICA IDENTITY FULL&quot; anywhere.<br></div></div></blockquote><div>Goo=
d=C2=A0</div></div></div>

--0000000000008ff832061ab455b5--