0

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.
hxcb
  • 3
  • 2

1 Answers1

0

You should rename the table on the publisher first, then add the renamed table to the publication. Then run ALTER SUBSCRIPTION ... REFRESH PUBLICATION WITH (copy_data = off) on the subscriber and wait until you get a replication conflict because the renamed table does not exist on the subscriber. Then rename the table on the subscriber, and logical replication should be able to catch up.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90