Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension
Hi Laurenz, Thanks for your reply. Sorry for the double posting here and StackOverflow http://stackoverflow.com/questions/39518417/unable-to-create-oracle-fdw-extension-on-postgres . I will update the details on StackOverflow since formatting and editing are easier there. On Fri, Sep 16, 2016 at 3:14 AM, Albe Laurenz wrote: > Arun Rangarajan wrote: > > But when I try to create the extension, I get the following error: > > > > postgres=# create extension oracle_fdw; > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > > t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG: 0: server process > (PID 20397) was terminated by signal 11: Segmentation fault > > Well, as I told you, get a stack trace with debugging symbols. > > Yours, > Laurenz Albe >
Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension
Thanks again, Scott. No success yet though. I uninstalled Oracle instant client 12.1 using dpkg, downloaded RPMs for instant client 10.2 and installed them with alien. I also downloaded oracle_fdw 1.4 and installed it, since I was getting this error with oracle_fdw 1.5: --- postgres=# create extension oracle_fdw; ERROR: could not load library "/usr/lib/postgresql/9.4/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory --- Once I installed oracle_fdw 1.4 I got this: # ldd /usr/lib/postgresql/9.4/lib/oracle_fdw.so linux-vdso.so.1 => (0x7ffc3b5ec000) libclntsh.so.10.1 => /usr/lib/oracle/10.2.0.3/client64/lib/libclntsh.so.10.1 (0x7f7251471000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f72510e6000) libnnz10.so => /usr/lib/oracle/10.2.0.3/client64/lib/libnnz10.so (0x7f7250c42000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f7250a3e000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f72507bc000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x7f725059f000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f7250387000) /lib64/ld-linux-x86-64.so.2 (0x7f7252a3b000) But again when I try to load the extension I get the same error and Postgres crashes. When I look in the latest log file at /var/lib/postgresql/9.4/main/pg_log I see these lines: --- t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG: 0: server process (PID 20397) was terminated by signal 11: Segmentation fault t=2016-09-15 16:16:08 PDT d= h= p=25327 a=DETAIL: Failed process was running: create extension oracle_fdw; t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION: LogChildExit, postmaster.c:3347 t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG: 0: terminating any other active server processes t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION: HandleChildCrash, postmaster.c:3068 t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] WARNING: 57P02: terminating connection because of crash of another server process t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] HINT: In a moment you should be able to reconnect to the database and repeat your command. t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] LOCATION: quickdie, postgres.c:2581 t=2016-09-15 16:16:08 PDT d= h= p=19668 a=WARNING: 57P02: terminating connection because of crash of another server process --- On Thu, Sep 15, 2016 at 2:58 PM, Scott Mead wrote: > > > On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan > wrote: > >> Thanks, Scott. >> >> oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/ >> >> Oracle client version: instantclient 12.1 >> > > I've had problems using anything > instant client 10. Give it a shot. > > --Scott > > > >> >> /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so >> linux-vdso.so.1 => (0x7fff50744000) >> libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 >> (0x7f44769f1000) >> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f447000) >> libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so >> (0x7f4475f4f000) >> libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so >> (0x7f4475d0b000) >> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f4475b07000) >> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f4475884000) >> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 >> (0x7f4475668000) >> libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f447545) >> librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x7f4475247000) >> libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x7f4475045000) >> libclntshcore.so.12.1 => >> /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 >> (0x7f4474af5000) >> /lib64/ld-linux-x86-64.so.2 (0x7f447990c000) >> >> >> >> On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead wrote: >> >>> >>> >>> On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan < >>> arunrangara...@gmail.com> wrote: >>> >>>> I am following the instructions here: >>>> http://blog.dbi-services.com/connecting-your-postgresql-inst >>>> ance-to-an-oracle-database/ >>>> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL >>>> server. >>>> >>>> --- >>>> Oracle version: Oracle Database 12c Enterpris
Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension
Thanks, Scott. oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/ Oracle client version: instantclient 12.1 /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so linux-vdso.so.1 => (0x7fff50744000) libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x7f44769f1000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f447000) libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x7f4475f4f000) libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x7f4475d0b000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f4475b07000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f4475884000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x7f4475668000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f447545) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x7f4475247000) libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x7f4475045000) libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x7f4474af5000) /lib64/ld-linux-x86-64.so.2 (0x7f447990c000) On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead wrote: > > > On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan > wrote: > >> I am following the instructions here: >> http://blog.dbi-services.com/connecting-your-postgresql-inst >> ance-to-an-oracle-database/ >> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL >> server. >> >> --- >> Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 >> - 64bit Production, running on Red Hat Linux 7.2 >> >> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, >> compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7 >> (wheezy). >> --- >> >> I was able to install sqlplus and connect from PostgreSQL server to >> Oracle server using sqlplus successfully, so connectivity is not a problem. >> >> But when I try to create the extension, I get the following error: >> >> > --- >> postgres=# create extension oracle_fdw; >> server closed the connection unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> The connection to the server was lost. Attempting reset: Failed. >> > > Hmm, odd that it's causing a crash. > > Which version of the oracle_fdw and which version of the oracle > libraries are you linked to? Make sure to check 'ldd oracle_fdw.so' > > --Scott > > > >> --- >> >> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1 >> and added oracle_fdw to shared_preload_libraries in postgresql.conf like >> this: >> >> shared_preload_libraries = 'oracle_fdw' >> >> but now I can't restart Postgres: >> >> --- >> # service postgresql restart >> [] Restarting PostgreSQL 9.4 database server: main[] The >> PostgreSQL server failed to start. Please check the log output: >> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache >> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: >> SearchSysCacheList, syscache.c:1219 ... failed! >> failed! >> --- >> >> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see >> these two lines: >> >> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache >> ID: 41 >> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, >> syscache.c:1219 >> >> Removing oracle_fdw from shared_preload_libraries allows postgres to be >> restarted, so this is the one causing restart to fail. >> >> How to fix this and get the foreign data wrapper working? >> >> Thank you. >> >> >> > > > > -- > -- > Scott Mead > Sr. Architect > *OpenSCG <http://openscg.com>* > http://openscg.com >
[GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension
I am following the instructions here: http://blog.dbi-services.com/connecting-your-postgresql-instance-to-an-oracle-database/ to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL server. --- Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, running on Red Hat Linux 7.2 PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7 (wheezy). --- I was able to install sqlplus and connect from PostgreSQL server to Oracle server using sqlplus successfully, so connectivity is not a problem. But when I try to create the extension, I get the following error: --- postgres=# create extension oracle_fdw; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. --- Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1 and added oracle_fdw to shared_preload_libraries in postgresql.conf like this: shared_preload_libraries = 'oracle_fdw' but now I can't restart Postgres: --- # service postgresql restart [] Restarting PostgreSQL 9.4 database server: main[] The PostgreSQL server failed to start. Please check the log output: t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219 ... failed! failed! --- Looking into /var/log/postgresql/postgresql-9.4-main.log I only see these two lines: t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache ID: 41 t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219 Removing oracle_fdw from shared_preload_libraries allows postgres to be restarted, so this is the one causing restart to fail. How to fix this and get the foreign data wrapper working? Thank you.