dbi Blog

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:

root@debianpg:~ 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:

root@debianpg:~ mkdir -p /u01/app/postgres/product
root@debianpg:~ chown -R postgres:postgres /u01/app
root@debianpg:~ mkdir -p /u02/pgdata
root@debianpg:~ mkdir -p /u03/pgdata
root@debianpg:~ chown -R postgres:postgres /u0*/pgdata

Compile and install PostgreSQL from source:

postgres@debianpg:~$ PGHOME=/u01/app/postgres/product/95/db_4
postgres@debianpg:~$ SEGSIZE=2
postgres@debianpg:~$ BLOCKSIZE=8
postgres@debianpg:~$ WALSEGSIZE=16
postgres@debianpg:~$ wget https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ tar -axf postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ 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"
postgres@debianpg:~$ make world
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd contrib
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd ../..
postgres@debianpg:~$ rm -rf postgres*

Initialize a new cluster:

postgres@debianpg:~$ /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:

postgres@debianpg:~$ sed -i 's/#logging_collector = off/logging_collector = on/g' /u02/pgdata/PG1/postgresql.conf
postgres@debianpg:~$ mkdir /u02/pgdata/PG1/pg_log
postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/pg_ctl start -D /u02/pgdata/PG1/
postgres@debianpg:~$ /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:

postgres@debianpg:~$ cd /u01/app/
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-basic-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sdk-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/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:

postgres@debianpg:/u01/app$ export ORACLE_HOME=/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export PATH=$PATH:$ORACLE_HOME
postgres@debianpg:/u01/app$ which sqlplus
/u01/app/instantclient_12_1/sqlplus
postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242: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:

1	`root@debianpg:~ apt-get` `install` `libaio1`

Again:

postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242: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:

postgres@debianpg:~$ wget https://github.com/laurenz/oracle_fdw/archive/master.zip
postgres@debianpg:~$ unzip master.zip 
postgres@debianpg:~$ cd oracle_fdw-master/
postgres@debianpg:~/oracle_fdw-master$ export PATH=/u01/app/postgres/product/95/db_4/bin/:$PATH
postgres@debianpg:~/oracle_fdw-master$ which pg_config 
/u01/app/postgres/product/95/db_4/bin//pg_config
postgres@debianpg:~/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:

postgres@debianpg:/u01/app/instantclient_12_1$ cd /u01/app/instantclient_12_1
postgres@debianpg:/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:

postgres@debianpg:~/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
postgres@debianpg:~/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:

postgres@debianpg:~$ echo $ORACLE_HOME
/u01/app/instantclient_12_1
postgres@debianpg:~$ echo $LD_LIBRARY_PATH
:/u01/app/instantclient_12_1:/u01/app/instantclient_12_1/sdk/include/
postgres@debianpg:~$ pg_ctl -D /u02/pgdata/PG1/ restart -m fast
postgres@debianpg:~$ 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)