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

Login as Administrator.

A. Figure out the OS and modify the yum files.

1. Before installing, use command (uname -m) to decide the bit of your linux machine; and command (lsb_release -a or cat /etc/redhat-release ) to find the Distributor ID and release of your linux machine. In the following steps, download the corresponding software accordingly.

2. Edit both /etc/yum.repos.d/epel.repo and /etc/yum.repos.d/epel-testing.repo files. Comment out all entries starting with mirrorlist= and uncoment all the entries starting with baseurl=. 

Change all https to http.

Add a line sslverify=0

B. Install PostgreSQL

Installing PostgreSQL 9.5 from Yum repository

1. Download the proper postgresql package right for your machine  from http://yum.postgresql.org/repopackages.php for record.

    Find the correct postgresql from its Official Website. Hover the cursor over the link to find out the rpm link.

For example, you download this package for you CentOS 6 (64bit) machine

      pgdg-centos95-9.5-2.noarch.rpm

2. Yum install postgresql using the link. For example, on a CentOS 6 (64bit) platform, execute the following command.

      yum install http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm

      or if you have downloaded the rpm file:
             yum install rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm

3. Edit /etc/yum.repos.d/pgdg-95-centos.repo.

      Change all https to http.
      Add line sslverify=0 to the main and update sessions.

4. List all the packages in pgdg95.

      yum list | grep pgdg95 

5. Install the packages interested

      yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel

When prompted: Is this ok [Y/N]: enter Y.

6. Modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/bin:/usr/pgsql-9.5/lib to the PATH.

Creating PostgreSQL data cluster

1. Changing password of system postgres account

Installing postgresql creates a default postgres database and a user account with username set to postgres. If you want to change the password to something you know do this:

      passwd postgres

You'll be prompted for a new password.

enter <your password>.

       su - postgres

2. Init the database

      Empty the /var/lib/pgsql/9.5/data directory. Delete anything there.

      /usr/pgsql-9.5/bin/initdb

3. Modify the pg_hba.conf file (host-based authentication)

      vi /var/lib/pgsql/9.5/data/pg_hba.conf

      change IPv4 and IPv6 local connections to:   host   all   all   all   trust

4. Modify the postgresql.conf file

  

      vi /var/lib/pgsql/9.5/data/postgresql.conf

     Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'. 
     Uncomment the line port=5432 and make sure the port is correct.

Starting the database server

su - postgres

1. Start the database server:

  

      /usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data -l logfile start

2. Check if server is running:
      /usr/pgsql-9.5/bin/pg_ctl status

3. Config to automatically start at boot:

      su - administrator      
      chkconfig postgresql-9.5 on

The data cluster is created by default in /var/lib/pgsql/9.5/data and the extensions folder is in /usr/pgsql-9.5/share/extension/ and the binaries are in/usr/pgsql-9.5/bin.

Launch the psql prompt

1. Start the pgsql command line:

      su - postgres

      psql

2. Use the following commands to control the database

       /usr/pgsql-9.5/bin/pg_ctl start      - start the database server
       /usr/pgsql-9.5/bin/pg_ctl stop      - stop the database server
       /usr/pgsql-9.5/bin/pg_ctl restart   - stop/start
       /usr/pgsql-9.5/bin/pg_ctl reload   - reread its configuration files (postgresql.conf, pg_hba.conf, etc.

To immediately shut down and kick everyone off, run the command below.

      pg_ctl -D /var/lib/pgsql/9.5/data -m immediate stop

3. If for some reason, you need to uninstall the postgresql, run the following command.
       yum remove postgresql95.x86_64

C. Install JDBC

Login as administrator.

1. Create the pgJDBC directory at /usr/pgsql-9.5

      mkdir pgJDBC

2. Download the jar of pgJDBC  according to the java version installed on your machine from https://jdbc.postgresql.org/download.html to the pgJDBC directory.

      (e.g. postgresql-9.4.1209.jre7.jar)

3. Set the path by running the command below.

      export PATH=$PATH:/usr/pgsql-9.5/pgJDBC. 

Or modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/pgJDBC to the PATH.

Restart posgresql server. (It is very important to restart the postgresql server, otherwise it will throw “ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": libmql1.so: cannot open shared object file: No such file or directory” when you try to create the Oracle Foreign Data Wrapper later.)

      su - postgres
      /usr/pgsql-9.5/bin/pg_ctl restart 

4. JDBC parameters

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>

                  (e.g. jdbc:postgresql://10.04.0.342:5432/postgres)

The pgJDBC driver: org.postgresql.Driver

D. Install ODBC

Login as postgres

1. Run the command below to find the psqlODBC.

      yum search postgre | grep odbc

2. Run the command below to install psqlODBC.

      yum install postgresql95-odbc.x86_64 postgresql95-odbc-debuginfo.x86_64 unixODBC

      Check to ensure that /usr/pgsql-9.5/lib/psqlodbc.so exists.

3. Modify the /etc/odbcinst.ini file. Set the following parameters for PostgreSQL.

      Description     = ODBC for PostgreSQL
      Driver          = /usr/pgsql-9.5/lib/psqlodbc.so
      Setup           = /usr/lib64/libodbcpsqlS.so
       Driver64        = /usr/pgsql-9.5/lib/psqlodbc.so
       Setup64         = /usr/lib64/libodbcpsqlS.so
      FileUsage       = 1

4. Modify the /etc/odbc.ini file. Set the following parameters.

      Description         = PostgreSQL connection to postgres database
      Driver              = PostgreSQL
      Database            = postgres
      Servername          = 10.04.0.342
      UserName            = postgres
      Password            = superuser
      Port                = 5432
      Protocol            = 9.5
      ReadOnly            = No
      RowVersioning       = No
      ShowSystemTables    = No
      ConnSettings        =

5. Test psqlODBC.

       odbcinst -q -d
       It should print on screen: [PostgreSQL]
                                                [MySQL]

E. Install Oracle Instant Client

Refer Install Oracle Instant Client on Windows and linux to install Oracle Instant Client.

F. Install the Oracle Data Wrapper

Login as administrator.

1. Download the oracle_fdw (Souce code) from https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_1_3_0 to the /usr/pgsql-9.5/oracle directory.

      unzip oracle_fdw-ORACLE_FDW_1_3_0.zip

2. Compile the code.

      cd /usr/pgsql-9.5/oracle/fdw-ORACLE_FDW_1_3_0

       make
       make install

3. Copy the three files (oracle_fdw.control, oracle_fdw--1.0--1.1.sql, and oracle_fdw--1.1.sql) from /usr/pgsql-9.5/oracle/oracle_fdw/share/extension to /usr/pgsql-9.5/share/extension.

Copy the three sql files (oracle_fdw.sql, oracle_gis.sql, and oracle_import.sql) in the sql directory to /usr/pgsql-9.5/share/extension.


4. Copy the /usr/pgsql-9.5/oracle/oracle_fdw-ORACLE_FDW_1_3_0/oracle_fdw.so to the /usr/pgsql-9.5/lib/ and the /usr/pgsql-9.5/oracle/instantclient_12_1/lib directory.

log out and log back in as administrator.

Ensure that the environmental variables are set correctly.

     env

6. Test the oracle_fdw.

      su - postgres
   
      psql
   
     CREATE EXTENSION oracle_fdw;

      CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//11.15.0.112:1521/ORACLE');

      DROP SERVER oradb;

      DROP EXTENSION IF EXISTS oracle_fdw;

G. Migration from Oracle to PostgreSQL

Login as postgres
psql

1. Create the Foreign Data Wrapper server.

      CREATE EXTENSION oracle_fdw;

      CREATE FOREIGN DATA WRAPPER oracle_fdw_char HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator OPTIONS (nls_lang 'AMERICAN_AMERICA.WE8MSWIN1252')

      CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw_char 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 FOREIGN DATA WRAPPER IF EXISTS oracle_fdw_char

      DROP EXTENSION IF EXISTS oracle_fdw;

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

Watch the blessing and loving online channel: SupremeMasterTV live

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 for free here.