Could not open extension control file

[taneraruk]

Hi,

I get this error message when try to create oracle_fdw extension. I would appreciate if you help.

could not open extension control file "/usr/share/postgresql/14/extension/oracle_fdw.control": No such file or directory

control file is located under share folder,

pg_config --sharedir /usr/share/postgresql/14

ls -al /usr/share/postgresql/14/extension/ drwxrwxrwx 1 root root 4096 Feb 7 10:28 . drwxrwxrwx 1 root root 4096 Feb 7 10:28 .. -rwxrwxrwx 1 root root 231 Feb 7 10:28 oracle_fdw--1.0--1.1.sql -rwxrwxrwx 1 root root 240 Feb 7 10:28 oracle_fdw--1.1--1.2.sql -rwxrwxrwx 1 root root 1244 Feb 7 10:28 oracle_fdw--1.2.sql -rwxrwxrwx 1 root root 133 Feb 7 10:28 oracle_fdw.control

ORACLE_HOME set,

$ORACLE_HOME bash: /usr/lib/oracle/12.2/client64: Is a directory

There is no error during make install.

$LD_LIBRARY_PATH bash: /usr/lib/oracle/12.2/client64/lib: Is a directory

[laurenz]

That is indeed mysterious:

could not open extension control file "/usr/share/postgresql/14/extension/oracle_fdw.control": No such file or directory

but

pg_config --sharedir
/usr/share/postgresql/14

ls -al /usr/share/postgresql/14/extension/
drwxrwxrwx 1 root root 4096 Feb 7 10:28 .
drwxrwxrwx 1 root root 4096 Feb 7 10:28 ..
-rwxrwxrwx 1 root root 231 Feb 7 10:28 oracle_fdw--1.0--1.1.sql
-rwxrwxrwx 1 root root 240 Feb 7 10:28 oracle_fdw--1.1--1.2.sql
-rwxrwxrwx 1 root root 1244 Feb 7 10:28 oracle_fdw--1.2.sql
-rwxrwxrwx 1 root root 133 Feb 7 10:28 oracle_fdw.control

The only explanation that I have is that the first message is from a connection to a machine that is different from the one where you listed the files...

[taneraruk]

Sorry, maybe I am doing something completely wrong, I should give more details.

I have an Oracle database and I want to migrate it to Postgresql database. For migration I am using ora2pg application. We have a big amount of blob data thus migration takes too long. In this article it is advised to use oracle_fdw to fasten blob migration.

[laurenz]

How is that comment or that image related to this issue?

Did you read my comment?

[taneraruk]

Yes, that image shows what I try to do. I try to extract and read data from oracle and write it to postgresql db. I have installed oracle_fdw on ora2pg server.

[laurenz]

Let me repeat:

Did you make sure that the machine where you ran ls -al /usr/share/postgresql/14/extension/ is the PostgreSQL server (not the client) where you got the error message for CREATE EXTENSION?

In your picture, that would be the machine on the right side.

[taneraruk]

Sorry for late response, yes you are right, it is my bad, trying to run it on client. It works now. Thanks for your help.

[laurenz]

Thanks for the feedback, and good that it works for you.