One of my preferred methods to move people from Oracle to PostgreSQL is Laurenz Albe's oracle_fdw extension (https://github.com/laurenz/oracle_fdw). It is a foreign data wrapper (FDW) that allows you to represent a table in Oracle as a table in PostgreSQL. The oracle_fdw extension is one of the most sophisticated FDWs and is rock solid, well documented, free, and open source.

Installing the oracle_fdw extension requires you to install the Oracle client library. Fortunately, there are already RPM packages that can be used out of the box (http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html). The oracle_fdw extension needs the OCI driver to talk to Oracle. In addition to ready-made Oracle client drivers, there is also an RPM package for the oracle_fdw extension itself, which is provided by the community. If you aren't using an RPM-based system, you might have to compile things on your own, which is clearly possible but a bit more labor-intensive.

Once the software has been installed, it can be enabled easily:

test=# CREATE EXTENSION oracle_fdw;

The CREATE EXTENSION clause loads the extension into your desired database. Now, a server can be created and users can be mapped to their counterparts on the Oracle side, as follows:

test=# CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw 
          OPTIONS (dbserver '//dbserver.example.com/ORADB'); 
test=# CREATE USER MAPPING FOR postgres SERVER oradb 
          OPTIONS (user 'orauser', password 'orapass'); 

Now, it's time to fetch some data. My preferred way is to use the IMPORT FOREIGN SCHEMA clause to import the data definitions. The IMPORT FOREIGN SCHEMA clause will create a foreign table for each table in a remote schema and expose the data on the Oracle side, which can then be read easily.

The easiest way to make use of the schema import is to create separate schemas on PostgreSQL, which just hold the database schema. Then, data can be sucked into PostgreSQL easily using the FDW. The last section of this chapter, Migrating data and schema, regarding migrating from MySQL, shows you an example of how this can be done with MySQL/MariaDB. Keep in mind that the IMPORT FOREIGN SCHEMA clause is part of the SQL/MED standard and therefore the process is the same as it is for MySQL/MariaDB. This applies to pretty much every FDW that supports the IMPORT FOREIGN SCHEMA clause.

While the oracle_fdw extension does most of the work for us, it still makes sense to see how data types are mapped. Oracle and PostgreSQL don't provide exactly the same data types, so some mapping is done either by the oracle_fdw extension or by us manually. The following table provides an overview of how types are mapped. The left-hand column shows the Oracle types, while the right-hand column shows the potential PostgreSQL counterparts:

If you want to use geometries, make sure that PostGIS is installed on your database server.

The downside of the oracle_fdw extension is that it cannot migrate procedures out of the box. Stored procedures are a somewhat special thing and need some manual intervention.