postgresqlaws-dms

invalid input syntax for type json aws dms postgres


I'm running a task that migrates all data from a postgres 10.4 to a RDS postgres 10.4. Not able to migrate tables which have jsonb column. After error, whole table is getting suspended.Table contain 449 rows only.

I have made following error policy, still whole table suspended. "DataErrorPolicy": "IGNORE_RECORD", "DataTruncationErrorPolicy": "IGNORE_RECORD", "DataErrorEscalationPolicy": "SUSPEND_TABLE", "DataErrorEscalationCount": 1000,

My expectation is that whole table should be transferred, it can ignore record if any json is wrong. I dont know why its giving this error 'invalid input syntax for type json' , i have checked all json and all jsons are valid.

After debugging more, this error has been considered as TABLE error , but why ? Thats why table got suspended since TableErrorPolicy is 'SUSPEND_TABLE'. Why this error considered as table error instead of record error?

Is JSONB column not supported by DMS thats why we are getting below error?

Logs :-

2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Next table to load 'public'.'TEMP_TABLE' ID = 1, order = 0 (tasktablesmanager.c:1817)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Start loading table 'public'.'TEMP_TABLE' (Id = 1) by subtask 1. 
Start load timestamp 0005AE3F66381F0F (replicationtask_util.c:755)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: REPLICA IDENTITY information for table 'public'.'TEMP_TABLE': Query status='Success' Type='DEFAULT' 
Description='Old values of the Primary Key columns (if any) will be captured.' (postgres_endpoint_unload.c:191)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Unload finished for table 'public'.'TEMP_TABLE' (Id = 1). 449 rows sent. (streamcomponent.c:3485)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Table 'public'.'TEMP_TABLE' contains LOB columns, change working mode to default mode (odbc_endpoint_imp.c:4775)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Table 'public'.'TEMP_TABLE' has Non-Optimized Full LOB Support (odbc_endpoint_imp.c:4788)
2020-09-01T12:10:04 https://forums.aws.amazon.com/I: Load finished for table 'public'.'TEMP_TABLE' (Id = 1). 449 rows received. 0 rows skipped.
Volume transferred 190376. (streamcomponent.c:3770)
2020-09-01T12:10:04 https://forums.aws.amazon.com/E: RetCode: SQL_ERROR SqlState: 22P02 NativeError: 1 Message: ERROR: invalid input syntax for type json; 
Error while executing the query https://forums.aws.amazon.com/ (ar_odbc_stmt.c:2648)
2020-09-01T12:10:04 https://forums.aws.amazon.com/W: Table 'public'.'TEMP_TABLE' (subtask 1 thread 1) is suspended (replicationtask.c:2471)

Edit- after debugging more, this error has been considered as TABLE error , but why ?


Solution

  • JSONB column data type must be nullable in target DB.

    Note- In my case, after making JSONB column as nullable, this error disappeared.

    As mentioned in AWS documentation-

    In this case, AWS DMS treats JSONB data as if it were a LOB column. During the full load phase of a migration, the target column must be nullable.

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

    https://aws.amazon.com/premiumsupport/knowledge-center/dms-error-null-value-column/