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

Viewed 357

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?

1 Answers

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.

Related