Обсуждение: [ADMIN] error installing oracle_fdw extension

Sumeet Shukla <sumeet.k.shukla@gmail.com> writes:
> I'm seeeing below error while installing oracle_fdw

Looks like the version of oracle_fdw you're trying to install has
not been updated for PG 9.6.  You need to ask its developers
whether there's a newer version.

            regards, tom lane

On 5/19/17 10:41, Tom Lane wrote:
> Sumeet Shukla <sumeet.k.shukla@gmail.com> writes:
>> I'm seeeing below error while installing oracle_fdw
>
> Looks like the version of oracle_fdw you're trying to install has
> not been updated for PG 9.6.  You need to ask its developers
> whether there's a newer version.

Yes, there are newer versions that support 9.6 and 10.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Adding Laurenz

On 22/05/2017 07:56, Sumeet Shukla wrote:
> Thanks Tom and Peter. I tried installing the latest version
> oracle_fdw-1.5.0 and now I see below error:
>
> -bash-4.1$ make
> gcc -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -g -O2
> -DMAP_HUGETLB=0x40000 -fpic
> -I/u01/app/oracle/product/12.1.0.2/db_1/sdk/include
> <http://12.1.0.2/db_1/sdk/include>
> -I/u01/app/oracle/product/12.1.0.2/db_1/oci/include
> <http://12.1.0.2/db_1/oci/include>
> -I/u01/app/oracle/product/12.1.0.2/db_1/rdbms/public
> <http://12.1.0.2/db_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 <http://10.2.0.5/client>
> -I/usr/include/oracle/10.2.0.5/client64 <http://10.2.0.5/client64>
> -I/usr/include/oracle/10.2.0.4/client <http://10.2.0.4/client>
> -I/usr/include/oracle/10.2.0.4/client64 <http://10.2.0.4/client64>
> -I/usr/include/oracle/10.2.0.3/client <http://10.2.0.3/client>
> -I/usr/include/oracle/10.2.0.3/client64 <http://10.2.0.3/client64> -I.
> -I./ -I/opt/postgresql/9.6/include/postgresql/server
> -I/opt/postgresql/9.6/include/postgresql/internal -D_GNU_SOURCE
>  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c
> -o oracle_utils.o oracle_utils.c
> oracle_utils.c:22:17: error: oci.h: No such file or directory
Did you also install Oracle SDK files in the directory pointed by
$ORACLE_HOME?

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

Sumeet Shukla wrote:
> I have below environment variables set:
> 
> 
> export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
> export ORACLE_HOME=/usr/lib/oracle/12.1/client64
> PATH=$PATH:/usr/lib/oracle/12.1/client64/bin:/usr/lib/oracle/12.1/client64/lib:/usr/lib/or
> acle/12.1/client64:/usr/lib/oracle/12.1/client64/sdk
> export PATH
> 
> The file /opt/postgresql/9.6/lib/postgresql/oracle_fdw.so is available.
> 
> I have also added "include /usr/lib/oracle/12.1/client64/lib" in /etc/ld.so.conf
> 
> I still see below error when I perform "create extension oracle_fdw;"
> 
> 
> ERROR: could not load library "/opt/postgresql/9.6/lib/postgresql/oracle_fdw.so":
> libclntsh.so.12.1: cannot open shared object file: No such file or directory

Two possibilities:

- There is no libclntsh.so.12.1 in /usr/lib/oracle/12.1/client64/lib
- The environment variable LD_LIBRARY_PATH is not set in the postmaster's environment.

For the latter (assuming you are using Linux), find out the process ID of the
PostgreSQL server process (postmaster).

Assuming it is 12345, run this:
$ cat /proc/1234/environ | xargs -n 1 -0 echo

And see if LD_LIBRARY_PATH is set correctly there.

Yours,
Laurenz Albe

Sumeet Shukla wrote:
> Yes, postmaster does not seem to be aware of  LD_LIBRARY_PATH. I tried ldconfig and
> postgres service restart but it does not seem to work. How can I fix this.

You first have to add a line to /etc/ld.so.conf that reads
/usr/lib/oracle/12.1/client64/lib

Then run "ldconfig".

> Also the owner of  /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 is root. Please
> confirm if it is OK.

Yes, as long as the PostgreSQL user has permission to read the file.

Yours,
Laurenz Albe

Sumeet Shukla wrote:
> The environment variable $LD_LIBRARY_PATH reflects if I use "/etc/init.d/postgresql-9.6
> restart" command but it does not reflect if I use "service postgresql-9.6 restart".
> 
> I find it little weird since essentially both the commands are internally calling the same
> service file. Any insight on this would be appreciated.

From "man service":

   ENVIRONMENT
      LANG, TERM
         The only environment variables passed to the init scripts.

Yours,
Laurenz Albe

On 5/23/17 05:04, Albe Laurenz wrote:
> Sumeet Shukla wrote:
>> The environment variable $LD_LIBRARY_PATH reflects if I use "/etc/init.d/postgresql-9.6
>> restart" command but it does not reflect if I use "service postgresql-9.6 restart".
>>
>> I find it little weird since essentially both the commands are internally calling the same
>> service file. Any insight on this would be appreciated.
>
> From "man service":
>
>    ENVIRONMENT
>       LANG, TERM
>          The only environment variables passed to the init scripts.

Yeah, one of the main points of the service program is that it clears
your environment.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services