public inbox for [email protected]help / color / mirror / Atom feed
Cascading Replication 3+ messages / 3 participants [nested] [flat]
* Cascading Replication @ 2024-08-20 11:55 Ilian Kostadinov <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ilian Kostadinov @ 2024-08-20 11:55 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cascading Replication @ 2024-08-22 11:22 Asad Ali <[email protected]> parent: Ilian Kostadinov <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Asad Ali @ 2024-08-22 11:22 UTC (permalink / raw) To: Ilian Kostadinov <[email protected]>; +Cc: [email protected] 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 > > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cascading Replication @ 2024-08-22 12:53 rams nalabolu <[email protected]> parent: Asad Ali <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: rams nalabolu @ 2024-08-22 12:53 UTC (permalink / raw) To: Asad Ali <[email protected]>; +Cc: Ilian Kostadinov <[email protected]>; [email protected] 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 >> >> ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-22 12:53 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-20 11:55 Cascading Replication Ilian Kostadinov <[email protected]> 2024-08-22 11:22 ` Asad Ali <[email protected]> 2024-08-22 12:53 ` rams nalabolu <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox