Infrastructure at your Service

Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database – Debian version

Some time ago I blogged about attaching your PostgreSQL instance to an Oracle database by using the oracle_fdw foreign data wrapper. This resulted in a comment which is the reason for this post: Doing the same with a Debian system where you can not use the rpm versions of the Oracle Instant Client (at least not directly). Lets go …

What I did to start with is to download the Debian 8 netinstall ISO and started from there with a minimal installation (see the end of this post for the screen shots of the installation if you are not sure on how to do it).

As I will compile PostgreSQL from source I’ll need to install the required packages:

[email protected]:~ apt-get install libldap2-dev libpython-dev libreadline-dev libssl-dev bison flex libghc-zlib-dev libcrypto++-dev libxml2-dev libxslt1-dev tcl tclcl-dev bzip2 wget screen ksh git unzip

Create the directory structure for my PostgreSQL binaries and instance:

[email protected]:~ mkdir -p /u01/app/postgres/product
[email protected]:~ chown -R postgres:postgres /u01/app 
[email protected]:~ mkdir -p /u02/pgdata
[email protected]:~ mkdir -p /u03/pgdata
[email protected]:~ chown -R postgres:postgres /u0*/pgdata 

Compile and install PostgreSQL from source:

[email protected]:~$ PGHOME=/u01/app/postgres/product/95/db_4
[email protected]:~$ SEGSIZE=2
[email protected]:~$ BLOCKSIZE=8
[email protected]:~$ WALSEGSIZE=16
[email protected]:~$ wget https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.bz2
[email protected]:~$ tar -axf postgresql-9.5.4.tar.bz2
[email protected]:~$ cd postgresql-9.5.4/
./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
            --with-extra-version=" dbi services build"
[email protected]:~$ make world
[email protected]:~$ make install
[email protected]:~$ cd contrib
[email protected]:~$ make install
[email protected]:~$ cd ../..
[email protected]:~$ rm -rf postgres*

Initialize a new cluster:

[email protected]:~$ /u01/app/postgres/product/95/db_4/bin/initdb -D /u02/pgdata/PG1 -X /u02/pgdata/PG1 --locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /u02/pgdata/PG1 ... ok
fixing permissions on existing directory /u02/pgdata/PG1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /u02/pgdata/PG1/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /u01/app/postgres/product/95/db_4/bin/pg_ctl -D /u02/pgdata/PG1 -l logfile start

Adjust the logging_collector parameter and startup the instance:

[email protected]:~$ sed -i 's/#logging_collector = off/logging_collector = on/g' /u02/pgdata/PG1/postgresql.conf
[email protected]:~$ mkdir /u02/pgdata/PG1/pg_log
[email protected]:~$ /u01/app/postgres/product/95/db_4/bin/pg_ctl start -D /u02/pgdata/PG1/
[email protected]:~$ /u01/app/postgres/product/95/db_4/bin/psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

postgres=

Download the Oracle Instant Client zip file from here. You’ll need these:

  • instantclient-basic-linux.x64-12.1.0.2.0.zip
  • instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
  • instantclient-sdk-linux.x64-12.1.0.2.0.zip

Extract to a location which fits your needs:

[email protected]:~$ cd /u01/app/
[email protected]:/u01/app$ unzip /home/postgres/instantclient-basic-linux.x64-12.1.0.2.0.zip
[email protected]:/u01/app$ unzip /home/postgres/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
[email protected]:/u01/app$ unzip /home/postgres/instantclient-sdk-linux.x64-12.1.0.2.0.zip
[email protected]:/u01/app$ ls -l
total 8
drwxr-xr-x 3 postgres postgres 4096 Sep 27 12:04 instantclient_12_1
drwxr-xr-x 4 postgres postgres 4096 Sep 27 10:57 postgres

Do a connection test with sqlplus to be sure the instant client is working in general:

[email protected]:/u01/app$ export ORACLE_HOME=/u01/app/instantclient_12_1
[email protected]:/u01/app$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/instantclient_12_1
[email protected]:/u01/app$ export PATH=$PATH:$ORACLE_HOME
[email protected]:/u01/app$ which sqlplus
/u01/app/instantclient_12_1/sqlplus
[email protected]:/u01/app$ sqlplus sh/[email protected]:1521/PROD
sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Ups, easy to fix:

[email protected]:~ apt-get install libaio1

Again:

[email protected]:/u01/app$ sqlplus sh/[email protected]:1521/PROD.local

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 12:12:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Sep 27 2016 12:09:05 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

Perfect. Connections are working to the Oracle instance. Continue with the oracle_fdw setup:

[email protected]:~$ wget https://github.com/laurenz/oracle_fdw/archive/master.zip
[email protected]:~$ unzip master.zip 
[email protected]:~$ cd oracle_fdw-master/
[email protected]:~/oracle_fdw-master$ export PATH=/u01/app/postgres/product/95/db_4/bin/:$PATH
[email protected]:~/oracle_fdw-master$ which pg_config 
/u01/app/postgres/product/95/db_4/bin//pg_config
[email protected]:~/oracle_fdw-master$ make
...
/usr/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
/u01/app/postgres/product/95/db_4/lib/pgxs/src/makefiles/../../src/Makefile.shlib:311: recipe for target 'oracle_fdw.so' failed
make: *** [oracle_fdw.so] Error 1

This one was unexpected. After some digging this resolves the issue:

[email protected]:/u01/app/instantclient_12_1$ cd /u01/app/instantclient_12_1
[email protected]:/u01/app/instantclient_12_1$ ln -s libclntsh.so.12.1 libclntsh.so

Not sure if I missed something or this is a bug (you can follow the issue here).

Once the link is there you’ll be able to “make” and to “make install”. This is the result:

[email protected]:~/oracle_fdw-master$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/u01/app/postgres/product/95/db_4/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/product/95/db_4/lib',--enable-new-dtags  -L/u01/app/instantclient_12_1 -L/u01/app/instantclient_12_1/bin -L/u01/app/instantclient_12_1/lib -L/u01/app/instantclient_12_1/sdk/include -lclntsh -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 -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib 
[email protected]:~/oracle_fdw-master$ make install
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/lib'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/u01/app/postgres/product/95/db_4/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/u01/app/postgres/product/95/db_4/share/doc/extension/'

Remember that the PostgreSQL instance needs to find the Oracle libraries, so set the environment before restarting PostgreSQL:

[email protected]:~$ echo $ORACLE_HOME
/u01/app/instantclient_12_1
[email protected]:~$ echo $LD_LIBRARY_PATH
:/u01/app/instantclient_12_1:/u01/app/instantclient_12_1/sdk/include/
[email protected]:~$ pg_ctl -D /u02/pgdata/PG1/ restart -m fast
[email protected]:~$ psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= create extension oracle_fdw;
CREATE EXTENSION
postgres= \dx
                        List of installed extensions
    Name    | Version |   Schema   |              Description               
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.1     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

All fine. Lets get the foreign data:

postgres= create schema oracle;
CREATE SCHEMA
postgres= create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres= create user mapping for postgres server oracle options (user 'sh', password 'sh');
CREATE USER MAPPING
postgres= import foreign schema "SH" from server oracle into oracle;
IMPORT FOREIGN SCHEMA
postgres= set search_path='oracle';
SET
postgres= \d
                       List of relations
 Schema |            Name            |     Type      |  Owner   
--------+----------------------------+---------------+----------
 oracle | cal_month_sales_mv         | foreign table | postgres
 oracle | channels                   | foreign table | postgres
 oracle | costs                      | foreign table | postgres
 oracle | countries                  | foreign table | postgres
 oracle | currency                   | foreign table | postgres
 oracle | customers                  | foreign table | postgres
 oracle | dimension_exceptions       | foreign table | postgres
 oracle | fweek_pscat_sales_mv       | foreign table | postgres
 oracle | products                   | foreign table | postgres
 oracle | profits                    | foreign table | postgres
 oracle | promotions                 | foreign table | postgres
 oracle | sales                      | foreign table | postgres
 oracle | sales_transactions_ext     | foreign table | postgres
 oracle | supplementary_demographics | foreign table | postgres
 oracle | times                      | foreign table | postgres
(15 rows)

postgres= select count(*) from countries;
 count 
-------
    23
(1 row)

Perfect, works. Hope this helps.

Debian 8 installation screen shots:

Selection_014
Selection_015
Selection_016
Selection_017
Selection_018
Selection_020
Selection_021
Selection_022
Selection_023
Selection_024
Selection_025
Selection_026
Selection_027
Selection_028
Selection_029
Selection_030
Selection_031
Selection_032
Selection_033
Selection_034
Selection_036
Selection_037
Selection_038
Selection_039
Selection_040
Selection_041
Selection_042
Selection_043
Selection_044
Selection_045
Selection_046
Selection_047
Selection_048

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure