Ora2pg - a free tool for Oracle migrate to Postgres

Ora2pg

I'm trying to help my team migration Oracle to Postgres and I found a very useful tool that can help me to solve migrate problems. It not only supports Postgres but also Mysql, Ora2pg can work with Orafce, if you have procedures or functions, its easy to integrate to work.

Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.

---

The "orafce" project implements some functions from the Oracle database. The functionality was verified on Oracle 10g, and the module is useful for production work.

Initiate a project for work

Ora2pg will help you to create a project, it helps you to configure the setting.

# it will create a folder 
ora2pg --init_project your_project

Configuration

The ora2pg.conf in the /config folder. This directives are demonstration.

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=localhost;sid=EE;port=1521
ORACLE_USER     user
ORACLE_PWD      pwd

# setting database objects to export
TYPE            TABLE,PACKAGE,VIEW,SEQUENCE,PROCEDURE,TYPE,MVIEW,TRIGGER,SEQUENCE,COPY

# you can exclude tables like logs table
EXCLUDE        LOG_FOO_TABLES

# Oracle data type mapping
DATA_TYPE       NUMBER(*\,0):bigint;NVARCHAR2(*):varchar(255)

# disable or enable Oracle NUMBER(p,s) type to Postgres numeric(p,s)
PG_NUMERIC_TYPE    1

# disable or enable Oracle NUMBER(p) type to Postgres int or bigint
PG_INTEGER_TYPE    1

# this setting should work with PG_INTEGER_TYPE
DEFAULT_NUMERIC    bigint

# setting specific pk fields to bigserial
MODIFY_TYPE  FOO_TABLE:ID_NUM:BIGSERIAL,BAR_TABLE:ID_NUM:BIGSERIAL

# Enable PLSQL to PLPSQL conversion
PLSQL_PGSQL 1

# if you installed Orafce, you can enable it
USE_ORAFCE  0

# setting specific Postgres version
PG_VERSION  11

Export and check schema

Ora2pg project provides a script, just execute the export_schema.sh, it easy to help you export Oracle schemas.

./export_schema.sh

Load schemas to Postgres

Another script is import_all.sh, its aims to help you import .sql files to Postgres.

# if you won't answer PG_PASSWORD prompt, you can setting PGPASSWORD var 
PGPASSWORD='password' ./import_all.sh -h postgres \
  -p 5432 \
  -U username \
  -o dbowner \
  -d database_name

Import data to Postgres

# Import data to Postgres with `-a`.
PGPASSWORD='password' ./import_all.sh -h postgres \
  -a \        # import data only
  -p 5432 \
  -U username \
  -o dbowner \
  -d database_name

or

# Directly import data to Postgres
PGPASSWORD='password' ora2pg -c config/ora2pg.conf \
  -t COPY \
  --pg_dsn "dbi:Pg:dbname=database_name;host=postgres;port=5432" \
  --pg_user username \
  -e 'TABLE[LOG_.*];TABLE[*._LOG]'  # exclude LOG tables