PostgreSql: Set environment variables

Question

I have installed the oracle_fdw plugin for postgreSql and have reached a point where I could create a foreign table. But when I try to query this table, I get the following error:

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

I went through the following thread where this exact issue is discussed:

OCIEnvCreate failed to create environment handle. #133

The thing is that I am not able to set the env variable for ORACLE_HOME. Below is the command to check the env and the corresponding output of the running postgres process:

:$ sudo cat /proc/4885/environ | xargs -0 -n 1 echo | sort
LANG=en_IN
PGDATA=/var/lib/postgresql/9.6/main
PG_GRANDPARENT_PID=4880
PGLOCALEDIR=/usr/share/locale
PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
PGSYSCONFDIR=/etc/postgresql-common
PWD=/

I have tried to export this variable in all possible places but to no avail. I referred the following threads:

https://unix.stackexchange.com/questions/117467/how-to-permanently-set-environmental-variables/117470

http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x486_007.htm

Thus I have added env variables in the following files: (after loging in as postgres using sudo su postgres)

~/.bashrc
~/.bash_profile
~/.bash_login
~/.login
~/.profile
/etc/profile

In all of these files I have the following line:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

Am I missing something here?

PS: I am on Ubuntu 17.10 using Postgres 9.6

Did you use sudo su - postgres ? (the extra hyphen is important) — joop
– joop, Commented Oct 27, 2017 at 10:10
No, just tried it. I checked and saw that all the above files still contain the export statement so nothing really changed. Do I need to do something else here? Thanks for your response though. — DaSarfyCode
– DaSarfyCode, Commented Oct 27, 2017 at 10:16
Sorry, forget it. It is only valid for interactive shells, anyway. — joop
– joop, Commented Oct 27, 2017 at 10:20
My first test would be to login as the postgres user in Linux, then try to run sqlplus or any other Oracle client tool from there. Once you get that working, chances are the Postgres process can successfully create an OCI connection. — user330315
– user330315, Commented Oct 27, 2017 at 10:26
Thanks, sqlplus works from postgres user, but I have to give the complete library path (/u01/app/oracle/product/11.2.0/xe/bin/sqlplus). It does not find it otherwise. — DaSarfyCode
– DaSarfyCode, Commented Oct 27, 2017 at 11:22

Laurenz Albe · Accepted Answer · 2017-10-27 10:51:47Z

1

All the locations where you added the variable are only used by shells, so it will only work if you start PostgreSQL from the command line.

It depends on your startup procedure what the best place is: with System V init scripts you can edit the script, with systemd there is a special keyword in the service file. There might be other ways for some Linux distributions.

To address your real problem, you only need ORACLE_HOME if you are not using Instant Client, so I am not certain that that is your problem.

Unfortunately this is the generic error message if Oracle has problems initializing, so it is very hard to diagnose. It can be a bad Oracle environment variable, but I have also seen it if there is a mixup of several Oracle products installed on a single machine, so try to avoid that.

It may be a good idea to trace the backend process with strace so you can see which files Oracle tries to access.

If you have Oracle's source code, a debugger can help (only joking).

answered Oct 27, 2017 at 10:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DaSarfyCode Over a year ago

Hi Laurenz, thanks a lot for the answer. I raised the same issue in the github repo btw. I can close if you want. So, you mean to say that is the Oracle client issue (or is it the server?). I was thinking may be it is an issue from postgres side as it cannot be the ORACLE_HOME variable in the environment. Also, I have not added the LD_LIBRARY_PATH in the env, but just added the path in /etc/ld.so.conf.d/xxx.conf. Can that be causing the issue? This problem is very similar to the issue I have referenced in the question where you have replied. I followed it, but could not set the ORACLE_HOME.

Laurenz Albe Over a year ago

Your library path is ok, else you would get a different message. It might be ORACLE_HOME. What is certain is that it is a configuration issue with Oracle client, it has nothing to do with the server. Probably also not oracle_fdw's fault, but you never know.

DaSarfyCode Over a year ago

Ok. But as a last ditch effort, can you please tell me how we can provide env variables to postgres? Is there any way to do it?

Laurenz Albe Over a year ago

I don't have an Ubuntu machine at my fingertips, so I can't tell you where the startup script is. To test, start PostgreSQL from the command line with pg_ctl start.

DaSarfyCode Over a year ago

It worked!! Thanks a lot Laurenz. This solves a huge problem for us. I cannot thank you enough.

Collectives™ on Stack Overflow

PostgreSql: Set environment variables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related