Skip to content

📚 oracle_fdw enabled

🔍 Prepare：

## OS and Software environment：

Environment name Version Download address
OS Rocky Linux 9.x Rocky Download
DB PostgreSQL 16.X PostgreSQL Download（YUM Repository）
Oracle Client PostgreSQL 16.X Oracle Client Download（ZIP）
Oracle FDW PostgreSQL 16.X Oracle FDW Download（ZIP）

## Download below packages（Example PG 16.8）：

Package name Download address
postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
instantclient-basic-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
instantclient-sqlplus-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
instantclient-sdk-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
oracle_fdw-master.zip Click Download RPM Package

### 1. Before installing the FDW, need to install the related packages for postgresql16-devel-16.* （Use root Account，if you install postgresql16-devel-* on create software setup，you can skip）

  • 1.1.1 Check rpm package install or
rpm -qa |grep postgresql16-devel

alt text

you can use rpm -qa |grep postgresql16-devel to check ，if you install ，skip 1

  • 1.1.2 Check rpm package depend
rpm -qpR /pgdata/source/postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm

alt text

  • 1.1.3 install package
yum install -y libicu.x86_64
yum install -y libxslt.x86_64
yum install -y perl-libs.x86_64
yum install -y lz4
yum install -y llvm
yum install -y perl
dnf --enablerepo=devel install perl-IPC-Run -y
yum install -y libicu
  • 1.1.4 install postgresql16-devel-16.*
rpm -ivh /pgdata/postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm
rpm -qa |grep postgresql16-devel-16.8-1PGDG.rhel9.x86_64
chown -R postgres. /usr/pgsql-16

alt text

### 2. Install and Create oracle_fdw extension

  • 2.1 put instantclient(basic、sqlplus、sdk) to /pgdata/source/

alt text

  • 2.2 unzip the 3 zip file and rename the folder from instantclient_21_X to instantclient
mkdir /opt/oracle/
unzip /pgdata/source/instantclient-basic-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/

alt text

unzip /pgdata/source/instantclient-sqlplus-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/

alt text

unzip /pgdata/source/instantclient-sdk-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/

alt text

mv /opt/oracle/instantclient_21_19 /opt/oracle/instantclient

alt text

  • 2.3 set up the environment parameter .bash_profile on postgres
su - postgres
vi .bash_profile
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include

LD_LIBRARY_PATH=/usr/pgsql-16/lib:/opt/oracle/instantclient
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

alt text

  • 2.4 Put the file to /pgdata/source/ and Unzip oracle_fdw-master.zip
su - root
unzip /pgdata/source/oracle_fdw-master.zip -d /pgdata/source/

alt text

  • 2.5 Install oracle_fdw
chown -R postgres:postgres /pgdata/source/oracle_fdw-master
su - postgres
cd /pgdata/source/oracle_fdw-master
make install

alt text

  • 2.6 make sure all the ldd are ready for oracle_fdw.so
su - root
ldd /usr/pgsql-16/lib/oracle_fdw.so

alt text

cp /opt/oracle/instantclient/libclntsh.so.21.1 /lib64/
cp /opt/oracle/instantclient/libnnz21.so /lib64/
cp /opt/oracle/instantclient/libclntshcore.so.21.1 /lib64/
ldconfig
ldd /usr/pgsql-16/lib/oracle_fdw.so

alt text

### 3. Create oracle_fdw Extension On postgres DB（Corresponding DB Creation，Example On postgres）

su - postgres
psql -p 5435

CREATE EXTENSION oracle_fdw;

alt text

alt text

### 4. Create Foreign Server

\des
CREATE SERVER "databasename_schemaname" FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP:PORT/SID');
ALTER SERVER "databasename_schemaname" OWNER TO postgres;
GRANT USAGE ON FOREIGN SERVER "databasename_schemaname" TO username;
\des
select * from pg_foreign_server;

### 5. Create User Mapping

\deu
CREATE USER MAPPING FOR "public" SERVER "databasename_schemaname" OPTIONS ("user" 'DBSNMP', password '1234');
\deu
select * from pg_user_mappings;

If the target Oracle database password is not case sensitive, the password must be capitalized

### 6. Create Foreign Table

  • 6.1 Manually Specifying Field Properties
\det
create foreign table "BSLN_METRIC_DEFAULTS" (METRIC_ID int,STATUS varchar(16), CATEGORY varchar(16)) server "databasename_schemaname" options (schema 'DBSNMP', table 'BSLN_METRIC_DEFAULTS');
\det
select ftrelid::regclass,* from pg_foreign_table;

Schema must be capitalized

  • 6.2 Import Foreign Table（Automatically Specifying Field Properties）

    alt text

    • 6.2.1 Import Remote_Schema one or more Table SFCS Execute on Their User Account
    \det
IMPORT FOREIGN SCHEMA "DBSNMP" LIMIT TO (BSLN_METRIC_DEFAULTS,BSLN_METRIC_DEFAULTS2) FROM SERVER "databasename_schemaname" INTO schemaname;
\det
select ftrelid::regclass,* from pg_foreign_table;
    • 6.2.2 Import Remote_Schema All Table
    \det
IMPORT FOREIGN SCHEMA "DBSNMP" FROM SERVER "databasename_schemaname" INTO schemaname;
\det
select ftrelid::regclass,* from pg_foreign_table;

### 7. Drop Foreign Server/user mapping/foreign table

  • 7.1 Drop foreign Server

    Drop Server "databasename_schemaname";

  • 7.2 Drop user mapping

    DROP USER MAPPING IF EXISTS FOR public SERVER "databasename_schemaname";

  • 7.3 Drop foreign table

    Drop Foreign Table BSLN_METRIC_DEFAULTS;

Drop foreign table（Drop External Tables Will Not Drop Remote Tables, If There Is A Drop Or Rename In The Remote Table, The Local PG tables Update Will Not Be Synchronized And External Table Queries Will Be Made, Indicating That It Does Not Exist）