AWS DMS Fails with "Command failed to load data with exit error code 1"

Question

I am trying to Migrate my Postgresql RDS to Aurora Postgresql using DMS.

All my tasks are failing, and I am unable to understand what can be done to help the tasks progress.

The error from cloud watch logs is

...
2019年05月29日T15:05:48 [TASK_MANAGER ]I: Start loading table 'public'.'my_ios_device_table' (Id = 642) by subtask 3. Start load timestamp 00058A081DCF0B40 (replicationtask_util.c:707)
2019年05月29日T15:05:48 [SOURCE_UNLOAD ]I: Calculated batch used for UNLOAD size is 1000 rows per fetch. (postgres_endpoint_unload.c:178)
2019年05月29日T15:05:48 [SOURCE_UNLOAD ]I: REPLICA IDENTITY information for table 'public'.'my_ios_device_table': Query status='Success' Type='DEFAULT' Description='Old values of the Primary Key columns (if any) will be captured.' (postgres_endpoint_unload.c:191)
2019年05月29日T15:05:49 [SOURCE_UNLOAD ]I: Unload finished for table 'public'.'my_ios_device_table' (Id = 642). 1589 rows sent. (streamcomponent.c:3392)
2019年05月29日T15:05:49 [TARGET_LOAD ]I: Load finished for table 'public'.'my_ios_device_table' (Id = 642). 1589 rows received. 0 rows skipped. Volume transfered 1558848. (streamcomponent.c:3663)
2019年05月29日T15:05:50 [TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: <truncated> [1020403] (csv_target.c:981)
2019年05月29日T15:05:50 [TARGET_LOAD ]E: Failed to wait for previous run [1020403] (csv_target.c:1578)
2019年05月29日T15:05:50 [TARGET_LOAD ]E: Failed to load data from csv file. [1020403] (odbc_endpoint_imp.c:5648)
2019年05月29日T15:05:50 [TARGET_LOAD ]E: Handling End of table 'public'.'my_ios_device_table' loading failed by subtask 3 thread 1 [1020403] (endpointshell.c:2416)
2019年05月29日T15:05:50 [TASK_MANAGER ]W: Table 'public'.'my_ios_device_table' (subtask 3 thread 1) is suspended (replicationtask.c:2356)
...

I have checked the Postgresql Roles, Privileges and even disabled triggers using the SET session_replication_role = 'replica';.

I am at my wits end as to why this is failing and how to rectify it.

I am migrating from RDS Postgres 9.5.16 to Aurora Postgres 10.7

Any help is appreciated. Thanks

DMS Task Details

• TurboGus: Have you checked the logs on the RDS instance? I was having the same errors and the RDS instance log showed me that DMS converted my geometry to varchar 255 and the values were too big.
• gvatreya: Yes - I did. We saw that some indices failed to migrate - Any combinations I tried didn't help. So we did the next best thing - use pg_dump with the migration and creating an Aurora from Postgresql snapshot. We gave up on using DMS, because of this.

Answer 1

I encountered the "Command failed to load data with exit error code 1" error while using AWS Database Migration Service (DMS). Upon enabling CloudWatch Logs for my PostgreSQL Aurora RDS cluster, I discovered that DMS was attempting to convert a column with an array data type into varchar during the migration process. Unfortunately, DMS does not support array data types directly.

To resolve this issue, I used a table transformation rule in DMS. Specifically, I changed the data type of the problematic column to CLOB (Character Large Object). This change ensured that DMS could handle the data properly during migration, ultimately resolving the error.

Enabling CloudWatch Logs and carefully reviewing the logs proved crucial in identifying the exact nature of the data type mismatch and applying the appropriate transformation to mitigate it. If you encounter similar issues with DMS, consider leveraging table transformation rules to align data types between your source and target databases effectively

Answer 2

Error:Command failed to load data with exit error code 0 and exitwhy 1

I encountered an issue with a JSONB column in the table, which I resolved by adding MapJsonbAsClob=true; as an attribute in the source endpoint. This setting treats JSONB data as NCLOB, ensuring proper handling.

For more details, refer to the AWS DMS documentation:

https://docs.aws.amazon.com/dms/latest/APIReference/API_PostgreSQLSettings.html https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Prerequisites