Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Today I will talk about how we can Data Transfer From Oracle To PostgreSQL With Oracle_fdw. Before migrating data, I will give information about oracle_fdw.
What is oracle_fdw?
Oracle_fdw is an extension that allows you to access Oracle tables and views through foreign tables.
When the PostgreSQL client accesses a foreign table, Oracle_fdw accesses the corresponding data in the Oracle database via OCI on the PostgreSQL server.
In order to migrate data from Oracle database to PostgreSQL database, we need to do the following steps. Transactions were performed using Centos 7 and PostgreSQL 12.
1. We are installing Oracle Client on the PostgreSQL server.
1 | $ sudo yum install -y http://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm |
To control access to the Oracle database from the server, we install the sqlplus package with the following command.
1 | $ sudo yum install -y http://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.8-sqlplus-19.8.0.0.0-1.x86_64.rpm |
2. We add the IP and hostname information of the Oracle database server to the /etc/hosts file.
1 2 | $ vi /etc/hosts 1921.168.1.40 test test |
3. We test access via PostgreSQL server.
1 2 | $ /usr/lib/oracle/19.8/client64/bin/sqlplus \ <user>/<password>@test:1521/orcl |
4. For oracle_fdw installation, we install the PostgreSQL NonFree repo, which is suitable for PostgreSQL 12 version.
1 | $ yum install https://download.postgresql.org/pub/repos/yum/reporpms/non-free/EL-7-x86_64/pgdg-redhat-nonfree-repo-latest.noarch.rpm |
5. We install the oracle_fdw package.
1 | $ yum install oracle_fdw12.x86_64 |
6. The oracle_fdw extension is created by connecting to the database and we list the extensions in the database using the \dx command.
1 2 3 4 | $ su - postgres $ psql -d test -U melek test# CREATE EXTENSION oracle_fdw; test# \dx |
7. We are creating a foreign server.
1 2 3 4 5 6 7 | test# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//test:1521/orcl'); test=# \dew List of foreign-data wrappers Name | Owner | Handler | Validator ------------+-------+--------------------+---------------------- oracle_fdw | melek | oracle_fdw_handler | oracle_fdw_validator (1 row) |
8. We create mapping for PostgreSQL user angel and Oracle database user.
1 | test# CREATE USER MAPPING FOR melek SERVER oradb OPTIONS (USER '<username>', PASSWORD '<password>'); |
9. Finally, we create a foreign table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | test# CREATE FOREIGN TABLE ftbl_test ( id int OPTIONS (key 'true') NOT NULL, name varchar(20), lastname varchar(20) ) SERVER oradb OPTIONS (SCHEMA 'MELEK', TABLE 'PERSONS'); test=# table ftbl_test; id | name | lastname ----+--------+---------- 1 | MELEK | DURDU 2 | SENA | EROĞLU 3 | ALEYNA | GÜLEÇ |
In this article, I told you how we can move data from Oracle to PostgreSQL using oracle_fdw. See you in my next post.
