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,
Iliyan

--000000000000a24500062045246f--