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.
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
./export_schema.sh
Finally, extract the data via the following command:
ora2pg -t COPY -o data.sql -b %namespace/data -c %namespace/config/ora2pg.conf
Here are generic recommendations to increase performance Oracle to Postgres migration:
Have questions? Contact us