CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-auroraaws-dmsaws-sct
Mina Fawzy
Mina Fawzy

Reputation: 21472

AWS - DMS migration missing sequence , views , routines ... etc

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.

Upvotes: 1

Views: 2506

Answers (3)

Yogesh Gunasekaran
Yogesh Gunasekaran

Reputation: 21

To Migrate Database Schema with Indexes using AWS DMS:

Problem Statement:

I'm encountering a scenario where AWS DMS migration doesn't automatically create indexes during schema replication. While "Drop table on target" is an option, it can lead to data loss. Here's a refined approach to achieve successful migration with existing indexes:

Solution:

  1. Pre-Migration Schema Creation:

    • Target Database: Create the target database schema beforehand, including tables, indexes, and routines. Ensure the schema structure (data types, constraints) aligns with your source database.

  2. AWS DMS Task Configuration:

    • Open the AWS DMS console and navigate to "Create task."
    • In the "Task settings" section, configure the source and target database connections.
    • Under "Table mappings," define how tables from the source will be mapped to the target.
    • Crucially, set the "Table preparation mode" to either "Do nothing" or "Truncate".
      • "Do nothing" preserves existing data in the target tables, but you'll need to ensure they're empty before starting the migration to avoid data duplication.
      • "Truncate" removes all existing data from the target tables while maintaining the schema structure (including indexes).

  3. Initiate Migration:

    • Once you've configured the task with the desired table preparation mode, start the DMS migration process. AWS DMS will replicate data from the source to the target tables, respecting the existing indexes on the target.

Upvotes: 2

sinwoobang
sinwoobang

Reputation: 138

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.

Upvotes: 2

Nikhil B
Nikhil B

Reputation: 393

  • 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.

Upvotes: 1

Related Questions