MIME-Version: 1.0
References: 
 <CACPVzBawQHXnAdSqC783b4Mkef7zxs-erqE7Ds1L+j1HctLYMg@mail.gmail.com>
In-Reply-To: 
 <CACPVzBawQHXnAdSqC783b4Mkef7zxs-erqE7Ds1L+j1HctLYMg@mail.gmail.com>
From: Asad Ali <asadalinagri@gmail.com>
Date: Thu, 22 Aug 2024 16:22:16 +0500
Message-ID: 
 <CAJ9xe=t_KzcuP8XDqGuDdbZGc9v1b07j1sy5K_OeS_QRG3FuDQ@mail.gmail.com>
Subject: Re: Cascading Replication
To: Ilian Kostadinov <ilian.kostadinov@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000cb7722062043dfb2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJ9xe%3Dt_KzcuP8XDqGuDdbZGc9v1b07j1sy5K_OeS_QRG3FuDQ%40mail.gmail.com>
Precedence: bulk

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

Hello *Ilian Kostadinov,*

Here=E2=80=99s how you can achieve this without needing to sync all data fr=
om the
beginning:

Make sure logical replication is enabled on Server B. In your
postgresql.conf, set the following parameters on Server B:


*wal_level =3D logicalmax_replication_slots =3D <desired number>max_wal_sen=
ders
=3D <desired number>*
Restart PostgreSQL on Server B for these changes to take effect.

On Server B, create a publication for the tables you want to replicate:

*CREATE PUBLICATION my_publication FOR ALL TABLES;*
If you need specific tables, adjust the query accordingly.

Create a logical replication slot on Server B. This slot will be used by
Server C to stream changes:

*SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');*
On Server C, create a subscription to the publication on Server B:


On Server C, create a subscription to the publication on Server B:


*CREATE SUBSCRIPTION my_subscription CONNECTION 'host=3D<Server B IP>
dbname=3D<dbname> user=3D<replication_user> password=3D<password>'PUBLICATI=
ON
my_publicationWITH (copy_data =3D false);*
The WITH (copy_data =3D false) clause is crucial because it prevents Server=
 C
from copying all the data from scratch, which would be inefficient given
your 50TB database size. Instead, it will start replicating changes from
the point when the subscription is created.

Since you're skipping the initial data copy, you need to ensure that the
data on Server C is in sync with Server B. If Server C was already a
streaming replica of Server B, this should already be the case.
However, if any discrepancies exist, you may need to manually sync specific
tables or sequences.

Best Regards,
Asad Ali

On Tue, Aug 20, 2024 at 4:55=E2=80=AFPM Ilian Kostadinov <ilian.kostadinov@=
gmail.com>
wrote:

> Hello,
>
> currently we have postgresql 16 with streaming replication. Is it possibl=
e
> to create logical replication of the streaming replica?
> So I have server A which is main database server with application
> connected to it.
> Also server B which is streaming replication of server A.  I succeded to
> created server C
> which is streaming replica of server B, but I want to convert it to
> logical replica of server B.
> Database is more then 50TB, so how to convert server C to logical replica
> without need to sync all data from the beginning?
>
> Best Regards,
> Iliyan
>
>

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

<div dir=3D"ltr">Hello <b>Ilian Kostadinov<font face=3D"Google Sans, Roboto=
, RobotoDraft, Helvetica, Arial, sans-serif"><span style=3D"font-size:14px"=
>,</span></font></b><br><div><b><font face=3D"Google Sans, Roboto, RobotoDr=
aft, Helvetica, Arial, sans-serif"><span style=3D"font-size:14px"><br></spa=
n></font></b></div><div>Here=E2=80=99s how you can achieve this without nee=
ding to sync all data from the beginning:</div><div><br></div><div>Make sur=
e logical replication is enabled on Server B. In your postgresql.conf, set =
the following parameters on Server B:</div><div><b>wal_level =3D logical<br=
>max_replication_slots =3D &lt;desired number&gt;<br>max_wal_senders =3D &l=
t;desired number&gt;</b><br></div><div>Restart PostgreSQL on Server B for t=
hese changes to take effect.<br></div><div><br></div><div>On Server B, crea=
te a publication for the tables you want to replicate:<br></div><div><b>CRE=
ATE PUBLICATION my_publication FOR ALL TABLES;<br></b></div><div>If you nee=
d specific tables, adjust the query accordingly.<br></div><div><br></div><d=
iv>Create a logical replication slot on Server B. This slot will be used by=
 Server C to stream changes:<br></div><div><b>SELECT * FROM pg_create_logic=
al_replication_slot(&#39;my_slot&#39;, &#39;pgoutput&#39;);<br></b></div><d=
iv>On Server C, create a subscription to the publication on Server B:<br></=
div><div><br></div><div><br></div>On Server C, create a subscription to the=
 publication on Server B:<br><div><b>CREATE SUBSCRIPTION my_subscription <b=
r>CONNECTION &#39;host=3D&lt;Server B IP&gt; dbname=3D&lt;dbname&gt; user=
=3D&lt;replication_user&gt; password=3D&lt;password&gt;&#39;<br>PUBLICATION=
 my_publication<br>WITH (copy_data =3D false);</b></div><div>The WITH (copy=
_data =3D false) clause is crucial because it prevents Server C from copyin=
g all the data from scratch, which would be inefficient given your 50TB dat=
abase size. Instead, it will start replicating changes from the point when =
the subscription is created.</div><div><br></div><div>Since you&#39;re skip=
ping the initial data copy, you need to ensure that the data on Server C is=
 in sync with Server B. If Server C was already a streaming replica of Serv=
er B, this should already be the case.</div><div>However, if any discrepanc=
ies exist, you may need to manually sync specific tables or sequences.</div=
><div><br></div><div>Best Regards,</div><div>Asad Ali</div></div><br><div c=
lass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Aug 20, =
2024 at 4:55=E2=80=AFPM Ilian Kostadinov &lt;<a href=3D"mailto:ilian.kostad=
inov@gmail.com">ilian.kostadinov@gmail.com</a>&gt; wrote:<br></div><blockqu=
ote 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>Hello,</div=
><div><br></div><div>currently we have postgresql 16 with streaming replica=
tion. Is it possible <br></div><div>to create logical replication of the st=
reaming replica? <br></div><div>So I have server A which is main database s=
erver with application connected to it.</div><div>Also server B which is st=
reaming replication of server A.=C2=A0 I succeded to created server C</div>=
<div>which is streaming replica of server B, but I want to convert it to lo=
gical replica of server B.</div><div>Database is more then 50TB, so how to =
convert server C to logical replica without need to sync all data from the =
beginning?</div><div><br></div><div>Best Regards,</div><div>Iliyan<br></div=
><div><br></div></div>
</blockquote></div>

--000000000000cb7722062043dfb2--