I'm having a Postgres RDS database that have many schemas in it. I want to move only certain schemas into another AWS account's RDS. The schemas I'm looking at to transfer is only approx 12GB.
I'm looking for a minimal downtime method that make use of CDC such as DMS rather than the traditional export-and-restore. That's also what I'm doing with MongoDB. Add some slaves to the set, wait for it to sync, promote a new master once sync up-to-date, terminate the old ones.
So is it possible to sync only certain schemas, Postgres RDS to Postgres RDS, using AWS DMS. Thanks.
Just curious, if using Aurora, have you looked into cross account synchronization using logical replication?
Using DMS, you can include/exclude based on one or more schemas. Table mapping rules can be applied to a source or target endpoint of a Data Migration Task.
If you wanted to sync only tables in a single schema, simply create a rule-type of selection and define the schema in the object-locator section the selection criteria. You can use the "%" to include all tables in the schema.
"rules":
[
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "include-public-schema-only",
"object-locator": {
"schema-name": "public",
"table-name": "%"
},
"rule-action": "include",
"filters": []
},
...
]
You can read more about the mapping rules and how to apply in the aws documentation.
If minimal downtime is your ultimate goal, the fastest way to get data to the target would be to upload a backup, restore at the target. Then set the DMS Task's (CDC) recovery checkpoint to correspond with the backup checkpoint of the source.