AWS DMS Removing LOB Columns

Question

I'm trying to set up a Postgresql migration using the DMS to s3 as target. But after running I noticided that some tables were missing some columns. After checking the logs I noticed this message:

Column 'column_name' was removed from table definition 'schema.table': the column data type is LOB and the table has no primary key or unique index

In the settings of the task migration I tried to increase the lob limit in the option Maximum LOB size to 2000000

But still getting the same result. Does anyone know a workaround for this problem?

Answer 1

I guess, the problem is you do not have the primary key in your table. From AWS documentation:

Currently, a table must have a primary key for AWS DMS to capture LOB changes. If a table that contains LOBs doesn't have a primary key, there are several actions you can take to capture LOB changes:

• Add a primary key to the table. This can be as simple as adding an ID column and populating it with a sequence using a trigger.
• Create a materialized view of the table that includes a system-generated ID as the primary key and migrate the materialized view rather than the table.
• Create a logical standby, add a primary key to the table, and migrate from the logical standby. Learn more

It is also important to have the primary key of a simple type, not LOB:

In FULL LOB or LIMITED LOB mode, AWS DMS doesn't support replication of primary keys that are LOB data types. Learn more