While oracle_fdw is a good start, we can do even better. ora_migrator (https://www.cybertec-postgresql.com/en/ora_migrator-moving-from-oracle-to-postgresql-even-faster/, https://github.com/cybertec-postgresql/ora_migrator) has been developed on top of oracle_fdw and uses all its features in the most efficient way possible. How does it work? Once you have installed ora_migrator from our GitHub page, you can enable the extension by using the following command:
CREATE EXTENSION ora_migrator;
Once the module has been installed, it makes sense to take a look at what ora_migrator is doing. Let's run a sample call and inspect the output:
SELECT oracle_migrate(server => 'oracle', only_schemas => '{LAURENZ,SOCIAL}');
NOTICE: Creating staging schemas "ora_stage" and "pgsql_stage" ...
NOTICE: Creating Oracle metadata views in schema "ora_stage" ...
NOTICE: Copying definitions to PostgreSQL staging schema "pgsql_stage" ...
NOTICE: Creating schemas ...
NOTICE: Creating sequences ...
NOTICE: Creating foreign tables ...
NOTICE: Migrating table laurenz.log ...
...
NOTICE: Migrating table social.email ...
NOTICE: Migrating table laurenz.numbers ...
NOTICE: Creating UNIQUE and PRIMARY KEY constraints ...
WARNING: Error creating primary key or unique constraint on table laurenz.badstring
DETAIL: relation "laurenz.badstring" does not exist:
WARNING: Error creating primary key or unique constraint on table laurenz.hasnul
DETAIL: relation "laurenz.hasnul" does not exist:
NOTICE: Creating FOREIGN KEY constraints ...
NOTICE: Creating CHECK constraints ...
NOTICE: Creating indexes ...
NOTICE: Setting column default values ...
NOTICE: Dropping staging schemas ...
NOTICE: Migration completed with 4 errors.
oracle_migrate
----------------
4
(1 row)
The way ora_migrator works is as follows. First, it clones parts of the Oracle system catalog and puts this data into a staging schema inside a PostgreSQL database. Then, this information is transformed so that we can actually use it on the PostgreSQL side to create tables, indexes, views, and so on easily. At this stage, we perform data type conversions and so on.
Finally, data is copied over and indexes, constraints, and so on are applied.
What you have just seen is the most simplistic case. In reality, you might want to do some post-processing on your own. oracle_migrate is just a wrapper function and therefore you can also call the individual steps needed on your own, step by step. The documentation shows what can be done at which level and you will easily be able to migrate objects in a simple way.
In contrast to some other tools, ora_migrator doesn't try to do things that are actually impossible to do properly. The most important components that stay untouched by ora_migrator are procedures. It is basically impossible to fully and automatically convert procedures from Oracle procedures into PostgreSQL procedures. Therefore, we shouldn't attempt to transform them. In short, migrating procedures is still a partially manual process.
ora_migrator is being steadily improved and works for all Oracle versions, starting with version 11.2.