public inbox for [email protected]  

help / color / mirror / Atom feed

From: rams nalabolu <[email protected]>
To: Asad Ali <[email protected]>
Cc: Ilian Kostadinov <[email protected]>
Cc: [email protected]
Subject: Re: Cascading Replication
Date: Thu, 22 Aug 2024 07:53:08 -0500
Message-ID: <CAHq7gtA2B22KEZdBEZDcjQh8VRaSnD7YUBJZrNfXSSHPMRe7hw@mail.gmail.com> (raw)
In-Reply-To: <CAJ9xe=t_KzcuP8XDqGuDdbZGc9v1b07j1sy5K_OeS_QRG3FuDQ@mail.gmail.com>
References: <CACPVzBawQHXnAdSqC783b4Mkef7zxs-erqE7Ds1L+j1HctLYMg@mail.gmail.com>
	<CAJ9xe=t_KzcuP8XDqGuDdbZGc9v1b07j1sy5K_OeS_QRG3FuDQ@mail.gmail.com>

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

And while creating subscriptions on server C using server B connection
information it will be on halt state and won’t 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 “select pg_log_standby_snapshot()” 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 AM Asad Ali <[email protected]> wrote:

> Hello *Ilian Kostadinov,*
>
> Here’s 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 = logicalmax_replication_slots = <desired
> number>max_wal_senders = <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=<Server B IP>
> dbname=<dbname> user=<replication_user> password=<password>'PUBLICATION
> my_publicationWITH (copy_data = false);*
> The WITH (copy_data = 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 PM Ilian Kostadinov <
> [email protected]> 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 replica
>> without need to sync all data from the beginning?
>>
>> Best Regards,
>> Iliyan
>>
>>

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Cascading Replication
  In-Reply-To: <CAHq7gtA2B22KEZdBEZDcjQh8VRaSnD7YUBJZrNfXSSHPMRe7hw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox