1

I a trying to do the migration for our Postgres database to Aurora postgres first I create a normal task it migrates all tables only except its constraints.

My tries to clone our database

I downloaded AWS SCT (Schema Conversion Tool) then set my configuration to generate a migration report, here is the report

We completed the analysis of your PostgreSQL source database and estimate that 100% of the database storage objects and 99.1% of database code objects can be converted automatically or with minimal changes if you select Amazon Aurora (PostgreSQL compatible) as your migration target. Database storage objects include schemas, tables, table constraints, indexes, types, sequences and foreign tables. Database code objects include triggers, views, materialized views, functions, domains, rules, operators, collations, fts configurations, fts dictionaries and aggregates. Based on the source code syntax analysis, we estimate 99.9% (based on # lines of code) of your code can be converted to Amazon Aurora (PostgreSQL compatible) automatically. To complete the migration, we recommend 133 conversion action(s) ranging from simple tasks to medium-complexity actions to complex conversion actions.

my question:

1- is there a way to automate including everything in my source database

2- the report mentions we recommend 133 conversion action(s) where I can find these conversion actions.

3- is it safe to ongoing migration as in my case we need to run migration every day.

Mina Fawzy
  • 20,852
  • 17
  • 133
  • 156

2 Answers2

2

Sequence, Index, and Constraint are not migrated and it is mentioned in the official docs on AWS.

You can use this source. This will help you to migrate Sequence, Index, and Constraint at once.

p.s: this doesn't include View and Routine.

sinwoobang
  • 138
  • 10
1

  • There's no way AFAIK in AWS to automate everything if that was there then it would have been already added in SCT. however, if there are similar errors that are occurring in code/DDL/function like some datatype conversions. you can create a script that will take schema dump and convert all these data types to the desired ones.

  • Choose the SQL Conversion Actions tab in SCT tool. The SQL Conversion Actions tab contains a list of SQL code items that can't be converted automatically. There are also recommendations for how to manually convert the SQL code. You can look into the errors and make changes accordingly.

In case if you are migrating to the same version of PG in aurora you can take a schema only dump and restore it into target aurora and later setup a full load/ongoing replication with DMS and you don't have to take SCT into consideration(most of the time worked for me). Just make sure you adhere to aurora limitations specific to the PG version

  • We have been using ongoing migration in our project at it's working great. There are some best practices we have developed but that will differ from project to project

    1. DDL changes must be made on the target first and stop replication while doing it and resume once done
    2. Separate the tables with high transactions as different DMS task as it will help you in troubleshooting and your rest of the tables can still be working
    3. Always keep in mind DMS replicates data, not the view/function/procedures
    4. Active monitoring of tasks and replication instances

And I would like to suggest if you are performing homogenous migration(PG -> PG) you should consider pg_dump & pg_restore that easy and sophisticated for the same versions and AWS aurora supports it.

Nikhil B
  • 353
  • 2
  • 7