DMS fails when migrating multi-tenant schemas

Question

I’m working with AWS DMS to replicate data from **Aurora PostgreSQL (RDS)** to **Amazon Redshift** using **full load + CDC**.

* **Task 1 (Single-Tenant Schema):**
  Replicates from one schema only. No new tables are added mid-replication. This task works fine.

* **Task 2 (Multi-Tenant Schemas):**
  Replicates from multiple schemas. The schemas follow the same table structure, so I use a **schema-level pattern** in my table mappings to consolidate them into one schema in Redshift (with an added `schema_id` column).
  The problem: when a new schema is created in the source Aurora PostgreSQL database, and its tables match the mapping rule pattern, the task **fails**.

**Current Configurations (Aurora parameter group):**

* `rds.logical_replication = 1`
* `synchronous_commit = on`
* `wal_sender_timeout = 0`
* `max_worker_processes = 15`
* `max_logical_replication_workers = 4`
* `autovacuum_max_workers = 3`
* `max_parallel_workers = 8`
* `rds.log_retention_period = 4320`
* `max_replication_slots = 20`
* `max_wal_senders = 10`
* `max_slot_wal_keep_size = 20000`

**Environment:**

* Aurora PostgreSQL engine version: **17.4**
* DMS instance version: **3.6.1**
* Using the **admin username** of the Aurora PostgreSQL cluster in the DMS source endpoint.

**Questions:**

1. Why does the multi-tenant task fail when a new schema/table matching the pattern is created?
2. What configuration or setup am I missing to handle this scenario more gracefully?
3. Is there a best practice for handling multi-tenant schema replication with Aurora PostgreSQL → Redshift via DMS?

re:Post Agent · Answer

The issue you're experiencing with your multi-tenant schema replication task failing when new schemas are created is related to how AWS DMS handles schema changes during ongoing replication.

The primary reason your multi-tenant task fails is that AWS DMS doesn't automatically detect and incorporate new tables or schemas that match your pattern rules after the task has started. This is a known limitation when working with pattern-based mappings.

Here are some key points to understand and potential solutions:

1. **Primary Key Requirement**: Ensure all tables in your source database have primary keys. AWS DMS ignores DELETE and UPDATE operations for tables without primary keys when using PostgreSQL as a source. This is especially important for CDC operations.

2. **Task Handling of New Objects**: By default, DMS doesn't automatically detect and start replicating new tables that match your selection patterns after the task has started. When a new schema is created that matches your pattern, the task may fail because it wasn't part of the initial metadata collection.

3. **Potential Solutions**:

a. **Reload Task Metadata**: You can stop the task, modify it to reload the table metadata, and then restart it. This will incorporate the new schemas/tables.

b. **Create Separate Tasks**: Consider having one task for initial schemas and separate tasks for new schemas as they're added.

c. **Use Task Settings for Schema Changes**: Adjust your task settings to better handle schema changes. In particular, review the `ChangeProcessingDdlHandlingPolicy` settings in your task configuration.

4. **Monitoring and Error Handling**: Implement robust monitoring using CloudWatch to detect when tasks fail due to schema changes. Set up appropriate error handling in your task settings to determine whether the task should log errors, suspend tables, or stop entirely when issues occur.

5. **Testing Strategy**: Develop a testing strategy that includes scenarios where new schemas are added during replication to validate your configuration changes.

For your specific multi-tenant architecture, consider implementing a process where you temporarily pause replication, reload metadata when new tenants are added, and then resume replication. Alternatively, you might explore using AWS DMS's table-mapping transformation rules more extensively to handle the dynamic nature of your multi-tenant environment.  
**Sources**  
[AWS DMS implementation guide: Building resilient database migrations through testing, monitoring, and SOPs | AWS Database Blog](https://aws.amazon.com/blogs/database/aws-dms-implementation-guide-building-resilient-database-migrations-through-testing-monitoring-and-sops/)  
[Using a PostgreSQL database as an AWS DMS source - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html)

DMS fails when migrating multi-tenant schemas

Add your answer

Relevant content