Ora2pg - a free tool for Oracle migrate to Postgres
Tags: postgres, oracle
Published: 2019-10-22 08:02:38

## [Ora2pg](http://ora2pg.darold.net/start.html)
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](http://ora2pg.darold.net/start.html) can work with [Orafce](https://github.com/orafce/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.

```bash
# 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.

```bash
./export_schema.sh
```

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


```bash
# 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

```bash
# 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 

```bash
# 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
```