Re: Cascading Replication

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 <asadalinagri(at)gmail(dot)com> 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 <
> ilian(dot)kostadinov(at)gmail(dot)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 replica
>> without need to sync all data from the beginning?
>>
>> Best Regards,
>> Iliyan
>>
>>