AWS Data Migration Service Not Capturing Partitioned

Question

Description

I have a postgreSQL Partitioned Table

shippo=> \d+ table_name
                                                           Partitioned table "public.table_name"
-- skipping column definitions

Partitions: table_name_201909 FOR VALUES FROM ('2019-09-01 00:00:00+00') TO ('2019-10-01 00:00:00+00'),
            table_name_201910 FOR VALUES FROM ('2019-10-01 00:00:00+00') TO ('2019-11-01 00:00:00+00'),

And a AWS DMS Database Migration Task Mapping Rules configuration

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": 29446,
            "rule-name": "replicate_table_name",
            "object-locator": {
                "schema-name": "public",
                "table-name": "table_name"
            },
            "rule-action": "include",
            "filters": []
        }
    ]
}

Running in a Change Data Capture only task (no full load, only ongoing replication) that's targeted at an S3 bucket for it's endpoint.

The issue is that DMS sees a set of changes incoming (from a test population script) but no changes are replicated to the S3 bucket.

I can replicate data from normal tables just fine. It's only the partitioned table that doesn't replicate data.

The aws docs state

• AWS DMS doesn't support replication of partitioned tables. When a partitioned table is detected, the following occurs:

  • the endpoint reports a list of parent and child tables.

  • AWS DMS creates the table on the target as a regular table with the same properties as the selected tables.

  • If the parent table in the source database has the same primary key value as its child tables, a "duplicate key" error is generated.

But they don't walk you through the process of configuring a task to accept output from a partitioned table. How do you do this?

Answer 1

The core issue here's that the mapping rules

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": 29446,
            "rule-name": "replicate_table_name",
            "object-locator": {
                "schema-name": "public",
                "table-name": "table_name"
            },
            "rule-action": "include",
            "filters": []
        }
    ]
}

Need to match the partitions table format and not the parent table's format

"table-name": "table_name_%"

After this modification CDC data started showing up in the S3 bucket as expected.