Hello,

currently we have pos= tgresql 16 with streaming replication. Is it possible

to cre= ate 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.=C2=A0 I s= ucceded to created server C

which is streaming replica of server = B, but I want to convert it to logical replica of server B.

Datab= ase is more then 50TB, so how to convert server C to logical replica withou= t need to sync all data from the beginning?

Best R= egards,

Iliyan

--00000000000001fd7e06201c1af8-- From asadalinagri@gmail.com Thu Aug 22 11:22:16 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sh5tk-00Co4K-Bh for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2024 11:22:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sh5ti-001fAn-D6 for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2024 11:22:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sh5th-001fAf-TY for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2024 11:22:34 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sh5tf-000tHF-SK for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2024 11:22:33 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-a86910caf9cso96945566b.1 for ; Thu, 22 Aug 2024 04:22:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724325750; x=1724930550; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EM5bX6krcAHZqxilkmYE5Edk/DcBIf2Aybs6fgHhb9U=; b=VHGvowDqJMblEHk0zP9M6G3dY6/yagKUD+SCJblntyHzqBxIqfqxTrfJf3HhQNS/Zt mHFpAL77r92eDvsvf4uQ+9eW7FKmzwn8FqNouh4XuEZmRQ8C1QKD7xO83RNLhX9LxT1l XFIDX24h9D8Yu3WglwoYkV3CyahibmtUP7S6htMlBwbqHCCgnLMb3vrqNdCLLxXZKTeN Gor11/7p7jmuujcdoOMESBXSBZkNdrP8Z+GT/GcGk45Jd/ex+7mxBSjHv/kIB++74auG nNIojmGiEGOrYK1aTwEufoekpr8ITnwxTAz+Ri8p+mzo2o+zamrFoVy8HC6zn4bM8vRo mHDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724325750; x=1724930550; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=EM5bX6krcAHZqxilkmYE5Edk/DcBIf2Aybs6fgHhb9U=; b=newtuLwBHxdHK7HH15rrK3bVIEP5yNpSqg4wgn6ShQRgkPIltwQgNl1zOGfj2drECH iOuHQd+icWcBAIHSqIrPYhCN30M5Cwj5zE82xfqvQmSU/+GVuBpcDnGWZDsWvubEx3C+ LPO/F+WPqkKNG5B6AULq9YHX9dllcXW57OQriJe7ZpaC9banJ+URIsra4epkeFRNUyvx OC7/zo09s8yJ9W81jb/OatleBBS2i/CQB2sbvaOKhfmgmJoWfceK5k3/Mn0NKRnvepA4 knsuarPCdTzxqWSc0F3ZVho0Y0nMRRtt38aOSUwcblc3AxKN/yNOdEL6CNV8THoTWMfW tVMA== X-Gm-Message-State: AOJu0YwZxmQEoUOsz806cMprYs0Wl3e1ee1n0CKfncH8+rMHZm2AIimw n9KP4DRyWObhfWGzQzoWYIQ4fV1XUfFeIZZJYpPDAexU5Cim9r6H/Kuz/UcEX1TL47Nse0CfpCt vXnkiKnFKqjGwHYwwyym9IE/zwBpS+nH0 X-Google-Smtp-Source: AGHT+IFP2qBmb2bo562OiixOEYmxSdB4I4a0nceWmlmx3pi9ajg3wCUVp2lpwA9Ekz6odUuncnhnVZtq16hPTw4Lo0w= X-Received: by 2002:a17:906:4fcb:b0:a7a:acae:340b with SMTP id a640c23a62f3a-a868a92106emr223066266b.31.1724325749812; Thu, 22 Aug 2024 04:22:29 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Asad Ali Date: Thu, 22 Aug 2024 16:22:16 +0500 Message-ID: Subject: Re: Cascading Replication To: Ilian Kostadinov Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000cb7722062043dfb2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: 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 max_wal_sen= ders =3D * 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 dbname=3D user=3D password=3D'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 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

Hello Ilian Kostadinov,

Here=E2=80=99s how you can achieve this without nee= ding to sync all data from the beginning:

Make sur= e 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_senders =3D &l= t;desired number>

Restart PostgreSQL on Server B for t= hese changes to take effect.

On Server B, crea= te a publication for the tables you want to replicate:

CRE= ATE PUBLICATION my_publication FOR ALL TABLES;

If you nee= d 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_logic= al_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>'
PUBLICATION= my_publication
WITH (copy_data =3D false);

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.

Since you'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.

However, if any discrepanc= ies 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 replica= tion. Is it possible

to create logical replication of the st= reaming replica?

So I have server A which is main database s= erver with application connected to it.

Also server B which is st= reaming replication of server A.=C2=A0 I succeded to created server C

which is streaming replica of server B, but I want to convert it to lo= gical 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-- From ramsveeru441@gmail.com Thu Aug 22 12:53:08 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sh7Jd-00D9Vh-Vz for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2024 12:53:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sh7Jc-002SIp-3N for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2024 12:53:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sh7Jb-002SIZ-Jr for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2024 12:53:24 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sh7JZ-000tzW-HY for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2024 12:53:22 +0000 Received: by mail-ej1-x62f.google.com with SMTP id a640c23a62f3a-a86984e035aso41498666b.2 for ; Thu, 22 Aug 2024 05:53:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724331200; x=1724936000; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=nRNjEQMuyEq5gYT75jxpub6OoXMK2oZtR49l+psgMRo=; b=PpwUUSWvcS+JSr65Z1eY06e9xxPrmrC+UGxU2eKGuEvjvNxjj2uhj6TQTjeOGu5PNO KPyG9tlcnsEaKcq9Ag0Prv2ZKJYXhDjNOOeV9yBUy7WMUrQV3UVrfxJjtyeb2NlFQ/Dp eluZhZfIaKhirg0p/VZ0uGfVQfsJPkWmfsLEZNbdALxBrJZYn5HhreDPR7nRcnl1R1F+ UoEbh8Qhnk0FmUH5g1P+/UG2ebci/Hi2I13F+nnPYR1CaegHDEQUUXacGmDyYJdmX0HZ BH6FG/8sU2JYofiiKTVN/9df4HUhX0o/tkifTNO3vMlXziy6RluPSFmOTQ9Ld2OE4DWX TZEA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724331200; x=1724936000; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=nRNjEQMuyEq5gYT75jxpub6OoXMK2oZtR49l+psgMRo=; b=dNVwFt1XkDLPSwVKoOO4cLOHVZN8ZpxecrOE8mTptAgKPHnChltBZNoKBnbi7QuWNM ZSxBGU1Jz7b1c9dGgBe5I5V+AyxgZQWSvxevClOOJL3w26ZBYyCGcGDE+i54HcQPuvkv +N/kST1IdS45wyLi1tj9xhsJbizke/VlgctUYxp+ykhys4WUssIHNkOAg5iYhpy7ul3n kR0rd//c2bTKKggEOqdcBA3HLn85KrsLqDfCzlbEd6z3LxIeRRN1L0qC2HPo0hK+03Ea UfewfBVl9peDgTD09mf1tIutHIILRbvCO6lzX0x7qLkQmQKrQc7sy5xx5LexyyUk+ZV5 8nzg== X-Forwarded-Encrypted: i=1; AJvYcCVJ3Si5ZlavKzNqOl0Al74YHEmvHRNVjDNqR66HI3pB3Vx2vMcfMBxs+u1zXXllLQhnSi8BokN9ZB+hdA==@lists.postgresql.org X-Gm-Message-State: AOJu0YxTvn+D58jYtH6+gU8ZHkIWQZ5sSt1eEuo/0GRfTrgDyqXr4el/ rWR7iJq2w4SZI75+22ta0jfQdYfgvacnvYgkbWyjfNbF8Pt5TdtZbRxsFcV3aKoSMxY5LxUcE9A H38I/eBDP1vaGeEz2XljZHPPOGotirg== X-Google-Smtp-Source: AGHT+IHwMbcCV0cUjUQaxb3r8FD2GwexgB2JlH5QHGqhIOnB2e+XRefmtAjOsvUHauW0UcfUQitkq74AA8MnjJb8weI= X-Received: by 2002:a17:907:971d:b0:a86:7c6f:7cfa with SMTP id a640c23a62f3a-a867c6f7da8mr389297366b.37.1724331199708; Thu, 22 Aug 2024 05:53:19 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: rams nalabolu Date: Thu, 22 Aug 2024 07:53:08 -0500 Message-ID: Subject: Re: Cascading Replication To: Asad Ali Cc: Ilian Kostadinov , pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a24500062045246f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a24500062045246f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Along with Asad steps you need to add one extra parameter hot_standby_feedback=3Don on Standby server(B) And while creating subscriptions on server C using server B connection information it will be on halt state and won=E2=80=99t finish fast. This wi= ll happen while server B is waiting to sync the data from Server A. In this case you need to refresh the replication state between server A and Server B using =E2=80=9Cselect pg_log_standby_snapshot()=E2=80=9D and there after = the server B will allow the replication commands to be executed and subscription creation will be finished. Thanks Veeru On Thu, Aug 22, 2024 at 6:22=E2=80=AFAM Asad Ali w= rote: > Hello *Ilian Kostadinov,* > > Here=E2=80=99s how you can achieve this without needing to sync all data = from 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 number>max_wal_senders =3D * > 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 > dbname=3D user=3D password=3D'PUBLICA= TION > my_publicationWITH (copy_data =3D false);* > The WITH (copy_data =3D false) clause is crucial because it prevents Serv= er > C from copying all the data from scratch, which would be inefficient give= n > 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 >> possible >> 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 replic= a >> without need to sync all data from the beginning? >> >> Best Regards, >> Iliyan >> >> --000000000000a24500062045246f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Along with Asad steps you need to add one extra parameter= hot_standby_feedback=3Don on Standby server(B)=C2=A0

And while creating subscriptions on server C = using server B connection information it will be on halt state and won=E2= =80=99t finish fast. This will happen while server B is waiting to sync the= data from Server A. In this case you need to refresh the replication state= between server A and Server B using =E2=80=9Cselect pg_log_standby_snapsho= t()=E2=80=9D and there after the server B will allow the replication comman= ds to be executed and subscription creation will be finished.=C2=A0

Tha= nks

Veeru

<= div dir=3D"ltr" class=3D"gmail_attr">On Thu, Aug 22, 2024 at 6:22=E2=80=AFA= M Asad Ali <asadalinagri@gmail= .com> wrote:

Hello Ili= an Kostadinov,

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

Make sure logical replication = is enabled on Server B. In your postgresql.conf, set the following paramete= rs on Server B:
wal_level =3D logical
max_replication_slots= =3D <desired number>
max_wal_senders =3D <desired number>
Restart PostgreSQL on Server B for these changes to take e= ffect.

On Server B, create a publication for t= he tables you want to replicate:
CREATE PUBLICATION my_pub= lication FOR ALL TABLES;
If you need specific tables, adj= ust the query accordingly.

Create a logical re= plication slot on Server B. This slot will be used by Server C to stream ch= anges:
SELECT * FROM pg_create_logical_replication_slot(&#= 39;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&= gt; password=3D<password>'
PUBLICATION my_publication
WITH = (copy_data =3D false);
The WITH (copy_data =3D false) clause = is crucial because it prevents Server C from copying all the data from scra= tch, which would be inefficient given your 50TB database size. Instead, it = will start replicating changes from the point when the subscription is crea= ted.

Since you're skipping the initial data co= py, 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 alread= y be the case.
However, if any discrepancies exist, you may need = to manually sync specific tables or sequences.

Bes= t Regards,
Asad Ali

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

currently we hav= e postgresql 16 with streaming replication. Is it possible
t= o create logical replication of the streaming replica?
So I = have server A which is main database server with application connected to i= t.
Also server B which is streaming replication of server A.=C2= =A0 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,