Automated regular sync between an amazon RDS test database and prod database

Question

We have a prod database which is an Amazon RDS instance, i wanted to setup a test database RDS instance which regularly is updated with the prod database on a weekly basis. I read that you can take automated snapshots of a database, so in this case how can we apply those snapshots to the test database in an automated manner?

Or if not snapshots, then is there a better way to sync a test or any other database with a prod database in Amazon RDS?

Answer 1

So after spending quite some time on AWS's documentation i found a way to have data replicated across databases. For my case above, the best service is DMS or Data migration service, docs here. I used the below steps to solve the problem

1. Created a migration task in DMS for migrating the entire data from prod database to test.
2. Created a change data capture task in DMS for migrating any changes from the prod database to test. This step will ensure any ongoing changes to prod database are migrated as well after the step 1.

Note: For step 2, your source database must have the right plugins which store the changes to your database e.g. for postgres one of the plugins is pglogical

The AWS's documentation lists all the knowledge that you need to migrate, in my case the migration was homogenous i.e. the source and test database engines were the same so it was not tedious.