Can AWS DMS detect new PostgreSQL table partitions without restart?

Question

I have implemented a AWS DMS task to replicate RDS PostgreSQL (source) changes to Kinesis Data Streams (target). At the moment, the task is responsible for only replicating a single table, which is a **partitioned table** in PostgreSQL. These are the task mapping rules:

```json
{
    "rules": [
        {
            "mapping-parameters": {
                "partition-key-type": "primary-key"
            },
            "object-locator": {
                "schema-name": "public",
                "table-name": "sales_p_%"
            },
            "rule-action": "include",
            "rule-id": "1",
            "rule-name": "1",
            "rule-type": "selection"
        }
    ]
}
```

We have triggers to create new `sales` partitions in our application. However, when a new partition is created for this table, DMS doesn't automatically detect it and start the CDC replication. The new partition is not listed in the "Table statistics" tab for the task, and inserts/updates for the new partition are not sent to Kinesis.

As soon as the DMS task is stopped and then resumed, the new partitioned table is discovered, and ongoing replication starts working.

Is AWS DMS capable of discovering new tables matching an `include` table name, without needing to create an automated process that stops/resumes the task as a workaround?

Priyanka_Sanghvi · Answer

AWS DMS does not automatically detect new partitions created for a partitioned table, even if the table name matches an include filter. When a new partition is created in PostgreSQL, AWS DMS needs to be aware of this new table (partition), which doesn’t happen dynamically. This is why your DMS task is not capturing the changes to the new partition until the task is stopped and resumed.
Stopping and resuming the task forces AWS DMS to re-evaluate the list of tables, including any new partitions created since the task was started.

Unfortunately, as of now, AWS DMS does not provide a native feature that automatically discovers and includes new partitions in ongoing replication without stopping and restarting the task.
As a potential workaround, you would indeed need to create an automated process that monitors for new partition creation and stops/resumes the DMS task accordingly. Another approach could be managing partitions more explicitly in your DMS task configuration, but that still wouldn’t bypass the need for restarting the task when a new partition is added.
This [Blog](https://aws.amazon.com/blogs/database/migrate-data-from-partitioned-tables-in-postgresql-using-aws-dms/) outlines similar limitations and suggests strategies like predefining partitions or managing table mappings in such a way that all partitions are included from the start, but new partitions will still require manual intervention or automation to be included in replication.

Can AWS DMS detect new PostgreSQL table partitions without restart?

Add your answer

Relevant content