Oracle to PostgreSQL Migration: Easy Guide(Step by Step)

Migrating from Oracle to PostgreSQL is often driven by the need for a more cost-effective, scalable, and flexible solution. However, organizations frequently encounter roadblocks such as data compatibility issues, complex schema conversions, and potential downtime during the migration process. The shift from Oracle’s proprietary ecosystem to PostgreSQL’s open-source platform can feel daunting, especially when faced with tight deadlines and limited resources.

In this guide, I’ll walk you through the Oracle to PostgreSQL migration process, addressing the key challenges that can arise along the way. I’ll provide practical insights to help you navigate the complexities of data transfer, schema adjustments, and connection setup. You’ll find actionable steps and solutions to ensure a smooth transition. Let’s dive in.

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. 

Method 1: 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

1.Create a User: Connect to Oracle using SQL tools and create a new user:

2. Grant Required Privileges: Allow access to necessary metadata and enable LogMiner:

3. 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

1. Verify Archive Log Mode: 

• Confirm that archive logs are enabled using:

SELECT LOG_MODE FROM V$DATABASE;

• If NOARCHIVELOG, enable it:

ALTER DATABASE ARCHIVELOG;

2. 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

1. Create a New Pipeline in Hevo:
   • Navigate to Pipelines > Create Pipeline in Hevo.

• Select Oracle as your source.

2. 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;

    3. 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.

Source: Source Configuration

 Step 5: Configure PostgreSQL as the Destination

1. Create PostgreSQL Destination in Hevo:
   • Navigate to Destinations > Create Destination.

• Select PostgreSQL as your destination.

Source: Destination Configuration

2. 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

1. Test the Connection: Use Hevo’s Test Connection feature to ensure everything is configured correctly.
2. 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.

Method 2: 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.

Before the Migration begins, you need to have the following things installed in your system :

• Oracle Database
• Strawberry Perl
• PostgreSQL
• Ora2pg

Step 1: Installation

1. a) 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:

• Link to install Strawberry Perl. I have downloaded the Latest Release 5.38.2.2 MSI(171.7 MB)

• 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. b)  How to Install Ora2pg

• You can install Ora2pg from the link to 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

<strong>ora2pg -t SHOW_REPORT --estimate_cost</strong>

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

Method 3: How to set up Oracle to PostgreSQL Migration using JDBC?

Now, I will show you how to migrate your data from Oracle to PostgreSQL using JDBC manually. I followed the following steps to set up Oracle to PostgreSQL Migration manually:

Step 1: Install PostgreSQL ODBC Driver

The first step was to install the PostgreSQL ODBC driver in the system in which Oracle is installed.  As I’m using Linux, I did this by executing the following command:

yum install postgresql93-odbc

The command will differ according to the version of your Linux. 

I created a file named odbc.ini and placed it in my user folder. This file contained the details of the target PostgreSQL database.

[ODBC Data Sources]

  TARGET_PG_LINK = PostgreSQL

[TARGET_PG_LINK]

  Debug = 1

  CommLog = 1

  ReadOnly = no

  Driver = /usr/pgsql-9.3/lib/psqlodbc.so

  Servername = <PostgreSQL_SERVER_IP>

  FetchBufferSize = 99

  Username = pg_user

  Password = pg_pass

  Port = 5432

  Database = pgdb

[Default]

  Driver = /usr/lib64/liboplodbcS.so.1

Step 2: Configure Heterogeneous Oracle Services

The next step was to configure the heterogeneous services in Oracle. For this, I created three files with the extension .ora. I began by creating the first while, this initialized the heterogeneous services. Navigated to /hs/admin in my Oracle installation directory and created a file named initdblink.ora with the following content:

HS_FDS_CONNECT_INFO = TARGET_PG_LINK

 HS_FDS_TRACE_LEVEL = 0

 HS_FDS_SHAREABLE_NAME=/usr/pgsql-9.3/lib/psqlodbc.so

 set ODBCINI=/PATH_TO_ODBC_FILE/.odbc.ini

The important section is where we need to mention the ODBC information for the destination PostgreSQL installation. I ensured that I specified the same name in HS_FDS_CONNECT_INFO.

Step 3: Modify the File Located in the Directory

The next step was to modify a file located in the Oracle installation’s/network/admin directory. I found the file tnsnames.ora and added details about the destination PostgreSQL instance.

TARGET_PG_LINK  =

  (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=<ORACLE_HOSTNAME>)(PORT=<ORACLE_PORT>))

(CONNECT_DATA=(SID=TARGET_PG_LINK))

(HS=OK)

  )

Step 4: Find the File for feeding more information

I located the listener.ora file in the same directory and added the following details:

SID_LIST_<your_LISTENER_NAME>=

  (SID_LIST=

   (SID_DESC=

      (SID_NAME=TARGET_PG_LINK)

      (ORACLE_HOME=<ORACLE_HOME_DIRECTORY>)

      (PROGRAM=dg4odbc)

   )

  )

Oracle listeners are responsible for listening for connections to other databases and handling them appropriately. The name of the configured listener was found out from the listener.ora file. After changing the file, I executed the below command to restart the listener.

lsnrctl reload <your_LISTENER_NAME>

Step 5: Establish a Connection for Oracle to PostgreSQL Migration

The final step was to start PostgreSQL and type the following statement to establish the connection:

Create database link TARGET_PG_LINK connect to "pg_user" identified by "pg_pass" using ‘TARGET_PG_LINK’;

When everything went well, I executed the following query in PostgreSQL.

select * from "target_table_name"@TARGET_PG_LINK;

If the manual method seems too overwhelming and you are looking for an automated method for transferring Oracle data to PostgreSQL, try Hevo Data.

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 migration 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.

You can also read about:

• Connect Databricks to Oracle
• Integrate Oracle with Elasticsearch
• Set up Kafka–Oracle integration (3 methods)
• Best Oracle replication tools & pricing

Wrapping Up

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