Many organizations are drawn to PostgreSQL’s robust features, open-source nature, and cost-effectiveness, and hence they look to migrate their data from their existing database to PostgreSQL. 

In this guide, we’ll discuss the Oracle to PostgreSQL migration process. Having done this migration several times, I understand the challenges and complexities of such a transition. 

Whether you are doing this for the first time or looking to refine your process, I will walk you through two options and easy-to-follow steps to migrate your data from Oracle to PostgreSQL. Let’s begin.

What is Oracle? An Overview

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? An Overview

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. 

Option 1: 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.

Configuration of the Oracle DB Used for Migration

I used Oracle Database Express Edition (XE) version 18 for the migration. The Oracle home directory was set to D:\app\HOME\product\18.0.0\dbhomeXE. The LD_LIBRARY_PATH environment variable was set to D:\app\HOME\product\18.0.0\dbhomeXE\bin.

Pre-migration

1. Discover:

  • First, I inventory all the databases that need migration. This involves scanning my network to identify all Oracle instances, along with their versions and features.

2. Assess:

  • With the inventory complete, I assess the migration challenges using the ora2pg tool. The tool’s content analysis mode generates a report describing the contents of the Oracle database and identifies elements that can’t be exported automatically.
  • I run the command ora2pg -t SHOW_REPORT 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. Convert:

  • During this step, I 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 create a migration template using the ora2pg command:

ora2pg --project_base /app/migration/ --init_project test_project

Migration

4. Export Oracle Objects:

I export Oracle objects as PostgreSQL objects using the generated script:

cd /app/migration/test_project

./export_schema.sh

I can also run specific export commands manually:

ora2pg -p -t DBLINK -o dblink.sql -b /app/migration/test_project/schema/dblinks -c /app/migration/test_project/config/ora2pg.conf

ora2pg -p -t TABLE -o table.sql -b /app/migration/test_project/schema/tables -c /app/migration/test_project/config/ora2pg.conf

5. Compile Files:

I compile the generated files against the PostgreSQL database. This involves running psql commands to load the DDL files and import the data:

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

6. Sync Data and Cut Over:

  • For online (minimal-downtime) migrations, I ensure all changes in the source are captured and applied to the target in near real-time. After verifying all changes, I cut over from the source to the target environment.

In a delta/incremental migration, I use specific queries to migrate data based on parameters such as date and time:

select * from table1 where filter_data < '01/01/2019'
select * from table1 where filter_data >= '01/01/2019'

Post-migration

7. Remediate Applications:

  • After migrating the data, I update all applications to consume the target PostgreSQL database instead of the source Oracle database. This may require changes to the applications.

8. Test:

I run tests against the PostgreSQL database to verify that applications work correctly with the new setup. I use ora2pg to ensure that all objects from the Oracle database have been created in PostgreSQL:

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

9. Optimize:

  • Finally, I address any data accuracy and performance issues. This phase involves reconciling data, verifying completeness, and optimizing the workload for the new PostgreSQL environment.

Option 2: Oracle to PostgreSQL Migration using JDBC

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

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
Install PostgreSQL ODBC Driver

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
Details of the target PostgreSQL database

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
Configure Heterogeneous Oracle Services

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)

  )
Modify the File Located in the Directory

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)

   )

  )
Find the File for feeding more information

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.

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 checkout 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

  1. What are the challenges of Oracle to Postgres migration?

Migrating from Oracle to Postgres may involve challenges such as schema conversion, continuous replication, low-latency data replication, and migration validation.

  1. What is the free tool to migrate Oracle to Postgres?

Ora2pg is a free tool for migrating Oracle to PostgreSQL. It connects and scans your Oracle database automatically, extracting the structure of your data to prepare SQL scripts and migrate it to PostgreSQL.

  1. Is PostgreSQL compatible with Oracle?

Yes, PostgreSQL is compatible with all the RDBMSs including Oracle.

mm
Principal Frontend Engineer, Hevo Data

With over a decade of experience, Suraj has played a crucial role in architecting and developing core frontend modules for Hevo. His expertise lies in building scalable UI solutions, collaborating across teams, and contributing to the open-source community, showcasing a deep commitment to innovation in the tech industry.

All your customer data in one place.