From:       To:      
Home > Documentation > Oracle to PostgreSQL

Oracle to Postgres Migration with ora2pg

This whitepaper explores Oracle to Postgres migration using popular open-source tool ora2pg. The tool can migrate Oracle schemas, views, sequences, indexes, constraints, SQL and PL/SQL code into Postgres-compatible format.

Ora2pg connects to the source Oracle database, scans it to recognize all supported objects automatically, extracts definitions of database objects and/or data. As the result of work, the tool generates SQL scripts that can be load into the target Postgres database. Ora2pg can be used for reverse-engineering database, complete Oracle to Postgres migration, or partial replication of Oracle data into a Postgres database.

Although it is a command line script written on Perl, Ora2pg is quite easy to use and has comprehensive documentation.


Oracle instant client or Oracle database server must be installed on the system. Perl version 5.10 or higher is required to run ora2pg. Also, the following Perl modules are required for database connection:


You will need the root user (administrator) privileges to make the installation steps listed below. Being a Perl script, ora2pg can be installed in common way for Perl modules:

    tar xjf ora2pg-x.x.tar.bz2
    cd ora2pg-x.x/
    perl Makefile.PL
    make && make install

These were statements for Linux/Unix OS, for Windows the last line must look like:

    gmake && gmake install

Default path for ora2pg installation is C:\ora2pg, to change it use this command before make/gmake call:

    perl Makefile.PL PREFIX=<your_install_dir>

Set PERL5LIB to the path to your installation directory:

    export PERL5LIB=<your_install_dir>

Perl module for Oracle connectivity DBD::Oracle can be obtained from CPAN module repository. Set necessary environment variables and install the module as follows:

    export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
    export ORACLE_HOME=/usr/lib/oracle/12.2/client64
    perl -MCPAN -e 'install DBD::Oracle'


Ora2pg has single configuration file (by default ora2pg.conf) that contains all settings for Oracle to Postgres migration. Those settings customize the following aspects of the migration process:

See the official documentation of ora2pg to learn more about configuring the tool.

Running ora2pg

Working cycle of ora2pg consists of the several steps. After you have set up the connection to Oracle database, you can test it via ora2pg as follows:

    ora2pg -t SHOW_VERSION -c config/ora2pg.conf

Next step is assessment that allows to estimate the cost of Oracle to Postgres migration. The result of this step is a text report that lists all objects contained in the source database and described which of them cannot be migrated. To activate the assessment mode, use the following command:

    ora2pg -t SHOW_REPORT

To create migration template (that is combination of a work tree, a configuration file, and a script to export all objects from the Oracle database), run the following command:

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

The result of this step is set of files and folders created at the path specified by "project_base" parameter. After the project structure is created along with template of configuration file, it is time to define connection settings for the Oracle database and other parameters of Oracle to Postgres migration in the configuration file.

Next step is to export all Oracle objects into Postgres format as follows:

    cd /app/migration/mig_project

Finally, extract the data via the following command:

    ora2pg -t COPY -o data.sql -b %namespace/data -c %namespace/config/ora2pg.conf

Optimize the Migration

Here are generic recommendations to increase performance Oracle to Postgres migration:

Have questions? Contact us