0

I have a use case where I am splitting one service into multiple and want to migrate tables (with huge data) from one RDS Postgres schema to another within the same DB Instance with ongoing replication and ~zero downtime, I am exploring AWS DMS service, I can see it is possible to migrate the entire DB, is it possible to migrate only a specific schema and how?

Using alter table query is not an option because I cannot move the table in one shot in production, it needs to happen gradually. An AWS DMS-like solution will fit the use-case. Thanks in advance.

Nayan Srivastava
  • 3,655
  • 3
  • 27
  • 49

1 Answers1

0

Moving a table from one schema to another schema, can be done by just altering the schema:

ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • this can move the table but for the production use case where I want to gradually turn off the traffic, moving tables in one shot would not work. – Nayan Srivastava Sep 18 '22 at 11:53
  • That's not what you asked for: "want to migrate tables (with huge data) from one RDS Postgres schema to another within the same DB Instance with ongoing replication and ~zero downtime". You're now asking for something different. So what is it that you really want? – Frank Heikens Sep 18 '22 at 12:02
  • The last has the answer for your question, "I can see it is possible to migrate the entire DB, is it possible to migrate only a specific schema and how", the question is wrt AWS DMS. Also updating my question to make it clearer. – Nayan Srivastava Sep 18 '22 at 12:06