How to Migrate Oracle to PostgreSQL: Step‑by‑Step Guide with SCT, Ora2PG, and Python

Open Source + Cloud

Open‑source databases and cloud services are increasingly popular because they eliminate costly commercial licenses, dramatically reducing overall IT spend. By adopting PostgreSQL or MySQL‑based cloud offerings such as Google Cloud SQL, Amazon Aurora, or Azure Database, organizations gain high availability, disaster recovery, security, scalability, and performance benefits.

Database Transformation

The DB‑Engines ranking for October 2021 shows Oracle as the leading commercial database, followed by MySQL, Microsoft SQL Server, and the rapidly rising open‑source PostgreSQL. Many PostgreSQL users deploy on‑premises, but the majority prefer cloud‑hosted instances to support applications and clusters.

Oracle Migration to PostgreSQL

The migration project focuses on three essential steps: assessment, schema conversion, and data migration. While additional tasks may be required, these three phases form the core of any database transformation effort.

Using AWS Schema Conversion Tool (SCT)

AWS SCT demonstrates the first two migration steps—assessment and schema conversion. The third step, data migration, can be performed with AWS Database Migration Service, which is mentioned later.

During assessment, SCT scans the Oracle schema and generates a detailed report of compatibility issues.

Next, SCT performs schema conversion, producing PostgreSQL‑compatible DDL scripts.

Ora2PG Demonstration

Ora2PG, an open‑source migration tool, is used for the same three steps. First, a configuration file Ora2PG_schema.conf is prepared for assessment.

Running Ora2PG with this file produces an assessment report.

For schema conversion, the same configuration file is reused, and the generated SQL is applied to the target PostgreSQL database.

Finally, data migration is performed by changing the TYPE parameter to COPY in the configuration and executing Ora2PG, which loads the data into PostgreSQL tables.

Python Example

Although Ora2PG is written in Perl, the article shows a Python‑based migration example. It copies Oracle performance data into PostgreSQL, visualizes it with Grafana, and demonstrates a dramatic performance improvement—from 19 ms query time down to 0.06 ms and a reduction of CPU usage from 5.7 % to a negligible amount.

Real‑Time Data Replication

When zero downtime is required, real‑time data replication becomes the third migration step. Numerous tools are available, or custom scripts can be written to achieve continuous data sync.