I want to move my Oracle database schema to PostgreSQL, but the casing is not compatible. What are my options?

Oracle stores metadata in its data dictionary in uppercase, and PostgreSQL stores it in lowercase. With either database, it is possible to override these default formats by encasing object names in quotes when they are created. However this is not recommended because it can cause unexpected behavior. Because the AWS Database Migration Service cannot auto correct metadata storage formatting, it creates the objects in quotes. As a workaround, you can override DMS quotes with mapping rules.

In this example, we are migrating the Oracle SCOTT schema to PostgreSQL. If you have an on-premises Oracle database, you can create this schema by running:

$ORACLE_HOME/rdbms/admin/utlsampl.sql

If you have an Amazon RDS Oracle DB instance, you can do an online search for a similar script. It creates 4 tables under the SCOTT schema: EMP, DEPT, BONUS, and SALGRADE. Collectively they contain a small number of rows.

Verify that no tables in the Oracle database were created with any lowercase letters in the table or column names. If the application is accustomed to mixed-case table and column names, it's best to allow DMS to replicate the tables exactly as it finds them (that is, don't use these mapping rules).

oracle> SELECT table_name from dba_tables where owner='SCOTT' and table_name <> upper(table_name);

oracle> SELECT table_name, column_name from dba_tab_columns where owner='SCOTT' and column_name <> upper(column_name);

Create endpoints, replication instance, and test connection:

  1. From the AWS DMS console or using the AWS CLI, create the endpoints and replication instance.
  2. Choose Test connection for both endpoints.
  3. If both test connections are successful, proceed with creating the task. Be sure to choose Enable Logging.
  4. At the bottom of the Table mappings section, select Custom and use code similar to the following example to build the transformation rules for schema, tables, and column case handling; then choose Create Task.

{

    "rules": [

        {

            "rule-type": "selection",

            "rule-id": "1",

            "rule-name": "select-scott",

            "object-locator": {

                "schema-name": "SCOTT",

                "table-name": "%"

            },

            "rule-action": "include"

        },

        {

            "rule-type": "transformation",

            "rule-id": "2",

            "rule-name": "convert-schemas-to-lower",

            "rule-action": "convert-lowercase",

            "rule-target": "schema",

            "object-locator": {

                "schema-name": "%"

            }

        },

        {

            "rule-type": "transformation",

            "rule-id": "3",

            "rule-name": "convert-tables-to-lower",

            "rule-action": "convert-lowercase",

            "rule-target": "table",

            "object-locator": {

                "schema-name": "%",

                "table-name": "%"

            }

        },

        {

            "rule-type": "transformation",

            "rule-id": "4",

            "rule-name": "convert-columns-to-lowercase",

            "rule-action": "convert-lowercase",

            "rule-target": "column",

            "object-locator": {

                "schema-name": "%",

                "table-name": "%",

                "column-name": "%"

            }

        }

    ]

}

Note: You can also use a rename schema rule instead of converting the schema to lowercase if you prefer.

When completed, verify that all objects were created in lowercase in PostgreSQL:

postgres> select table_name from information_schema.tables where table_schema='scott';

 table_name

------------

 bonus

 dept

 salgrade

 emp

(4 rows)

postgres> select table_name, column_name from information_schema.columns    where table_schema='scott' order by table_name;

 table_name | column_name

------------+-------------

 bonus      | ename

 bonus      | job

 bonus      | sal

 bonus      | comm

 dept       | deptno

 dept       | dname

 dept       | loc

 emp        | deptno

 emp        | hiredate

 emp        | sal

 emp        | comm

 emp        | empno

 emp        | ename

 emp        | job

 emp        | mgr

 salgrade   | losal

 salgrade   | hisal

 salgrade   | grade

(18 rows)

AWS Database Migration Service, AWS DMS, Oracle, PostgreSQL


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-08-26