RDS Oracle to RDS postgres using AWS DMS

Using this migration we can migrate data from postgres database to oracle database seamlessly , In this use case we have taken RDS on the both ends for migration.

Pre-requisites

As we consider postgres and oracle for migration we need both instance available and able to connect aws dms

Create a Replication Instance

AWS Database Migration Service (DMS) uses an intermediary resource called a Replication Instance to migrate data from a source database to a target. A replication instance can be thought of as a specialized compute resource, similar to an EC2 instance, that has been optimized for database migration workloads.

Follow the below steps to create a Replication Instance.

Thus, Replication Instance is created as you see below.

Create the Source EndPoint

Let’s create the Source Endpoint in AWS DMS. Follow the below steps to create a Source Endpoint for the Migration.

Thus, the Source Endpoint is created successfully, as you see below.

Create the Target Endpoint

Let’s create the Target Endpoint in AWS DMS. Follow the below steps to create a Source Endpoint for the Migration.

Thus, the Target Endpoint is created successfully, as you see below.

If the schemas you are migrating do not exist on the PostgreSQL database, then create the schemas.

postgres=> create schema oraclesource;
CREATE SCHEMA
postgres=> \dn
     List of schemas
     Name     |  Owner
--------------+----------
 oraclesource | postgres
 public       | postgres
(2 rows)
postgres=>        

Create the Migration Task

Let’s create the Migration Task with the below Steps.

The following is an example of mappings that convert schema names and table names to lowercase.

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "100000",
      "rule-name": "Default Lowercase Table Rule",
      "rule-action": "convert-lowercase",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "100001",
      "rule-name": "Default Lowercase Schema Rule",
      "rule-action": "convert-lowercase",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "%"
      }
    }
  ]
}

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "100001",
      "rule-name": "Default Lowercase Schema Rule",
      "rule-action": "convert-lowercase",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "ORACLESOURCE"
        "table-name":"%"
        }
      "rule-action": "convert-lowercase",
      "value": null,
      "old-value": null     
  ]
}        

The following is an example of a Selection Rule which you can run instead of GUI mode.

{
   "rules": [
        {
           "rule-type": "selection",
           "rule-id": "3",
           "rule-name": "3",
           "object-locator": {
               "schema-name": "oraclesource",
               "table-name": "%",
               "table-type": "all"
            },
           "rule-action": "include"
        }
    ]
}        

Use above rules in json. otherwise, choose the GUI for Selection and Transformation rules as below,

Thus, the task is ready to run now, as you see below.

Run the Migration Task

Run the Pre-Migration Task before running the actual Task.

From the below picture, we could see that our ‘Premigration Assessment’ is passed.

Let’s run the original Task.

Data Migration from Oracle to PostgreSQL RDS is completed and below is the Task output,

Verify Postgres Database after Migration

Let’s verify the target PostgreSQL Database after Migration,

postgres=> \dn
     List of schemas
     Name     |  Owner
--------------+----------
 oraclesource | postgres
 public       | postgres
(2 rows)

postgres=> show search_path ;
 search_path
--------------
 oraclesource
(1 row)

postgres=> \dt+ big_table
                         List of relations
    Schema    |   Name    | Type  |  Owner   |  Size  | Description
--------------+-----------+-------+----------+--------+-------------
 oraclesource | big_table | table | postgres | 152 kB |
(1 row)

postgres=> select count(*) from big_table ;
 count
-------
  1000
(1 row)

postgres=>postgres=> SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'big_table';
  column_name   |          data_type
----------------+-----------------------------
 id             | numeric
 owner          | character varying
 object_name    | character varying
 subobject_name | character varying
 object_id      | numeric
 data_object_id | numeric
 object_type    | character varying
 created        | timestamp without time zone
 last_ddl_time  | timestamp without time zone
 timestamp      | character varying
 status         | character varying
 temporary      | character varying
 generated      | character varying
 secondary      | character varying
(14 rows)

postgres=>        

Points to Consider during Oracle to PostgreSQL Migration:

• In Oracle a schema and a user is?a one to one relationship?and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema
• Oracle converts the names of Schema, Tables, Columns, Functions to UPPER CASE, unless quoted. PostgreSQL converts them all to LOWER CASE, unless quoted.
• Primary Key, Foreign Key, Unique, CHECK, NOT NULL constraints - They all work pretty much same in both Engines.
• Tablespace is not really the same thing as Oracle, but serves the same purpose. The biggest differences compared to Oracle might be that Postgres tablespaces are cluster level objects and there's no possibility to restrict tablespace (thus cluster/database) size. You don't have to deal with single datafiles but folders instead
• Data TypesVARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2 - Convert to VARCHAR or TEXTCHAR, NCHAR - Convert to CHARCLOB, LONG - Convert to VARCHAR or TEXTNUMBER - Convert to BIGINT, INT, SMALLINT, REAL, DOUBLE PRECISION for Good Performance, but less control on scale. Convert to NUMERIC for Unlimited size and Low performance.BINARY_INTEGER, BINARY_FLOAT - Convert to INTEGER, FLOATBLOB, RAW, LONG RAW - Convert to BYTEA.DATE - Convert to DATE, TIMESTAMP, TIMESTAMP without TIMEZONE.
• Avoid transaction logging (WAL) for speeding up the Migration.
• Defer Index creation until after data load
• Postgres doesn't have synonyms like Oracle. Use Views for tables and Wrappers for Functions.
• PostgreSQL Process Architecture is similar to Oracle SGA => shared_buffers PGA => work_mem PMON => Postmaster TNS Listener => Postmaster GRANT/REVOKE => Almost the same; mostly syntax change

Summary:

We have successfully migrated data from a table with 100 rows, 14 columns and 3 different Data Types of a Source RDS Oracle Database to the destination RDS PostgreSQL Database.

Thanks Raj Kumar for this content.