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
Top comments (0)