In the article “Postgresql Foreign Data Wrapper“, we created Foreign Data Wrapper to read data between two PostgreSQLs.
And we have created Foreing Data Wrapper Between PostgreSQL and MSSQL in the article named “PostgreSQL Foreign Data Wrapper Between PostgreSQL and MSSQL“.
In this article, we will create Foreign Data Wrapper between PostgreSQL and Oracle.
When installing Oracle Foreign Data Wrapper, we need to install oracle instant client.
In the instant client, we need to install the following rmps;
For installation, please refer to the article titled “How To Install Oracle Client For PostgreSQL On Centos“.
If you have a standard installation on Postgresql, you should download and install postgresql-devel and postresql-lib rpm packages according to your version.
After downloading and installing the required oracle_client version, you can install oracle_fdw by following the steps below.
First we download the Oracle_fdw extension, you can use the link below to download.
After downloading Oracle_fdw extension files and transferring them to any folder on our machine, we go the folder and execute the following command.
After running this command, the result should return as follows.
You may receive errors as follows when you run this command. You can find solutions to these errors in the following articles.
After the above operation is completed, you can continue by running the next command.
Finally, we execute the following command and our operations at the operating level are completed.
The purpose of this process is to compile the PostgreSQL code and the oracle_fdw code together.
After connecting to postgres with psql, we run the following command to create the extension.
create extension oracle_fdw;
After the extension is installed, we create the server as in other extension installations.
CREATE SERVER oradb1 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'oracle_ip:1521');
We create user mapping.
CREATE USER MAPPING FOR pguser SERVER oradb1 OPTIONS (USER 'oracleuser', PASSWORD 'OracleUserPassword');
CREATE FOREIGN TABLE:
And finally, we create a foreign table.
CREATE FOREIGN TABLE ftbl_ora_test1
id int OPTIONS (key 'true') NOT NULL,
SERVER oradb1 OPTIONS (SCHEMA 'oracle_schema', TABLE 'oracle_table');