Re: error installing oracle_fdw extension

[Sumeet Shukla <sumeet.k.shukla@xxxxxxxxx>]

Thank You All for jumping in for the help. I was able to install it finally. The problem was with the way I was restarting postgresql service.

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.

Warm Regards,

Sumeet Shukla

On Mon, May 22, 2017 at 8:23 PM, Cachique <cachique@xxxxxxxxx> wrote:

Hi.

I had this problem a while ago.

It has to do with the way systemd handles environment variables. Apparently systemd isn't aware of exports of variables. It uses a special section in a special file.

Using this post ( https://serverfault.com/questions/413397/how-to-set-environment-variable-in-systemd-service ) I managed to create the extension.

Steps to follow...

* You need to create a directory inside /etc/systemd/system/

  and name it the same as your service with suffix .d -> postgresql-9.5.service.d

* Put inside a file (name is not important) with extension .conf -> orafwd.conf

* In that file specify a section [Service] with your Oracle environment variables.

  Something like this...
[Service]
Environment=ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
Environment=TNS_ADMIN=/u01/app/oracle/product/11.2.0/xe/network/admin

* Article says you can use systemctl edit yourservice but I didn't tried that. Just created the directory and file by myself.

* Names and paths may vary according to your distribution and likes.

Resuming
You have this file...
/etc/systemd/system/postgresql-9.5.service.d/orafdw.conf

With content...
[Service]
Environment=ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
Environment=TNS_ADMIN=/u01/app/oracle/product/11.2.0/xe/network/admin

Check.
When you run systemctl status postgres you see the config file included

[root@localhost ~]# systemctl status postgresql-9.5.service
● postgresql-9.5.service - PostgreSQL 9.5 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/postgresql-9.5.service.d
           └─orafdw.conf
   Active: active (running) since Wed 2017-05-17 12:11:49 PYT; 4 days ago

Regards,

Walter

On Mon, May 22, 2017 at 9:12 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:

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

--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin