Обсуждение: Unable to create oracle_fdw (foreign data wrapper) extension
I am following the instructions here:
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.
On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan@gmail.com> wrote:
I am following the instructions here: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.2PostgreSQL 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 unexpectedlyThis probably means the server terminated abnormallybefore 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: 41t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219Removing 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.
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 => (0x00007fff50744000)
libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f44769f1000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4476666000)
libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f4475f4f000)
libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f4475d0b000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4475b07000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4475884000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f4475668000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4475450000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4475247000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4475045000)
libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f4474af5000)
/lib64/ld-linux-x86-64.so.2 (0x00007f447990c000)
On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan@gmail.com> wrote:I am following the instructions here: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.2PostgreSQL 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 unexpectedlyThis probably means the server terminated abnormallybefore 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: 41t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219Removing 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.--
On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan <arunrangarajan@gmail.com> 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.solinux-vdso.so.1 => (0x00007fff50744000)libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f44769f1000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4476666000) libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f4475f4f000) libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f4475d0b000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4475b07000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4475884000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f4475668000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4475450000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4475247000) libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4475045000) libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f4474af5000) /lib64/ld-linux-x86-64.so.2 (0x00007f447990c000)On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan@gmail.com> wrote:I am following the instructions here: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.2PostgreSQL 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 unexpectedlyThis probably means the server terminated abnormallybefore 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: 41t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219Removing 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.--
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 => (0x00007ffc3b5ec000)
libclntsh.so.10.1 => /usr/lib/oracle/10.2.0.3/ client64/lib/libclntsh.so.10.1 (0x00007f7251471000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so. 6 (0x00007f72510e6000)
libnnz10.so => /usr/lib/oracle/10.2.0.3/ client64/lib/libnnz10.so (0x00007f7250c42000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl. so.2 (0x00007f7250a3e000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so. 6 (0x00007f72507bc000)
libpthread.so.0 => /lib/x86_64-linux-gnu/ libpthread.so.0 (0x00007f725059f000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl. so.1 (0x00007f7250387000)
/lib64/ld-linux-x86-64.so.2 (0x00007f7252a3b000)
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: 00000: 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: 00000: 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 <scottm@openscg.com> wrote:
On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan <arunrangarajan@gmail.com> wrote:Thanks, Scott.oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/ Oracle client version: instantclient 12.1I've had problems using anything > instant client 10. Give it a shot.--Scott/usr/lib/postgresql/9.4/lib# ldd oracle_fdw.solinux-vdso.so.1 => (0x00007fff50744000)libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f44769f1000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4476666000) libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f4475f4f000) libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f4475d0b000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4475b07000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4475884000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f4475668000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4475450000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4475247000) libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4475045000) libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f4474af5000) /lib64/ld-linux-x86-64.so.2 (0x00007f447990c000)On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan@gmail.com> wrote:I am following the instructions here: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.2PostgreSQL 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 unexpectedlyThis probably means the server terminated abnormallybefore 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: 41t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219Removing 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.--
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: 00000: server process (PID 20397) was terminated by signal 11: Segmentationfault Well, as I told you, get a stack trace with debugging symbols. Yours, Laurenz Albe
Hi Laurenz,
Thanks for your reply.
Sorry for the double posting here and StackOverflow
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 <laurenz.albe@wien.gv.at> 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: 00000: 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
On 16.09.2016 17:01, Arun Rangarajan wrote: > 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 <laurenz.albe@wien.gv.at <mailto:laurenz.albe@wien.gv.at>> 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: 00000: server process (PID 20397) was terminated by signal 11: Segmentationfault > > Well, as I told you, get a stack trace with debugging symbols. > > Yours, > Laurenz Albe > > Hi! May be this helps: "Don't add oracle_fdw to shared_preload_libraries! (c) Laurenz Albe Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company