AWS / PostgreSQL

AWS DMS – Oracle to PostgreSQL and Object Case Issues

One of the differences between Oracle and PostgreSQL is the default behaviour of identifiers when they are not quoted. In Oracle it folds to uppercase (SQL Standard) while in PostgreSQL it folds to lowercase.

So when you migrate from Oracle to PostgreSQL with AWS DMS, the DMS wraps the DDL with quotes and makes the PostgreSQL objects case sensitive. It becomes an issue as instead of running your queries with:

you now have to quote the identifiers so it looks like:

I’m not a fan of case sensitive identifiers so thankfully the DMS has table mappings which can perform transformations on columns, tables and schema.

In my Oracle to PostgreSQL migration scenario I needed to migrate a schema and all its tables and objects. The steps I used to achieve this are as follows:

  • AWS Schema Conversion Tool to identify compatibility
  • AWS Schema Conversion Tool to generate DDL for indexes and other objects
  • Manipulate this file to suit my requirements
  • Create the JSON file with the appropriate rules for the migration. See code block below
  • Create the DMS task in console and paste in custom table mappings and run
  • In Oracle user and schema are synonymous. The user gives you login ability and you can also store objects within that “user” namespace. You also have the flexibility to separate this out with multiple users accessing a schema with different permission models and so forth. In PostgreSQL a user is a role and schema is a namespace to store objects. So when migrating to PostgreSQL you may need to add an extra layer as you cannot use the schema as a login role to access migrated data.

    My solution to this was to migrate the Oracle schema as a different name into PostgreSQL and then create a role with appropriate permissions to the new schema in PostgreSQL with the same name as the Oracle user.

    My lowercase table mapping JSON which migrates an Oracle Schema and converts it all to lowercase and renames the schema looks like:

    This was a pretty small database and the migration complete within 10 minutes ready for testing.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.