AWS DMS Gotcha - Losing Column Types

AWS Database Migration Service (AWS DMS) is a managed migration and replication service that helps move your database and analytics workloads to AWS quickly, securely, and with minimal downtime and zero data loss.

Based on the above description, you might think using AWS DMS to migrate your database to RDS wouldn’t be difficult, especially if you’re going from Postgres to Postgres.

That’s what I thought, too.

You might not realize that AWS DMS translates the data types into its own internal data types for all migrations.

This conversion means you must be careful with your column types after a migration task.

I’ve seen citext (case-insensitive text) columns get lost in translation when going from just Postgres to Postgres. Errors wouldn’t appear until the application tried to use the migrated column as citext.

There’s also no json column type either.

The AWS documentation for DMS is pretty terrible, so if you were planning on using DMS to do an “easy” migration, be extra diligent.

---