How to resolve "Unsupported data types Failed" issue when using AWS DMS service, where _varchar and _text types are not being recognized on PostgreSQL?

Question

I'm facing a problem using the AWS DMS (Database Migration Service) service to migrate data from a PostgreSQL database to another PostgreSQL database(we are encrypting our current database). When performing the premigration assessment, I get an error message "Unsupported data types Failed". It looks like the _varchar and _text data types are not being recognized correctly during the migration process. I would like to know how I can resolve this issue and make these data types correctly recognized by the AWS DMS service when migrating from one PostgreSQL database to another. We also noticed that the _varchar and the _text is an array definition.

The migration type used is "Migrate existing data and replicate ongoing changes".

We like to resolve this issue without changing the source and target database.

The migration type used is "Migrate existing data and replicate ongoing changes".

We like to resolve this issue without changing the source and target database.

Answer

AWS DMS (Database Migration Service) supports many of the PostgreSQL data types for migration, but some of them are unsupported.

in this article you can find which data types supported https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html
as you can see varchar migrates partially

as workaround

If you must use DMS and the amount of data is not huge, you could consider creating views on the source database that transform the arrays into a format DMS can handle, like JSON or comma-separated strings, then reverse the process on the target.

You might consider other migration tools or services that support PostgreSQL array data types. These could be open source tools, other cloud provider services, or specialized database migration software.

How to resolve "Unsupported data types Failed" issue when using AWS DMS service, where _varchar and _text types are not being recognized on PostgreSQL?

Relevant content