This article explain From postgres, how can we fetch oracle db data. For this we need to use foreign data wrapper and foreign table concepts.
DEMO:
1.Create oracle client on postgres server:
2. Install oracle_fdw extension on postgres server:
Download the package from github – https://github.com/laurenz/oracle_fdw
[root]# export ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1 [root]# export PATH=/usr/edb/as12/bin:$PATH [root]# [root]# cd /pgdata [root]# cd oracle_fdw-master/ [root]# make gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_utils.o oracle_utils.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_gis.o oracle_gis.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/edb/as12/lib -L/usr/libexec/edb-as12-icu53.1/lib/ -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/edb/as12/lib',--enable-new-dtags -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/bin" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.14/client64/lib -L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib -L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib -L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib -L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib -L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib -L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib [root]# make install /bin/mkdir -p '/usr/edb/as12/lib' /bin/mkdir -p '/usr/edb/as12/share/extension' /bin/mkdir -p '/usr/edb/as12/share/extension' /bin/mkdir -p '/usr/share/doc/extension' /bin/install -c -m 755 oracle_fdw.so '/usr/edb/as12/lib/oracle_fdw.so' /bin/install -c -m 644 .//oracle_fdw.control '/usr/edb/as12/share/extension/' /bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/usr/edb/as12/share/extension/' /bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/extension/'
3. Get postgres system service file.
[rootdata]# systemctl status edb-as-12.service
● edb-as-12.service - EDB Postgres Advanced Server 12
Loaded: loaded (/usr/lib/systemd/system/edb-as-12.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2022-04-18 18:23:33 +03; 17s ago
Main PID: 13437 (edb-postmaster)
Tasks: 12
CGroup: /system.slice/edb-as-12.service
├─13437 /usr/edb/as12/bin/edb-postmaster -D /var/lib/edb/as12/data
├─13440 postgres: logger
├─13442 postgres: checkpointer
├─13443 postgres: background writer
├─13444 postgres: walwriter
├─13445 postgres: autovacuum launcher
├─13446 postgres: stats collector
├─13447 postgres: dbms_aq launcher
├─13448 postgres: logical replication launcher
.
.
.
.............
--- as it is under /usr/lib/ location. better to copy the file to /etc/ and update the file there itself.
cp /usr/lib/systemd/system/edb-as-12.service /etc/systemd/system/edb-as-12.service
NOTE – > If you using pg_ctl command manually to stop and start postgres cluster(instead of service), then you just need to define the variables inside bash_profile. and restart using pg_ctl .
4.Update the service file with environment variables:
-bash-4.2$ cat /etc/systemd/system/edb-as-12.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/edb-as-12.service",
# containing
# .include /lib/systemd/system/edb-as-12.service
# ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
[Unit]
Description=EDB Postgres Advanced Server 12
After=syslog.target network.target
[Service]
Type=notify
Environment=ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1
Environment=TNS_ADMIN=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1/bin
Environment=LD_LIBRARY_PATH=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1/lib
User=enterprisedb
Group=enterprisedb
# Location of database directory
Environment=PGDATA=/var/lib/edb/as12/data
PIDFile=/var/lib/edb/as12/data/postmaster.pid
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA}
ExecStart=/usr/edb/as12/bin/edb-postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target
5.Reload the daemon and restart the postgres cluster:
[root~]# systemctl daemon-reload
[root ~]# systemctl stop edb-as-12.service
[root ~]# systemctl start edb-as-12.service
6.Create the extension oracle_fdw;
postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=#
postgres=# \dx oracle*
List of installed extensions
Name | Version | Schema | Description
------------+---------+--------+----------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
(1 row)
postgres=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
--------------+--------------+--------------------+--------------------------
dblink_fdw | enterprisedb | - | dblink_fdw_validator
libpq_dblink | enterprisedb | libpq_fdw_handler | edb_dblink_fdw_validator
oci_dblink | enterprisedb | oci_fdw_handler | edb_dblink_fdw_validator
oracle_fdw | enterprisedb | oracle_fdw_handler | oracle_fdw_validator
(4 rows)
7.Create foreign server:
postgres=# create server ora_gamedb foreign data wrapper oracle_fdw options (dbserver '10.21.45.64:1524/AMIIFDEV');
CREATE SERVER
postgres=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options |
Description
------------+--------------+----------------------+-------------------+------+---------+--------------------------------------------------------+
-------------
ora_gamedb | enterprisedb | oracle_fdw | | | | (dbserver '10.21.45.64:1524/AMIIFDEV') |
(2 rows)
8.Create user mapping:
— provide oracle database username and password
postgres=# create user mapping for enterprisedb server ora_gamedb options (USER 'dba_raj' , password 'dba_raj');
CREATE USER MAPPING
postgres=# \deu+
List of user mappings
Server | User name | FDW options
------------+--------------+----------------------------------------
ora_gamedb | enterprisedb | ("user" 'dba_raj', password 'dba_raj')
9.Now create the foreign table to access oracle data:
The table (EMP) structure has to be same as that of oracle database
postgres=# create foreign table EMP (EMP_NAME VARCHAR2(20), EMP_ID numeric) server ora_gamedb options ( schema 'DBA_RAJ' , table 'EMP');
CREATE FOREIGN TABLE
postgres=# select count(*) from EMP;
count
-------
1
^
postgres=# select * from emp;
emp_name | emp_id
----------+--------
KALAM | 1
(1 row)
COMMON ISSUES DURING SETUP:
1. If environmental variables are not set properly, then below error.
postgres=# select count(*) from EMP;
ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:
Make sure to set env values as explained in point 4 of this article.
2. If ld_library_path is not set or incorrect , then below error
postgres=# create extension oracle_fdw;
ERROR: could not load library "/usr/edb/as12/lib/oracle_fdw.so": libclntsh.so.19.1: cannot open shared object file: No such file or directory
Make sure to set LD_LIBRARY_PATH values as explained in point 4 of this article.
