How to migrate tables with defaults, constraints and sequences with AWS DMS for postgres to postgres migration?

Question

I recently did a migration from a RDS postgresql to Aurora postgresql. The tables were migrated successfully but the tables are missing their defaults, constraints and references. It also did not migrate any sequences.

Table in source database:

                                        Table "public.addons_snack"
    Column     |           Type           | Collation | Nullable |                 Default
---------------+--------------------------+-----------+----------+------------------------------------------
 id            | integer                  |           | not null | nextval('addons_snack_id_seq'::regclass)
 name          | character varying(100)   |           | not null |
 snack_type    | character varying(2)     |           | not null |
 price         | integer                  |           | not null |
 created       | timestamp with time zone |           | not null |
 modified      | timestamp with time zone |           | not null |
 date          | date                     |           |          |
Indexes:
    "addons_snack_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "addons_snack_price_check" CHECK (price >= 0)
Referenced by:
    TABLE "addons_snackreservation" CONSTRAINT "addons_snackreservation_snack_id_373507cf_fk_addons_snack_id" FOREIGN KEY (snack_id) REFERENCES addons_snack(id) DEFERRABLE INITIALLY DEFERRED

Tables in target database

                         Table "public.addons_snack"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 id            | integer                     |           | not null |
 name          | character varying(100)      |           | not null |
 snack_type    | character varying(2)        |           | not null |
 price         | integer                     |           | not null |
 created       | timestamp(6) with time zone |           | not null |
 modified      | timestamp(6) with time zone |           | not null |
 date          | date                        |           |          |
Indexes:
    "addons_snack_pkey" PRIMARY KEY, btree (id)

Did I do something wrong or DMS is not capable of doing this?

Archdeacon

Answer 1

This SQL Snippet will be a clear answer for you.

You can restore Index and Constraint by using pg_dump and pg_restore, and the snippet consists of executing them.

Lightfoot

Answer 2

I am currently migrating a Django project (or really just finished migrating) from a Postgres DB that is on an EC2 instance to RDS Aurora and didn't have luck with @sinwoobang's answer. We are using a custom function for generating IDs and trying to get everything in place after the fact caused more problems than it was solving.

What I ended up doing was running the Django migrations against the new database so that all the constraints, sequences, etc were all in place and then determining which tables had dependencies on other tables. I started a table per DMS task at a time and used the Table preparation mode of "Do nothing" so that data would just get inserted into the table.

If you use the truncate table option it will fail on tables that are referenced by other tables even if there is no data.

Then it was just doing a table or set of tables at a time depending on how they were dependent on each other.

There were a few cases where I needed to remove a FK constraint before doing it and add it back in like in the case where we had a circular reference between two tables or a recursive reference on a table.

It was pretty tedious but I was able to do it within the time frame of 2 work days which isn't too bad for a DB that was almost 500GB in size.

I did use the limited LOB mode and depending on the table I changed the amount up to 32768 KB to make sure that embedded JSON data got copied over.

I also left off the batching setting as that seemed to cause some tables to fail.

Jibe