CREATE EXTENSION oracle_fdw; ERROR: could not load library "/pgsql/g01/software/rdbms/9.3.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

[cmodomingo]

Hi Laurenz,

Needing your expert advice on my issue here. I followed the below steps to install the extension and didn't ran into any error during the install but when i try to create the extension in postgresql error mentioned above in the subject is being encountered.

set ORACLE_HOME
executed make
executed make install

Thank you in advance for your help.

[laurenz]

I guess that libclntsh.so.11.1 cannot be found on the library load path.

The README has the following to say on the topic:

Since the Oracle client shared library is probably not in the standard
library path, you have to make sure that the PostgreSQL server will be able
to find it. How this is done varies from operating system to operating
system; on Linux you can set LD_LIBRARY_PATH or use /etc/ld.so.conf.

Make sure that all necessary Oracle environment variables are set in the
environment of the PostgreSQL server process (ORACLE_HOME if you don't use
Instant Client, TNS_ADMIN if you have configuration files, etc.)

What measures did you take to put the Oracle client shared library on the library load path?

[cmodomingo]

Hi Laurenz,

I already set the LD_LIBRARY_PATH to look into oracle client library wherein, libclntsh.so.11.1. is located. I have also set ORACLE_HOME in the environment but Im still getting the error.

Thank you in advance :)

[laurenz]

You didn't tell me your operating system, I'll assume it is Linux.

You can examine the environment of the process with

cat /proc/<pid>/environ | xargs -0 -n 1

where <pid> is the process ID of the PostgreSQL server process (postmaster).

Check if LD_LIBRARY_PATH and ORACLE_HOME are really set there.
If not, you'll probably have to set the variables in the PostgreSQL startup script.

The much better method is to use /etc/ld.so.conf (see man ldconfig).

[cmodomingo]

Hi Laurenz,

This is the current OS version the I'm using. CentOS Linux release 7.1.1503 (Core). I will try to collaborate with our SysAd on your recommendation. Will keep you posted on the updates.

Thank you so much :)

[cmodomingo]

Hi Laurenz,

I'm able to create the oracle_fdw extension now. I did execute the command you gave above to examine the environment of the postmaster and I didn't see there the LD_LIBRARY_PATH and ORACLE_HOME, what I did is I included these in the .bash_profile to have it permanently and then restarted postmaster. I tried to stop and start postmaster to test if an error will occur and it did not.

Thank you so much for your help. I really do appreciate it.

[laurenz]

A word of caution:
If you manually start the server, it will inherit the environment setting from your shell.
But if PostgreSQL is started from a startup script, e.g. when the machine is booted,
you will not have the environment setting, and things will suddenly stop working.

That's why I recommended to set the variables in the PostgreSQL startup script.
As I said before, using ldconfig is much better (see http://xahlee.info/UnixResource_dir/_/ldpath.html).