Monday , July 4 2022

Data Transfer From Oracle To PostgreSQL With Oracle_fdw

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.

Accessing Oracle Database

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.

To control access to the Oracle database from the server, we install the sqlplus package with the following command.

2. We add the IP and hostname information of the Oracle database server to the /etc/hosts file.

3. We test access via PostgreSQL server.

4. For oracle_fdw installation, we install the PostgreSQL NonFree repo, which is suitable for PostgreSQL 12 version.

5. We install the oracle_fdw package.

6. The oracle_fdw extension is created by connecting to the database and we list the extensions in the database using the \dx command.

7. We are creating a foreign server.

8. We create mapping for PostgreSQL user angel and Oracle database user.

9. Finally, we create a foreign table.

In this article, I told you how we can move data from Oracle to PostgreSQL using oracle_fdw. See you in my next post.

Melek Durdu
Author: Melek Durdu

About Melek Durdu

Leave a Reply

Your email address will not be published. Required fields are marked *