An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. ora2pg a powerful open source utility.
ora2pg is a tool that migrates Oracle or MySQL databases to PostgreSQL by generating compatible SQL files As the documentation states, ora2pg “connects your Oracle database, scans it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.”
Below Steps Required for Setup ora2pg and Configuration:
- Install necessary prerequisites
- Build and install DBD::Oracle
- Build and install ora2pg
- Install the Oracle Instant Client SDK
- Configure ora2pg
- Test and use ora2pg
Prerequisite
- Install wget, perl, DBI (database interface module), DBD::Oracle and DBD::Pg
- Download the latest version of ora2pg
Step 1: Install Required Package and it's dependency
# yum install -y wget perl perl-DBI perl-DBD-Pg perl-Time-HiRes make gcc
# yum install -y perl-core perl-devel
Step 2: Download the ora2pg
# wget https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz
--2026-03-31 15:31:03-- https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz
Resolving github.com (github.com)... 20.207.73.82
Connecting to github.com (github.com)|20.207.73.82|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/darold/ora2pg/tar.gz/refs/tags/v24.3 [following]
--2026-03-31 15:31:09-- https://codeload.github.com/darold/ora2pg/tar.gz/refs/tags/v24.3
Resolving codeload.github.com (codeload.github.com)... 20.207.73.88
Connecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: ‘v24.3.tar.gz’
[ <=> ] 576,965 1.34MB/s in 0.4s
2026-03-31 15:31:10 (1.34 MB/s) - ‘v24.3.tar.gz’ saved [576965]
Step 3: Check file Downloaded or not and after that untar
# ll
total 7408
-rwxrwxrwx 1 oracle oracle 12668 Jun 28 2025 crontabs-1.11-6.20121102git.el7.noarch.rpm
-rwxrwxrwx 1 oracle oracle 3169 Sep 8 2025 RPM-GPG-KEY-oracle-ol8
-rwxrwxrwx 1 oracle oracle 576965 Mar 31 15:31 v24.3.tar.gz
# tar -xvf v24.3.tar.gz
Step 4: Install Postgres Software
# cd ora2pg-24.3/
# ll
total 644
-rwxrwxrwx 1 oracle oracle 374342 Mar 29 2024 changelog
drwxrwxrwx 1 oracle oracle 512 Mar 29 2024 doc
-rwxrwxrwx 1 oracle oracle 21 Mar 29 2024 INSTALL
drwxrwxrwx 1 oracle oracle 512 Mar 29 2024 lib
-rwxrwxrwx 1 oracle oracle 32472 Mar 29 2024 LICENSE
-rwxrwxrwx 1 oracle oracle 75191 Mar 29 2024 Makefile.PL
-rwxrwxrwx 1 oracle oracle 180 Mar 29 2024 MANIFEST
drwxrwxrwx 1 oracle oracle 512 Mar 29 2024 packaging
-rwxrwxrwx 1 oracle oracle 171271 Mar 29 2024 README
drwxrwxrwx 1 oracle oracle 512 Mar 29 2024 scripts
# perl Makefile.PL
which: no bzip2 in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:..........)
Checking if your kit is complete...
Looks good
Writing Makefile for Ora2Pg
Writing MYMETA.yml and MYMETA.json
Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: make && make install
# make && make install
# make install
OR
# make && make install
Step 5: Check Postgres Software version
# ora2pg -v
Ora2Pg v24.3
Step 6: Download & Install Oracle Instant Client SDK
# yum install -y /path/to/oracle-instantclient*.rpm
You will need to include the below in your ~/.bash_profile:
$ vi ~/.bash_profile:
# InstantClient
export ORACLE_HOME=/opt/oracle/instantclient_23_26
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PAT
export PATH=$ORACLE_HOME/bin:$PATH
Then source the new env vars by running
$ source ~/.bash_profile
You will then test the connection to your Oracle server (if you've installed the sqlplus instantclient package simply run the below):
$ sqlplus user/pwd@//IP:1521/ORCLPDB1
Configure ora2pg
After you have ora2pg built and installed you can now configure the ora2pg.conf to work with your Oracle server. The settings that you see below are the settings in my environment. The ora2pg.conf file has many configuration settings that you can change. Below are the configuration changes that you will need to apply to your ora2pg.conf in order to be able to use ora2pg. Please keep in mind that you will need to change the settings to match your environment.
Edit config:
$vi /etc/ora2pg/ora2pg.conf
# Set the Oracle home directory
ORACLE_HOME /opt/oracle/instantclient_23_26
#Connection Details (Oracle Source)
ORACLE_DSN dbi:Oracle:host=hostname;port=1521;sid=ORCL
ORACLE_USER user
ORACLE_PWD password
#Migration Scope
#Oracle schema/owner to use
SCHEMA APPS
#EXPORT SECTION (Export type and filters)
TYPE TABLE
ALLOW XX_.*
#Performance Settings
PARALLEL_TABLES 2
JOBS 2
ROWS 50000
PREFETCH 10000
CASE_SENSITIVE 0
USE_DBA 0
DATA_LIMIT 1
FILE_PER_TABLE 1
SPLIT_LIMIT 10M
# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA 1
EXPORT_TABLES 1
EXPORT_INDEXES 0
EXPORT_CONSTRAINTS 0
EXPORT_TRIGGERS 0
#Data Type Handling
#TYPE SECTION (Control type behaviors and redefinitions)
PG_VERSION 11
PG_NUMERIC_TYPE 1
DEFAULT_NUMERIC numeric
#Disabled Objects
#OBJECT MODIFICATION SECTION (Control objects structure or name modifications)
DISABLE_PARTITION 1
DISABLE_TRIGGERS 1
DISABLE_SEQUENCE 1
DISABLE_PACKAGE 1
DISABLE_FUNCTION 1
DISABLE_PROCEDURE 1
#Parsing & Metadata
PLSQL_PARSING 0
ORACLE_METADATA 0
#Other Flags
DIRECT 1
LOOK_FORWARD_FUNCTION 0
NO_FULL_SCHEMA 1
Testing ora2pg
Now that ora2pg has been configured you can finally test connectivity. By testing the connectivity you will see that you have configured ora2pg correctly, if you didn’t you will receive errors. You can test by running the below command:
$ ora2pg -t SHOW_VERSION -c config/ora2pg.conf
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Note: Here we used 'estimate_cost' is to activate the migration cost evaluation with SHOW_REPORT
$ ora2pg -t show_report --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/ora2pg/ora2pgtestreport.html
-------------------------------------------------------------------------------
Ora2Pg v24.3 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Schema APPS
Size 90.36 MB