Wondering how to migrate an Oracle schema to PostgreSQL in AWS DMS? We can help you.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how our Support Techs assist with this AWS query.

 

AWS DMS : migrate an Oracle schema to PostgreSQL

First and foremost modernization phase makes the migration easier.

This phase involves taking an inventory of objects in your Oracle database and then making a few decisions.

Firstly, deprecate any objects that are no longer needed. Don’t waste time migrating objects that no one cares about.

Also, purge any historical data that you no longer need. You don’t want to waste time replicating data you don’t need.

Secondly, move flat files and long strings stored in LOBs, CLOBs, LONGs, and so on into Amazon S3 or Amazon Dynamo DB.

This process requires client software changes, but it reduces the complexity and size of the database and makes the overall system more efficient.

The objects, like PL/SQL packages and procedures, need to be manually migrated if SCT cannot translate them, or considered to be moved back to the client software.

Today, let us see the steps followed by our Support Techs to migrate the database from Oracle to PostgreSQL.

1. Firstly, create your schema in the target database.
2. Drop foreign keys and secondary indexes on the target database, and disable triggers.
3. Set up a DMS task to replicate your data – full load and change data capture (CDC).
4. Stop the task when the full load phase is complete, and recreate foreign keys and secondary indexes.
5. Enable the DMS task.
6. Finally, migrate tools and software, and enable triggers.

Now, let us discuss about each steps in detail.

Create your schema in the target database

Start your migration by taking a look at the schema that you want to migrate.

In this case, we use the AWS Schema Conversion Tool (AWS SCT) to perform the analysis.

When you start the application, you need to create a new project, with the source being Oracle and the target being PostgreSQL.

When you’re connected, select the name of the schema that you want to migrate on the left side. Right-click the schema name and choose Convert Schema.

Then choose View / Assessment Report View.

The report breaks down by each object type the manual work effort needed to successfully convert it.

Generally packages, procedures, and functions have some issues to resolve. AWS SCT also tells you why to fix these objects and gives you hints on how to do it.

If your schema doesn’t convert automatically, below are some helpful suggestions to fix the issues:

• Modify objects on your source Oracle database so that AWS SCT can convert them to the target PostgreSQL.
• Try converting the schema as-is and modifying the scripts generated by AWS SCT manually before applying them to the target PostgreSQL database.
• Ignore the objects that cannot be converted and replace the functionality with another AWS service or equivalent.

When you make improvements to the convertibility of the schema, you can go through an iterative process to regenerate the report and schema.

The Action Items view provides you with an issue by issue listing as you work through the conversion process.

When you are satisfied with the converted schema results, you can apply them to the target PostgreSQL database.

 

Drop foreign key constraints and secondary indexes and disable triggers

Once we settle on the schema that we want on the target, we have to prepare it for migrating the actual data from the source.

We will be using the AWS Database Migration Service (AWS DMS). DMS has two phases: full load and change data capture (CDC).

During the full load phase, the tables are loaded out of order.

Thus, we have some foreign key constraint violations if we keep constraints enabled on the target.

Also, during the full load, secondary indexes should be disabled because they can slow down the table replication.

They do so because the indexes need to be maintained as the records are loaded.

On the target PostgreSQL database, run a query to generate DDL on the database tables’ foreign key constraints and save the output.

You can find many sample queries online to do this.

You should get a similar to the following. Doing this gives us the DDL to recreate the foreign key constraints later.

ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> FOREIGN KEY (key column) REFERENCES <parent table_name> (key column) MATCH FULL;

Then, similarly, run a DDL-generating query to drop all the foreign key constraints on the target database.

ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;

Now do the same for the secondary indexes – generate the create commands and the results, then drop the secondary indexes.

Next, disable triggers.

ALTER TABLE <table_name> DISABLE TRIGGER ALL;

If you are using sequences for ID columns, we recommend that when you create them on the target, you set the next values higher than they are on the source database.

Leave enough of a gap to make sure that the values are still higher than for the source database at your migration cutover date.

This approach avoids collisions in sequence IDs after the migration.

 

Set up a DMS task to replicate your data

Once we have prepared the schema on the target PostgreSQL database, we are ready to replicate the data.

DMS keeps that data up to date with CDC mode until you’re ready to migrate.

In the AWS console, bring up DMS.

Firstly, you will need to create a replication instance.

A replication instance runs the DMS task. This instance is an intermediary server that connects to both your source Oracle database and target PostgreSQL database.

Select an appropriately sized server, especially if you expect to create multiple tasks, migrate a large number of tables, or both.

Next, create an endpoint for your source database and another endpoint for your target database.

Enter all the appropriate connection information for the Oracle database and the PostgreSQL database.

Make sure that you select the Refresh schemas option after a successful connection test and Run test before you finish creating each endpoint.

Then you’re ready to create a task.

Enter a task name, select the replication instance you’ve created, and the source and target endpoint.

For Migration Type, you’ll want to use Migrate existing data and replicate ongoing changes.

Because we are using AWS SCT to precreate the schema, select Do nothing or Truncate for Target table preparation mode.

Select Stop After Applying Cached Changes for the option Stop task after full load completes.

We want the task to stop temporarily after the full load completes and apply the cache changes.

Then, we modernize our source Oracle database and move LOBs to S3, DynamoDB, or another similar service.

Finally, we suggest that you choose Enable logging so that you can see any errors or warnings that the task encounters and troubleshoot those issues. Choose Create task.

Next, under Table mappings, select which schema you want to migrate and choose Add selection rule. Choose the JSON tab. Select the Enable JSON editing check box.

Then enter the following JSON string, replacing the schema name DMS_SAMPLE with your schema name.

{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "DMS_SAMPLE",
"table-name": "%"
},
"rule-action": "include"
},
{
"rule-type": "transformation",
"rule-id": "6",
"rule-name": "6",
"rule-action": "convert-lowercase",
"rule-target": "schema",
"object-locator": {
"schema-name": "%"
}
},
{
"rule-type": "transformation",
"rule-id": "7",
"rule-name": "7",
"rule-action": "convert-lowercase",
"rule-target": "table",
"object-locator": {
"schema-name": "%",
"table-name": "%"
}
},
{
"rule-type": "transformation",
"rule-id": "8",
"rule-name": "8",
"rule-action": "convert-lowercase",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%",
"column-name": "%"
}
}
]
}

This JSON string transforms the schema name, table names, and column names to lowercase for PostgreSQL.

When the task is create, it starts automatically.

You can monitor its progress using the DMS console by selecting the task and clicking on the Table statistics tab.

When the full load is complete and cache, apply the changes, the task stops on its own.

 

Stop the task when full load is complete; recreate foreign keys and secondary indexes

Your table loads are now complete! Now is probably a good time to review the logs to make sure that there are no errors in the task.

The next phase of the task is CDC, which applies changes in the order that they occurred in the source database.

This approach means that foreign keys can be recreated because the parent tables are updated before child tables on the target database.

Recreate foreign keys and any secondary indexes that were dropped earlier by adjusting the generated script as required.

The secondary indexes are important in this phase of the task.

This phase is important because any updates done on the source database with a where clause are also index lookups on the target database also.

If updates are missing indexes, these updates run as full table scans.

Hold off enabling triggers until the migration switchover, because they can update data coming from the source.

 

Enable the DMS task

Now that we have the foreign keys and secondary indexes back, we can enable the DMS task.

Simply go to the DMS console and choose Tasks.

Next, select the task in the list and choose Start/Resume.

Then, select the Start option and choose Start task.

 

Migrate tools and software and enable triggers

Finally, the cutover point is here.

When the tools and software connections have stopped accessing the source database and the DMS task has replicated the last data changes to the target database, stop the DMS task in the DMS console.

Then enable triggers on the target database.

ALTER TABLE <table_name> ENABLE TRIGGER ALL;

 

[Need help with the process? We’d be happy to assist you]

 

Conclusion

To conclude, here we saw how our Support Techs migrate an Oracle schema to PostgreSQL in AWS DMS.