Oracle to PostgreSQL Migration: The Complete Guide

Migrating from Oracle to PostgreSQL is one of the most strategic database decisions organizations are making in 2026. According to the 2025 Stack Overflow Developer Survey, PostgreSQL has reached 55.6% developer adoption, making it the most widely used database among developers for four consecutive years. Meanwhile, Oracle continues to face scrutiny over licensing costs, vendor lock-in, and limited cloud portability.

But the switch is not straightforward. Schema conversions, PL/SQL rewrites, data type mismatches, and downtime risk make Oracle to PostgreSQL one of the more complex migrations a team can undertake.

This guide covers three proven methods, from no-code automation to open-source tooling and a fully programmatic approach, so you can choose the right path and migrate without disrupting production.

Oracle to PostgreSQL Migration methods: Step-by-step guide

There is no single right way to migrate from Oracle to PostgreSQL. The best method depends on your technical expertise, data volume, and whether you need a one-time migration or ongoing replication.

Method 3: AWS Schema Conversion Tool is best for teams migrating to AWS-hosted PostgreSQL who need automated schema and PL/SQL code conversion with a detailed assessment report

Method 1: ora2pg is best for technical teams that need granular control over schema conversion, data export, and SQL script generation

Method 2: Hevo Data is best for teams that want automated, real-time data migration with zero infrastructure overhead and no manual intervention

	ora2pg	Hevo Data	AWS SCT
Technical Skill	High	Low	Medium
Setup Time	Hours to days	Minutes	Hours
Schema Conversion	Yes, manual adjustments needed	Automatic	Yes, with flagged exceptions
PL/SQL Conversion	Partial	No	Yes, automated
Real-Time Sync	No	Yes, CDC-based	No
Data Movement	Yes, via SQL scripts	Yes, fully managed	No
Cost	Free	Subscription-based	Free
Maintenance	Ongoing	Zero	One-time tool

Method 1: How to Set Up Oracle to PostgreSQL Migration using ora2pg?

Ora2Pg is a free tool for migrating Oracle or MySQL databases to PostgreSQL. It automatically scans and extracts the structure or data from your Oracle database, generating SQL scripts for loading into PostgreSQL. It’s versatile, suitable for reverse engineering, large-scale migrations, or data replication. Its user-friendly interface requires minimal Oracle database knowledge, only necessitating connection parameters.

Let’s examine the steps to migrate your data from Oracle to PostgreSQL.

Prerequisites

• Oracle Database
• Strawberry Perl
• PostgreSQL
• Ora2pg

Step 1: Installation

1.1) Installing Strawberry Perl

Strawberry Perl is an MS Windows environment that contains everything you need to run and develop Perl applications. It is designed to be as close as possible to the Perl environment on UNIX systems.

You can install it from the link provided below:

• Download the latest MSI release from the Strawberry Perl website

• After it is downloaded, Accept the license agreement and click on Next.
• Select the Destination Folder Path.

The progress bar shows the progress of your installation.

Note: To check if your Strawberry Pearl is correctly installed, open your text editor and create a new text file. While saving it, give the extension as .pl (My file name is xyz.pl). Then open your command prompt and type in:

perl filename.pl

It shows the content of the text file which you just created. Now you know that Strawberry Perl is running successfully.

1. 2)  How to Install Ora2pg

• You can install the latest version of ora2pg (v25.0) from the GitHub repository

Ora2Pg is a free tool for migrating an Oracle database to a PostgreSQL-compatible schema. It connects to your Oracle database, scans it automatically, extracts its structure or data, and generates SQL scripts that you can load into your PostgreSQL database.

• Open the command prompt and type the command cd ora2pg-master

Congratulations. We are done with the key installations. Now, we will move on to how to configure them to migrate our data from Oracle to PostgreSQL using Ora2pg successfully.

Step 2: Configurations

Ora2Pg needs the Perl module DBD::Oracle to connect to an Oracle database from Perl DBI. To get DBD::Oracle, go to CPAN, a Perl module repository.

• Install Oracle Instant Client Basic and SQL PLUS Package. Then, you need to set up the Environment Variables as follows.
• Go to the admin folder of Instant Client.

• Go to Advanced System → Environment Variables → New → New System Variable.

• Give the Variable Name ‘ORACLE_HOME’ and the Variable Value the admin folder path, which in my case is  ‘C:\add\db_home.’
• Similarly, add another System Variable, LD_LIBRARY_PATH, with the name and value shown below.

• Open Command Prompt and type the command

echo %ORACLE_HOME%.

The file directory will change to Variable Admin path, which in my case is C:\add\db_home.

• Now, you can use the CPAN command to install DBD::Oracle. and give the command install “DBD::Oracle.”

• Then give the command install “DBD::Oracle.”
• Go to any Text Editor, such as Visual Studio Code, and create an ora2pg.conf text file in the same directory where ora2pg is downloaded.

Note: We will make changes to this file to connect to Oracle and PostgreSQL.

• We need to configure the DSN, and then we can connect to Oracle and PostgreSQL, respectively.

I used Oracle Database Express Edition (XE) version 19 for the migration. The Oracle home directory was set to C:\add\db_home, and the LD_LIBRARY_PATH environment variable was set to C:\add\db_home\bin.

Step 3: Pre-migration

3.1. Discover:

• Version of the Oracle database

SELECT banner

FROM v$version;

• Instance information

SELECT instance_name, host_name, version, status

FROM v$instance;

• Data file information

SELECT file_name, tablespace_name

FROM dba_data_files;

• Tablespaces information

SELECT tablespace_name, status, contents

FROM dba_tablespaces;

This involves scanning the present network to identify all Oracle instances’ versions and features.

3.2. Assess:

• Use the command: to get a detailed text report and estimate the migration cost in human days using the estimate_cost option

ora2pg -t SHOW_REPORT –estimate_cost

3.3. Convert :

• In this step, we will convert the Oracle code and DDL scripts to PostgreSQL. The ora2pg tool helps export Oracle objects in a PostgreSQL-compatible format. However, some objects require manual changes.
• I will create a migration template using the ora2pg command:

ora2pg –project_base /app/migration/ –init_project test_project

The following message will be displayed:

Step 4: Migration 

4.1. Export Oracle Objects :

• We will now export Oracle objects as PostgreSQL objects using the script:

cd /app/migration/test_project

./export_schema.sh

4.2. Compile Files :

• We will run SQL commands to load the DDL files and import the data into PostgreSQL.The command is :

psql -f /app/migration/test_project/schema/sequences/sequence.sql -h server1-server.postgres.database.azure.com -p 5432 -U username@server1-server -d database -L /app/migration/test_project/schema/sequences/create_sequences.log

psql -f /app/migration/test_project/data/table1.sql -h server1-server.postgres.database.azure.com -p 5432 -U username@server1-server -d database -l /app/migration/test_project/data/table1.log

4.3. Sync Data Selectively:

• We will use specific queries to migrate data based on parameters such as date.

select * from table1 where filter_data < ’01/06/2024′

Step 5: Post Migration

• We will run tests to verify that applications work correctly with the new setup. The command for doing the same is:

ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt

ora2pg handles schema conversion. But moving your data reliably and keeping it in sync requires a separate solution.

Hevo Data automates the entire data movement process with CDC-based real-time replication, zero maintenance, and full pipeline visibility.

Try Hevo for Free →

Method 2: How to Set Up Oracle to PostgreSQL Migration using HevoData?

Migrating from Oracle to PostgreSQL can be a complex task, but Hevo Data simplifies the process by offering an automated, no-code solution. Below is a step-by-step guide to connect your Oracle database to PostgreSQL via Hevo.

Step 1: Prepare Your Oracle Database

Ensure that the following prerequisites are in place:

• Oracle Version: Ensure Oracle 11 or above is used.
• Permissions: The database user needs SELECT permissions, as well as SYSDBA privileges for LogMiner (used in Redo Log mode).
• Redo Log Replication: Enable Redo Log replication for near-real-time data capture.

Step 2: Set Up Oracle Database User and Privileges

• Create a User: Connect to Oracle using SQL tools and create a new user:
• Grant Required Privileges: Allow access to necessary metadata and enable LogMiner:
• Enable Archive Logs and Supplemental Logging: Archive logs ensure full data recovery, and supplemental logging tracks column changes. Ensure these are enabled:
  • ARCHIVELOG: Indicates that the archive log is enabled.
  • NOARCHIVELOG: Indicates that the archive redo is disabled.

Step 3: Configure Oracle Database for Hevo Data

• Verify Archive Log Mode:
  • Confirm that archive logs are enabled using:

SELECT LOG_MODE FROM V$DATABASE;

• If NOARCHIVELOG, enable it:

ALTER DATABASE ARCHIVELOG;

• Set Up Supplemental Logging:
  • Enable logging for all columns in Oracle:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 4: Connect Hevo Data to Your Oracle Database

• Create a New Pipeline in Hevo:
  • Navigate to Pipelines > Create Pipeline in Hevo.
  • Select Oracle as your source.

• Enter Connection Details:
  • Database Host: Enter your Oracle database IP or DNS (e.g., oracle-rds-1.xxxx.rds.amazonaws.com).
  • Database User/Password: Enter the credentials for the user created.
  • Service Name: Retrieve it from Oracle using:

select name from v$database;

• Select Ingestion Mode:
  • Redo Log is recommended for real-time, change-data capture. If using Table Mode or Custom SQL, you’ll need to specify additional settings.

Step 5: Configure PostgreSQL as the Destination

• Create PostgreSQL Destination in Hevo:
  • Navigate to Destinations > Create Destination.
  • Select PostgreSQL as your destination.

• Enter Connection Details:
  • Host: Provide the PostgreSQL server’s IP or DNS.
  • User/Password: Enter PostgreSQL credentials.
  • Database Name: Specify the PostgreSQL database.

Step 6: Monitor and Test the Connection

• Test the Connection: Use Hevo’s Test Connection feature to ensure everything is configured correctly.
• Run the Pipeline: Start the pipeline to initiate data replication.

If you want your source to be Amazon RDS Oracle connected to PostgreSQL destination then read our blog.

Load you Data from Oracle to PostgreSQL in minutes!

Start for Free

Method 3: How to Set Up Oracle to PostgreSQL Migration Using AWS Schema Conversion Tool (SCT)?

AWS Schema Conversion Tool (SCT) is a free desktop application from AWS that automatically converts Oracle schemas, stored procedures, functions, triggers, and views into PostgreSQL-compatible equivalents. It is best suited for teams migrating Oracle workloads to AWS-hosted PostgreSQL environments such as Amazon RDS or Aurora PostgreSQL.

Prerequisites

• An AWS account
• AWS SCT installed. Download from the AWS SCT page
• Oracle and PostgreSQL JDBC drivers installed on the same machine
• Access to your Oracle source and target PostgreSQL database on AWS RDS or Aurora

Step 1: Install and Launch AWS SCT

1. Download and install AWS SCT and add the required Oracle and PostgreSQL JDBC drivers when prompted
2. Open AWS SCT and go to File and click New Project
3. Enter a project name, select Oracle as the source engine and Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL as the target engine and click OK

Step 2: Connect to Your Oracle Source Database

1. In the left panel click Add Source, select Oracle and enter your server name, port, SID or service name, and credentials
2. Click Test Connection to verify, then Connect to load your Oracle schema

Step 3: Run the Migration Assessment Report

1. Right-click your Oracle schema and select Create Report
2. Review the output carefully:
   • Conversion summary: Percentage of objects automatically converted
   • Action items: Objects requiring manual intervention with estimated effort in hours
   • Severity levels: High, medium, and low complexity items flagged for review
3. Export the report as PDF or CSV for migration planning

Step 4: Connect to Your PostgreSQL Target Database

1. In the right panel click Add Target, select Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL
2. Enter your RDS endpoint, port, database name, and credentials
3. Click Test Connection then Connect

Step 5: Convert and Apply the Schema

1. Right-click your Oracle schema and select Convert Schema
2. Review flagged items in the right panel:
   • Red: Cannot be automatically converted. Requires manual PL/pgSQL rewriting
   • Orange: Partially converted. Review and adjust before applying
   • Green: Successfully converted and ready to apply
3. Resolve all red and orange items, then right-click the converted schema and select Apply to Database
4. Verify the applied schema:

sql

SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’;

Step 6: Migrate the Data and Validate

AWS SCT converts schemas but does not move data. Use one of the following to complete the migration:

• AWS DMS: Best for large-scale, ongoing replication with near-zero downtime
• Hevo Data: Best for fully managed, no-code CDC-based replication with zero maintenance
• pg_dump and COPY: Best for simple, one-time bulk transfers on smaller datasets

After migration, run row count checks and sample query comparisons between Oracle and PostgreSQL to validate accuracy. Test all manually rewritten stored procedures, functions, and triggers before going live.

AWS SCT is a schema conversion tool, not a data pipeline. For ongoing real-time sync between Oracle and PostgreSQL, use Hevo Data alongside AWS SCT for a complete end-to-end migration solution. Try Hevo for now →

What is Oracle?

Oracle Database, or Oracle DB, is a relational database management system(RDBMS) primarily used for data storage, organization, and retrieval, data warehousing, and online transaction processing(OLTP). It is a commercial database(licenced by Oracle Corporation) used by the world’s largest enterprises. The Oracle Database can be run on-premise, on the Cloud, or even as a hybrid cloud installation. 

What is PostgreSQL?

PostgreSQL is a free, open-source object-relational database system(ORDBMS) that supports both relational(SQL) and non-relational(JSON) querying. It is an enterprise-class database that was started as part of the POSTGRES project at the University of California, Berkeley. It is a highly extensible system where users can build many custom functions, write their own codes, and build data types. 

Key Aspects of Oracle to PostgreSQL Migration

Migrating from Oracle to PostgreSQL involves several key aspects, including planning, schema conversion, data migration, and testing.

1. Assessment and Planning

The migration begins with a thorough assessment of the Oracle environment:

• Identify Oracle-Specific Features: Oracle’s PL/SQL, proprietary data types, and other features may not have direct equivalents in PostgreSQL. Identifying these early helps with planning their conversion or replacement.
• Define Migration Scope: Determine which parts of the database need to be migrated and which can be excluded, simplifying the process and reducing complexity.
• Choose Migration Tools and Strategies: Select appropriate tools like Ora2Pg or AWS Schema Conversion Tool, and plan the migration strategy based on data volume, downtime, and complexity.

2. Schema Conversion

Once the assessment is done, convert Oracle’s schema to PostgreSQL:

• Automated Tools: Tools like Ora2Pg can automate schema conversion, including tables, indexes, and constraints. However, manual adjustments are required for features like PL/SQL code and user-defined types.
• Address Unsupported Features: Some Oracle features may require manual intervention. For example, PL/SQL procedures must be rewritten as PostgreSQL PL/pgSQL.

3. Data Migration

Migrating the data is another key aspect:

• ETL Process: The standard process is to Extract, Transform, and Load the data into PostgreSQL. This involves transforming Oracle data to a compatible PostgreSQL format.
• Direct Data Transfer: In some cases, you can use Foreign Data Wrappers (FDW) to transfer data directly without migrating it.
• Synchronization: If Oracle and PostgreSQL need to coexist temporarily, tools like SymmetricDS can sync data between both databases during the migration.

4. Testing and Validation

Testing ensures the migration is successful:

• Test Schema and Functionality: Validate that the converted schema works as expected and that all data has been migrated correctly.
• Performance Testing: Compare the performance of PostgreSQL with Oracle to ensure it meets the required standards.
• Address Issues and Fine-Tune: Optimize PostgreSQL for performance by adjusting indexes, query execution plans, and storage configurations.

Key Considerations for Oracle to PostgreSQL Migration

While the key aspects focus on the technical details, the key considerations provide strategic insights for a successful migration.

1. Database-Specific Features and Limitations

• Oracle and PostgreSQL have different features:
  • PL/SQL to PL/pgSQL: Oracle’s PL/SQL doesn’t translate directly to PostgreSQL’s PL/pgSQL. This requires rewriting stored procedures, triggers, and functions.
  • Data Types: Some Oracle data types have no direct PostgreSQL equivalent, requiring adjustments or custom solutions.

2. Performance Optimization

• Post-migration, optimizing PostgreSQL for performance is crucial:
  • Query Optimization: PostgreSQL may require query tuning to achieve the same performance as Oracle, such as reworking indexes or query execution plans.
  • PostgreSQL-Specific Features: Leverage PostgreSQL features like JSON support, full-text search, and GIN indexes for better performance.

3. Cost Implications

• Migrating from Oracle to PostgreSQL often reduces licensing costs:
  • PostgreSQL is an open-source, cost-effective alternative, but migration can be resource-intensive. Ensure you evaluate the migration cost, including tools, downtime, and potential consulting.

4. Change Management and Team Readiness

• Switching from Oracle to PostgreSQL requires team readiness:
  • Training: Your team will need to be trained in PostgreSQL administration, as it has different management tools and approaches compared to Oracle.
  • Tool Familiarity: The team should become familiar with PostgreSQL tools like pgAdmin and psql, as well as migration tools like Ora2Pg.

5. Downtime and Data Availability

• Migration downtime can be a significant consideration:
  • Minimize Downtime: Strategies such as replication-based migrations or incremental migrations help reduce downtime and allow for near-zero disruption.

6. Backup and Rollback Plan

• Have a backup and rollback strategy in case something goes wrong during migration. This ensures that you can revert to the Oracle database if necessary.

7. Post-Migration Support

• After the migration, monitoring the new PostgreSQL system is essential:
  • Ongoing Monitoring: Regular performance monitoring and database maintenance (such as vacuuming and analyzing tables) ensure that PostgreSQL continues to perform well.

Conclusion

Migrating your data from Oracle to PostgreSQL can be a complex process, but with the right steps and tools, it can be achievable. In this blog, I covered a step by step approach for Oracle to PostgreSQL migration using JDBC.

If you’re looking for a more hands-on approach, the manual JDBC method offers more control. But if you’d prefer a faster and more automated solution, Hevo is a great option to explore. I hope this guide helps you choose the best migration method for your needs.

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

Share your experience of Oracle to PostgreSQL migration in the comments section below!

Frequently Asked Questions