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's possible to override these default formats by encasing object names in quotes when they are created. However this isn't recommended, because doing so can cause unexpected behavior. Because the AWS Database Migration Service (AWS DMS) can't autocorrect metadata storage formatting, AWS DMS creates the objects in quotes. As a workaround, you can override AWS DMS quotes with mapping rules. For more information, see Use the AWS Schema Conversion Tool (AWS SCT) to Convert the Oracle Schema to PostgreSQL.

In this example, the Oracle SCOTT schema is being migrated 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 Relational Database Service (Amazon RDS) DB instance that is running Oracle, search online for a similar script.

This script 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 lowercase letters in the table or column names. If the application is accustomed to mixed-case table and column names, it's a best practice to allow AWS 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, a replication instance, and then test the connection:

1.    Open the AWS DMS console or using the AWS Command Line Interface (AWS CLI), create the endpoints and then create the replication instance.

2.    Choose Endpoints from the navigation pane, select both endpoints, and choose Test connection.

3.    If both test connections are successful, then create a task. Be sure to choose Enable logging.

4.    In the Table mappings view, select the JSON tab, and then choose Enable JSON editing. Then, use code similar to the following example to build the transformation rules for schema, tables, and column case handling. For more information, see Using Table Mapping to Specify Task Settings.

{
  "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": "%"
      }
    }
  ]
}

5.    Choose Create task.

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

After the task is created, verify that all the 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)

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

Updated: 2018-02-04