What are the best practices for migrating an Oracle database to a target RDS for PostgreSQL database using AWS DMS?

5 minute read

I want to migrate data from an Oracle source database to an Amazon Relation Database Service (Amazon RDS) for PostgreSQL target database. What best practices can I follow when migrating data from Oracle to PostgreSQL using AWS Database Migration Services (AWS DMS)?


Best practices for migrating from Oracle to PostgreSQL databases

  • Configure your Oracle source database using the required prerequisites before you start migrating your data..
  • Test the migration by using the same production data on any other QA/DEV database environment. Based on the results, you can then continue with the production migration. Be sure to use the same AWS DMS configuration for both the test and the production migrations.
  • Provision your AWS DMS instances with the enough CPU, memory, storage, and IOPS to avoid resource contention during migration. For more information, see Choosing the right AWS DMS replication instance for your migration, and Choosing the best size for a replication instance. For more information on the costs associated with upgrading your instance, see AWS Database Migration Service pricing. Provision the target Amazon RDS instance with the same consideration.
  • If your source database contains a lot of tables, with a heavy workload, then split the tables across multiple AWS DMS tasks. Split the tables based on their size on the source, application traffic patterns, and the presence of LOB columns. If a table has a lot of LOB columns, with high write traffic on the source, then create a separate task for that table.
  • Use parallel full-load to reduce the migration time for heavy workloads. For more information, see Using parallel load for selected tables, views, and collections.
  • Turn off backups and database-specific logs like bin, general, audit, and PG query logs on the target database during migration. Turn them back on to troubleshoot any issues.
  • Turn off triggers, procedures, other cron jobs, and event schedulers on the target database during migration.
  • Avoid using Multi-AZ concepts on target RDS databases during migration.
  • Avoid applying any other external client traffic to the target during migration.
  • Add any secondary indexes on the target database before starting the change data capture (CDC) phase of replication.
  • Turn off foreign keys on the target before migration. Use the target extra connection attribute (ECA)/endpoint setting to do this. For more information, see Using a PostgreSQL database as a target for AWS Database Migration Service.
afterConnectScript=SET session_replication_role='replica'
  • Use optimized LOB settings like limited or inline LOB for migration.

  • Test your workload using the default transactional CDC apply before using the batch apply CDC method. For more information, see How can I use the DMS batch apply feature to improve CDC replication performance?

  • Increase the values of executeTimeout in the target endpoint settings. This value sets the client statement timeout for a PostgreSQL instance. The default value is 60 seconds.

  • If you are using sequences for ID columns, then set the values higher on the target than they are on the source. Make sure that the target values are still higher than your source at the migration cutover date. This approach prevents the collision of sequence IDs after migration.

  • To enhance performance in the full-load phase, use settings like:

  • MaxFullLoadSubTasks - The default value is 8, and the maximum is 49. This setting is useful, but make sure that you have enough resources available.
  • CommitRate - Use this setting to indicate the maximum number of records that can be transferred together.
  • For Full LOB mode in the full-load phase - N rows from source = N inserts+N updates in target = N events, where CommitRate=N.
  • For limited LOB mode in the full-load phase - Write N rows into the CSV file, where CommitRate=N).
  • For large tables, increase the size of the CSV file. This is controlled by maxFileSize in the target endpoint settings.

Migrate an Oracle database to a PostgreSQL database

Note: Before continuing with these steps, prepare your source Oracle database as described in the Prerequisites.

1.    Install the SQL driers and AWS Schema Conversion Tool (AWS SCT) on your local machine.

2.    Configure your Oracle source and your PostgreSQL target databases.

3.    Convert the Oracle schema to PostgreSQL using the AWS SCT.

4.    Create an AWS DMS replication instance.

5.    Create your AWS DMS source and target endpoints.

6.    Create and then run your AWS DMS task.

7.    Under Table mappings, apply a transformation rule. Add the rule-action parameter, with a value of convert-lowercase. The Oracle engine stores all object names in uppercase, by default, but PostgreSQL stores all object names in lowercase.

8.    Create a full-load and CDC AWS DMS task to replicate your data. Stop the task when the full-load phase is complete by using StopTaskCachedChangesNotApplied. Now, create secondary indexes.

9.    Cut over to PostgreSQL.

Related information

Migrating an Oracle database to PostgreSQL

How to migrate your Oracle database to PostgreSQL

AWS OFFICIALUpdated 2 years ago