AWS DMS and RDS

Question

Hello team,

I ahve an AWS RDS for PostgreSQL (Version 13.15). An AWS DMS job moves data from RDS to AWS Redshift. I executed an SQL to alter one column of a table from int to bigint.

```
alter table mytable  alter column mycolumn type bigint;
```
This executed in the RDS without any issue. But the DMS job failed .

`2025-04-30T04:11:34 [TARGET_APPLY    ]D:  Adding columns to table_def public.mytable  (odbc_util.c:1315)
2025-04-30T04:11:34 [TARGET_APPLY    ]D:  Modify column statement: ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" INT8  (provider_syntax_manager.c:2437)
2025-04-30T04:11:34 [TARGET_APPLY    ]I:  Alter table command captured. Action: 1, statement: ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" INT8  (cloud_imp.c:3326)
2025-04-30T04:11:34 [TARGET_APPLY    ]E:  Failed to get DDL template. Action: 1, table: 'public.mytable' id :'179, statement: ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" INT8 [1020409]  (cloud_imp.c:3330)
2025-04-30T04:11:34 [TARGET_APPLY    ]I:  Received records to load or apply on target, reading data from upstream. The last context is  {operation:DDL_TABLE_ALTER (23), tableName:mytable, schemaName:public, txnId:-265908027, streamPosition:0000C9CC/552F91B0.2.0000C9CC/555C59C8}  (streamcomponent.c:2006)
2025-04-30T04:11:34 [TASK_MANAGER    ]W:  Table 'public'.'mytable' was errored/suspended (subtask 0 thread 1).   (replicationtask.c:3081)`
What would have caused this? How can this be fixed?

re:Post Agent · Answer

The issue you're experiencing is likely due to AWS DMS not being able to properly handle the DDL (Data Definition Language) change you made to your table in the source RDS PostgreSQL database. When you alter a column's data type, DMS needs to capture and apply this change to the target (in this case, Redshift). However, it seems that DMS encountered difficulties processing this specific ALTER TABLE statement.

To resolve this issue, you can try the following approaches:

1. Refresh the table metadata: Sometimes, DMS may not immediately recognize schema changes. You can try refreshing the table metadata in your DMS task. This can be done by:
   - Stopping the task
   - Modifying the task and selecting the option to reload tables
   - Restarting the task

2. Update DMS task settings: Ensure that your DMS task is configured to handle DDL changes. In your task settings, check the "Change Processing DDL Handling Policy" section and make sure options like "Handle Source Table Altered" are enabled.

3. Manual intervention: If the automatic handling doesn't work, you might need to manually alter the table in the target Redshift database to match the new schema, and then restart the DMS task.

4. Review DMS version: Ensure you're using the latest version of AWS DMS, as newer versions often include improvements in handling various data types and DDL changes.

5. Check for limitations: Review the DMS documentation for any limitations related to altering column data types, especially when migrating from PostgreSQL to Redshift.

6. Consider using schema conversion: If you frequently make schema changes, you might want to explore using AWS Schema Conversion Tool (SCT) in conjunction with DMS to handle complex schema transformations.

If these steps don't resolve the issue, you may need to reach out to AWS Support for further assistance. They can provide more detailed, account-specific troubleshooting based on your exact DMS task configuration and logs.  
**Sources**  
[Issue with AWS DMS Full Load + CDC Task Not Reflecting Newly Created Tables in Target DB | AWS re:Post](https://repost.aws/questions/QUecWIOaAcTbWpF85GBwx9XA/issue-with-aws-dms-full-load-cdc-task-not-reflecting-newly-created-tables-in-target-db)  
[DMS replication error:  malformed array literal | AWS re:Post](https://repost.aws/questions/QUf0W_akyWQKq3XuN-zTZG7A/dms-replication-error-malformed-array-literal)

AWS DMS and RDS

Relevant content