ora_migrator

ora_migrator is a PostgreSQL plugin for db_migrator that uses oracle_fdw to migrate an Oracle database to PostgreSQL. The migration process will be done via a simple command line interface. 

With ora_migrator you can migrate a simple database with a single SQL statement:

CREATE EXTENSION ora_migrator;
SELECT oracle_migrate(server => 'oracle', only_schemas => '{HANS,PAUL}');

Unless you have stored procedures in PL/pgSQL or some other complex parts within your database, this is already it. The entire migration will be done in a SINGLE transaction.

How does it work?

First of all the ora_migrator will connect to Oracle using the oracle_fdw, which is the real foundation of the software. Then the Oracle system catalog will be read and a copy of the table definitions, index definitions, etc. will be stored in PostgreSQL. oracle_fdw will do all the data type mapping for you. 

Why do we copy the Oracle system catalog to a local table and not just use it directly? During the migration process you might want to make changes to the underlying data structure. You might not want to blindly copy table definitions.

Once the definitions are duplicated and once you have made your modifications (which might not be the case too often – most people prefer a 1:1 copy), the ora_migrator will actually create the desired tables in PostgreSQL, load the data from Oracle, create indexes and add constraints. Your transaction will commit and your migration is done.