Migration from Oracle to PostgreSQL using oracle_fdw Foreign Data Wrapper on Windows - step by step guide

A. Install PostgreSQL 

1. Download PostgreSQL from its Official Website.

2. For installing on Windows, launch the GUI installer wizard by executing the .exe file. The installer will create a default PostgreSQL superuser, postgres. Give it a password when asked for. A detailed installation guide can be obtained from Install PostgreSQL.

After you finished installing PostgreSQL, add full path of its bin and lib directories to the PATH environmental variable.

3. Modify the postgresql.conf. The file is located in the data directory. Open it with a text editor. Change the listen_addresses to '*' (listen_address = '*') and the port to the port number you used during installation    

4. Create a new database in pgAdmin III by right clicking Databases and selecting New Database.

5. Create a new schema in pgAdmin III by right clicking the Schemas and selecting New Schema.

6. Install the pgJDBC and pgODBC. Click Start/All Programs/PostgreSQL/Application Stack Builder to launch the Application Stack Builder.

      Select your PostgreSQL installation. Click Next.

      Unfold Database Drivers. Check pgJDBC and psqlODBC. And click Next

On following screens, click Next or Install to install the pgJDBC and pgODBC.

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>
The pgJDBC driver: org.postgresql.Driver

B. Install Oracle Instant Client.

The Foreign Data Wrapper for Oracle requires Oracle Instant Client to access the remote Oracle database. Otherwise, it will throw a "could not load library "C:/Program Files/PostgreSQL/9.4/lib/oracle_fdw.dll" error.

A detailed installation guide can be obtained at Install Oracle Instant Client on Windows.

C. Install Oracle Data Wrapper oracle_fdw

1. download oracle_fdw from its Official Website.

2. Unzip it to a directory.

3. Copy the three files oracle_fdw.control, oracle_fdw--1.0--1.1.sql, and oracle_fdw--1.1.sql from the share\extension directory to the PostgreSQL Home\share\extension\ directory.

4. Copy lib\oracle_fdw.dll to PostgreSQL Home\lib\ directory.

5. Create the oracle_fdw extension in PostgreSQL. Select your database in pgAdmin III. Click the Tools in the top menu bar and select Query tool. In the SQL Editor, type CREATE EXTENSION oracle_fdw WITH SCHEMA <your schema>, and execute it.

D. Migration from Oracle to PostgreSQL

1. Create the Foreign Data Wrapper server. In the SQL Editor, execute the below statements.

      CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//<oracle database host IP address>:<port>/<database name>');

      GRANT USAGE ON FOREIGN SERVER oradb TO postgres;

2. Create user mapping by execute the following statement in the SQL Editor.

      CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle database user>', password '<oracle database password>');

3. Migrating tables.

    i) Create the foreign table. A foreign table is a stub to access corresponding remote oracle database table as if it is local.

          CREATE FOREIGN TABLE <foreign table name> SERVER oradb OPTIONS (table '<oracle database table name>');

    ii) Migrating data to PostgreSQL.

          CREATE TABLE <table name> AS (SELECT * FROM <foreign table name>);

E. Drop the User Mapping, Server, and oracle_fdw Extension.

      DROP EXTENSION IF EXISTS oracle_fdw CASCADE;

      With CASCADE option at the end, it will drop all objects that depend on the oracle_fdw extension.

      Or you can drop each one of them separately.

      DROP USER MAPPING for postgres server oradb;

      DROP SERVER oradb;

      DROP EXTENSION IF EXISTS oracle_fdw;

--------------------------------------------------------------------------------------------------------


                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.