Migrating an On-Prem Oracle database to PostgreSQL AWS Cloud

Migrating an Oracle database to PostgreSQL involves multiple phases: schema conversion, data migration, and application changes. When migrating from an on-premises Oracle database to PostgreSQL (either on AWS RDS or self-managed on EC2), the process requires tools like AWS Schema Conversion Tool (SCT), AWS Database Migration Service (DMS), and manual interventions for data types and stored procedures. Below is a step-by-step guide with command-line and AWS console instructions, including both on-premises to cloud migration steps.

1. Planning and Assessment

Console & On-Prem Steps:

• Assess Oracle Database:

  • Identify the Oracle database size, schema, data, and any complex PL/SQL code that might require modification when migrating to PostgreSQL.

  • Ensure that all data types, functions, and triggers in Oracle have an equivalent in PostgreSQL.

• Estimate Downtime: Plan migration based on acceptable downtime. If downtime is critical, you may want to use tools that support continuous replication like AWS DMS with CDC (Change Data Capture).

2. Install and Set Up AWS Schema Conversion Tool (SCT)

AWS SCT helps in converting Oracle schema to PostgreSQL-compatible schema.

Console Steps:

1. Download and Install AWS SCT:

   • Download AWS Schema Conversion Tool and install it on your local machine.

2. Connect to Source (Oracle) and Target (PostgreSQL):

   • Open SCT.

   • Connect to your on-prem Oracle database as the source.

   • Connect to AWS RDS PostgreSQL or your PostgreSQL instance on EC2 as the target.

3. Convert Schema:

   • Use SCT to convert Oracle database schema to PostgreSQL-compatible schema. SCT will provide a report of what can be automatically converted and what requires manual intervention.

4. Save the Converted Schema:

   • Save the converted schema SQL scripts for manual deployment or use the SCT to deploy it directly to PostgreSQL.

Command Line Steps (For PostgreSQL on EC2):

If you're setting up PostgreSQL on EC2, use the following to install PostgreSQL and set up your instance:

bash

# Update EC2 instance sudo apt-get update # Install PostgreSQL sudo apt-get install postgresql postgresql-contrib # Start PostgreSQL service sudo systemctl start postgresql # Enable PostgreSQL to start on boot sudo systemctl enable postgresql # Create a new PostgreSQL user and database sudo -i -u postgres psql CREATE USER myuser WITH PASSWORD 'mypassword'; CREATE DATABASE mydb OWNER myuser;

3. Set Up AWS RDS PostgreSQL Instance

If you're migrating to AWS RDS for PostgreSQL, follow these steps:

Console Steps:

1. Create PostgreSQL Instance on RDS:

   • Go to RDS Console > Create Database.

   • Choose PostgreSQL as the database engine.

   • Choose the required instance type and storage settings.

   • Set the password for the postgres admin user.

   • Enable automatic backups and Multi-AZ (if needed for high availability).

2. Configure Security Groups:

   • Open EC2 Console > Security Groups.

   • Ensure the security group attached to the RDS instance allows incoming connections on port 5432 (PostgreSQL's default port) from your on-prem Oracle host or DMS instance.

   
   

4. Prepare Oracle Database for Migration

Command Line Steps (Oracle):

1. Export Oracle Schema Using Data Pump: Use Oracle Data Pump to export the schema and data from Oracle:

   bash

   expdp system/password@oracleDB schemas=your_schema directory=DATA_PUMP_DIR dumpfile=oracle_schema.dmp logfile=export.log

   • Ensure that the DATA_PUMP_DIR is set to a valid location on the Oracle server.

   • Transfer the dump file to a location that will be accessible for migration, such as an EC2 instance, or use a local server for manual data load.

5. Set Up AWS DMS for Data Migration

AWS Database Migration Service (DMS) helps with moving data from Oracle to PostgreSQL while converting schemas with minimal downtime.

Console Steps:

1. Create a DMS Replication Instance:

   • Navigate to DMS Console > Replication Instances > Create Replication Instance.

   • Choose an instance size based on your database size and throughput needs.

2. Configure Source and Target Endpoints:

   • Go to DMS Console > Endpoints > Create Endpoint.

   • Set up Oracle as the source and PostgreSQL as the target.

   • Test both endpoints to ensure DMS can communicate with the source and target databases.

3. Create a DMS Migration Task:

   • Go to DMS Console > Tasks > Create Task.

   • Select your replication instance, source, and target endpoints.

   • Choose migration type: Full Load or Full Load + CDC (if minimal downtime is needed).

4. Run the Migration Task:

   • Start the migration task to begin the process of copying data from Oracle to PostgreSQL.

   • Monitor the task in the DMS Console for any errors or issues.

Command Line (AWS CLI):

• Create Replication Instance:

  bash

  Copy code

  aws dms create-replication-instance --replication-instance-identifier my-dms-instance --replication-instance-class dms.r5.large --allocated-storage 100

• Create DMS Task:

  bash

  aws dms create-replication-task --replication-task-identifier oracle-to-postgres-task --source-endpoint-arn arn:source --target-endpoint-arn arn:target --migration-type full-load --table-mappings file://table-mapping.json

6. Data Load and Testing

Console Steps:

1. Verify Data in PostgreSQL:

   • Use pgAdmin or a PostgreSQL client to verify that the data has been migrated successfully.

2. Test Application: Point your application to the new PostgreSQL database instance and run functional and performance tests.

Command Line (PostgreSQL):

• Check Tables and Data:

  bash

  psql -h <PostgreSQL_endpoint> -U myuser -d mydb \dt # List tables SELECT * FROM employees LIMIT 10; # Test data query

7. Schema Optimization and Manual Adjustments

Some objects (like stored procedures, triggers, and complex data types) may not be automatically converted by AWS SCT or DMS. You’ll need to manually adjust these.

Manual Adjustments:

1. Stored Procedures: Convert Oracle PL/SQL procedures to PostgreSQL PL/pgSQL equivalents.

2. Data Types: Adjust incompatible data types (e.g., NUMBER in Oracle may need to be converted to NUMERIC in PostgreSQL).

Command Line:

• Manually deploy adjusted schema using the psql tool:

  bash

  psql -h <PostgreSQL_endpoint> -U myuser -d mydb -f schema_adjustments.sql

8. Cutover and Go Live

Once you've verified that all data is successfully migrated, and the application works with the new PostgreSQL database:

Console Steps:

1. Switch Application: Update application configurations to point to the new PostgreSQL database.

2. Update DNS Records: If needed, update DNS records to point to the new PostgreSQL database endpoints.

Command Line:

• Update Connection String in your application to PostgreSQL connection string:

  arduino

  jdbc:postgresql://<RDS-endpoint>:5432/mydb

9. Post-Migration Activities

Console Steps:

1. Backup Setup:

   • For RDS PostgreSQL, enable automated backups via the RDS Console.

   • For PostgreSQL on EC2, set up pg_basebackup or other backup strategies.

2. Monitoring: Use CloudWatch for RDS monitoring and performance insights.

Command Line:

• Enable Backups for EC2 PostgreSQL:

  bash

  sudo crontab -e # Add a cron job for pg_dump 0 3 * /usr/bin/pg_dumpall -U postgres | gzip > /var/backups/pg_backup.gz

10. Decommission Old Oracle Infrastructure

Once you've confirmed the success of the migration, you can begin decommissioning the old Oracle database infrastructure.

Command Line:

• Stop Oracle Services:

  bash

  sudo systemctl stop oracle-xe

• Delete Oracle VM (on-prem or EC2):

  bash

  aws ec2 terminate-instances --instance-ids i-0123456789abcdef0

Conclusion:

By following this detailed guide, you can successfully migrate an Oracle database from an on-premise environment to PostgreSQL in AWS (either on RDS or EC2). This process involves schema conversion using SCT, data migration using AWS DMS, and manual adjustments for complex objects. Proper planning, testing, and monitoring are essential for a smooth migration.