By using AWS re:Post, you agree to the Terms of Use

Does AWS DMS support ARRAY data type for RDS for PostgreSQL on EC2 to Aurora PostgreSQL migration?


I am currently migrating an Amazon RDS for PostgreSQL database from Amazon EC2 to Amazon Aurora PostgreSQL-Compatible Edition. I am using AWS DMS and have encountered the following issue: One of the columns in a particular table stores the values of water pressure measured within a second. This column is an array of decimal numbers (Ex: {2.44, 5.66, 8.55}). I received the following error message during the migration from AWS DMS: "1 unsupported data type '_float4' on table 'data1', column 'pressure'". Does AWS DMS support ARRAY data type for double or floating point numbers? The AWS documentation indicates that the arrays can't be migrated. However, further down on the same page, it's mentioned that AWS DMS supports arrays from a source RDS for PostgreSQL database and that arrays are mapped to CLOBs in AWS DMS. I'm looking for some guidance on whether ARRAY data type is supported by AWS DMS during migration. The reports return the following error:

Note: You can see that the pressure column is indicated with real[] .

1 unsupported data type '_float4' on table 'data1', column 'pressure'

pipeminder=# \d data1 Table "public.data1" Column | Type | Modifiers ---------------+--------------------------+----------- device_id | bigint | not null timestamp | timestamp with time zone | not null pressure | real[] | not null pressure_min | real | not null pressure_mean | real | not null pressure_max | real | not null flow | real | not null Indexes: "data1_unique_device_time" UNIQUE CONSTRAINT, btree (device_id, "timestamp")

1 Answer
Accepted Answer

If your RDS for PostgreSQL version on Amazon EC2 is PostgreSQL 10 or higher, then you can use the native logical replication to Aurora. In this case, you don't need to use AWS DMS. For more information, see Migrating PostgreSQL from on-premises or Amazon EC2 to Amazon RDS using logical replication. The native logical replication handles all native PostgreSQL data types, including arrays. If the source database is PostgreSQL 9.4, 9.5, or 9.6, then you can use the pglogical extension. Pglogical extension can handle arrays as well.

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions