PostgreSQL-Patroni-Cluster: Installing oracle_fdw, dependency problem

For Oracle to PostgreSQL migrations we're currently looking into ora2pg. However, the performance using either writes to file or direct read from Oracle and write to PostgreSQL have both been very underwhelming. There's not really good information about that, while some parts I found suggested that it could be related to the Oracle-Perl-Driver. In a new version of ora2pg it introduced a new way of converting the data using the Foreign Data Wrapper oracle_fdw. It is written in C and performance benchmarks seem to suggest a much higher performance. For that, we would like to use oracle_fdw. The target for our conversion is a Patroni Cluster setup with 2 nodes and a 3rd witness node using etcd. However, adding oracle_fdw to the Patroni cluster has been difficult and so far unsuccessful. After compiling oracle_fdw, trying to load it gives the following message: > ERROR: could not load library "/usr/lib/postgresql14/lib64/oracle_fdw.so": libclntsh.so.19.1: cannot open shared object file: No such file or directory That library exists on the system, but in the directory of the Oracle Instantclient (/dbdata/oracle/instantclient_19_12). For the library to be found, we think we would need the following standard environment variables added to the postgres process that Patroni starts: export LD_LIBRARY_PATH=/dbdata/oracle/instantclient_19_12 export ORACLE_HOME=/dbdata/oracle/instantclient_19_12 export PATH=/dbdata/oracle/instantclient_19_12:$PATH However, we have been wholly unsuccessful to inject these variables into the postgress process started by Patroni. The process is started using the system user postgres, as seen here: dbserver01 root 6 (/dbdata/oracle/instantclient_19_12): ps -ef|grep postgres root 9236 9120 0 07:56 pts/0 00:00:00 su - postgres postgres 9238 9236 0 07:56 pts/0 00:00:00 -bash postgres 9289 9238 0 07:56 pts/0 00:00:00 psql postgres 9290 27443 0 07:56 ? 00:00:00 postgres: pgcluster2: postgres postgres [local] idle root 9307 9180 0 07:58 pts/1 00:00:00 grep --color=auto postgres postgres 27423 1 0 Mar23 ? 00:01:41 /opt/patroni/bin/python3 /usr/bin/patroni /etc/patroni/postgres.yml postgres 27443 1 0 Mar23 ? 00:00:04 postgres -D /dbdata/pgcluster --config-file=/dbdata/pgcluster/postgresql.conf --listen_addresses=0.0.0.0 --port=5432 --cluster_name=pgcluster2 --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on The following methods have been tried: * Adding the export statements to the .bashrc of the postgres user * Adding the export statements to the .bash_profile of the postgres user * Adding the export statements to the global /etc/profile of the server * Adding the /dbdata/oracle/instantclient_19_12/ path to the /etc/ld.so.conf Each time Patroni was restarted, and so was Postgres (checked by the ever changing PID), yet the environment always looks like this: dbserver01 root 7 (/dbdata/oracle/instantclient_19_12): cat /proc/27443/environ | xargs -0 -n 1 LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin HOME=/var/lib/pgsql LOGNAME=postgres USER=postgres SHELL=/bin/bash INVOCATION_ID=3806a21dfa52455baad431cc2bbfa533 JOURNAL_STREAM=9:5426402 dbserver01 root 8 (/dbdata/oracle/instantclient_19_12): The Patroni documentation has loads of information about Patroni specific environment variables, yet I have so far not found any documentation that helps pass variables to the Postgres process. Copying the dependencies around might work around the problem, however, adding the variables would be the preferred way of fixing the problem. Is there a right or supported way to add environment variables to the postgres process started by Patroni? Edit 1: The installation process was done using a checkout of the repository with the root user, after setting the above mentioned exports, then compiled using make and make install. Here's the output of both commands currently: dbserver01 root 25 (/tmp/oracle_fdw): make make: Nothing to be done for 'all'. goeccdb11l root 26 (/tmp/oracle_fdw): make install /usr/bin/mkdir -p '/usr/lib/postgresql14/lib64' /usr/bin/mkdir -p '/usr/share/postgresql14/extension' /usr/bin/mkdir -p '/usr/share/postgresql14/extension' /usr/bin/mkdir -p '/usr/share/doc/packages/postgresql14/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/lib/postgresql14/lib64/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql14/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/usr/share/postgresql14/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/packages/postgresql14/extension/' dbserver01 root 27 (/tmp/oracle_fdw): ls -la /usr/lib/postgresql14/lib64 Edit 2: I've tried the installation again, using make clean and the process of make and make install again: dbserver01 root 11 (/tmp/oracle_fdw): make clean rm -f oracle_fdw.so liboracle_fdw.a liboracle_fdw.pc rm -f oracle_fdw.o oracle_utils.o oracle_gis.o oracle_fdw.bc oracle_utils.bc oracle_gis.bc rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/ dbserver01 root 12 (/tmp/oracle_fdw): export LD_LIBRARY_PATH=/dbdata/oracle/instantclient_19_12 dbserver01 root 13 (/tmp/oracle_fdw): export ORACLE_HOME=/dbdata/oracle/instantclient_19_12 dbserver01 root 14 (/tmp/oracle_fdw): export PATH=/dbdata/oracle/instantclient_19_12:$PATH dbserver01 root 15 (/tmp/oracle_fdw): make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -fmessage-length=0 -grecord-gcc-switches -O2 -Wall -D_FORTIFY_SOURCE=2 -fstack-protector-strong -funwind-tables -fasynchronous-unwind-tables -fstack-clash-protection -g -fPIC -I"/dbdata/oracle/instantclient_19_12/sdk/include" -I"/dbdata/oracle/instantclient_19_12/oci/include" -I"/dbdata/oracle/instantclient_19_12/rdbms/public" -I"/dbdata/oracle/instantclient_19_12/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_fdw.o oracle_fdw.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -fmessage-length=0 -grecord-gcc-switches -O2 -Wall -D_FORTIFY_SOURCE=2 -fstack-protector-strong -funwind-tables -fasynchronous-unwind-tables -fstack-clash-protection -g -fPIC -I"/dbdata/oracle/instantclient_19_12/sdk/include" -I"/dbdata/oracle/instantclient_19_12/oci/include" -I"/dbdata/oracle/instantclient_19_12/rdbms/public" -I"/dbdata/oracle/instantclient_19_12/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_utils.o oracle_utils.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -fmessage-length=0 -grecord-gcc-switches -O2 -Wall -D_FORTIFY_SOURCE=2 -fstack-protector-strong -funwind-tables -fasynchronous-unwind-tables -fstack-clash-protection -g -fPIC -I"/dbdata/oracle/instantclient_19_12/sdk/include" -I"/dbdata/oracle/instantclient_19_12/oci/include" -I"/dbdata/oracle/instantclient_19_12/rdbms/public" -I"/dbdata/oracle/instantclient_19_12/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_gis.o oracle_gis.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -fmessage-length=0 -grecord-gcc-switches -O2 -Wall -D_FORTIFY_SOURCE=2 -fstack-protector-strong -funwind-tables -fasynchronous-unwind-tables -fstack-clash-protection -g -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/lib/postgresql14/lib64 -L/usr/lib64 -Wl,--as-needed -L"/dbdata/oracle/instantclient_19_12/" -L"/dbdata/oracle/instantclient_19_12/bin" -L"/dbdata/oracle/instantclient_19_12/lib" -L"/dbdata/oracle/instantclient_19_12/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.14/client64/lib -L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib -L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib -L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib -L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib -L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib -L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib dbserver01 root 16 (/tmp/oracle_fdw): make install /usr/bin/mkdir -p '/usr/lib/postgresql14/lib64' /usr/bin/mkdir -p '/usr/share/postgresql14/extension' /usr/bin/mkdir -p '/usr/share/postgresql14/extension' /usr/bin/mkdir -p '/usr/share/doc/packages/postgresql14/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/lib/postgresql14/lib64/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql14/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/usr/share/postgresql14/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/packages/postgresql14/extension/' dbserver01 root 17 (/tmp/oracle_fdw): Edit 3: Added a 4th method to get the path into the postgres user process.