Sample Header Ad - 728x90

Postgres logical replication and table rename

0 votes
1 answer
672 views
We have a table in main DB that is getting logically replicated to another DB called SOR. We need to rename this table, and add a bunch of tables to the publication on the main DB. I tried this procedure 1. disable the subscriber on SOR DB 2. rename the (receiving) table on SOR DB 3. rename the table on main DB, also add the tables to the publication 4. re-enable subscriber on SOR DB 5. refresh the subscription ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data=false); I waited for the replication slot on main DB to clear up, and then checked record count, SOR DB lost some data in this time window. Is there a seamless way to rename a table in logical replication without losing data on the subscriber side ? Would (copy_data=true) help ? Thank you ! some more details: 1. we use PG 14 on AWS RDS 2. The reason for this change is we are migrating a parent-child table into a partitioned table 3. The said table (to be renamed) is close to 6 TB, and the DB is our live production database, so we would like to avoid copying this table over from scratch.
Asked by hxcb (3 rep)
May 9, 2024, 04:34 PM
Last activity: May 10, 2024, 07:10 AM