Does load-order and parallel-load work in combination in AWS DMS?

Viewed 874

I have two tables let's call it tbl_parent and tbl_child in Microsoft SQL Server, and trying to transfer the data of these tables to AWS aurora PostgreSQL.

Here tbl_child has a foreign key reference on tbl_parent, so to avoid FK issues during the full load I have mentioned higher load-order for tbl_parent than table tbl_child. The same was suggested in aws documentation.

So with that load-order setting, I was expecting that the data would only be inserted into tbl_child after the complete insertion is done in tbl_parent .

Also I want to do a parallel load into child table, so I have specified table-settings rule to perform parallel load as suggested in aws documentation

For some reason I see the data into tbl_child is getting inserted even before tbl_parent load is complete, and I'm getting Foreign key issues like below ...

2021-07-14T18:53:43 [TARGET_LOAD ]W: Load command output: psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libgssapi_krb5.so.2) psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libkrb5.so.3) ERROR: insert or update on table "tbl_child" violates foreign key constraint "tbl_child_tbl_parent_fkey" DETAIL: Key (parent_id)=(1468137) is not present in table "tbl_parent". (csv_target.c:1018)

In case if it helps please find the mapping rules json below

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "101",
            "rule-name": "101",
            "object-locator": {
                "schema-name": "dbo",
                "table-name": "tbl_parent"
            },
            "rule-action": "include",
            "filters": [],
            "load-order": 2
        },
        {
            "rule-type": "selection",
            "rule-id": "102",
            "rule-name": "102",
            "object-locator": {
                "schema-name": "dbo",
                "table-name": "tbl_child"
            },
            "rule-action": "include",
            "filters": [],
            "load-order": 1
        },
        {
            "rule-type": "table-settings",
            "rule-id": "131",
            "rule-name": "Parallel_Range_Child",
            "object-locator": {
                "schema-name": "dbo",
                "table-name": "tbl_child"
            },
            "parallel-load": {
                "type": "ranges",
                "columns": [
                    "child_id"
                ],
                "boundaries": [
                    [
                        "100"
                    ],
                    [
                        "200"
                    ],
                    [
                        "300"
                    ]
                ]
            }
        }
    ]
}
1 Answers

If you want to strictly control load order, for example, finished one table then another table, you must set maxFullloadsubtasks=1.

Actually, to avoid such issues one way is to disable FK/RI checking in target db, such options are a/v in SQL Server/MySQL/Oracle, but seems not a/v in PgSQL. Anyway your steps for full + CDC using DMS maybe:

  1. Create the basic table schema including primary key in target db
  2. DMS full load
  3. Create FK/RIs/index and other db objects
  4. DMS CDC
Related