The question:
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
.bashrcof thepostgresuser - Adding the export statements to the
.bash_profileof thepostgresuser - Adding the export statements to the global
/etc/profileof 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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
Read the oracle_fdw documentation:
Since the Oracle client shared library is probably not in the standard library path, you have to make sure that the PostgreSQL server will be able to find it. How this is done varies from operating system to operating system; on Linux you can set
LD_LIBRARY_PATHor use/etc/ld.so.conf.
The PostgreSQL server may have to get restarted to make this effective.
Method 2
After starting the PostgreSQL instance manually without the Patroni supervision, then adding the environment variables using su - postgres --whitelist-environment=ORACLE_HOME,LD_LIBRARY_PATH,PATH, I realized that the loading of shared dynamic libraries is done using the paths in /etc/ld.so.conf (also checked using ldd).
Although the path /dbdata/oracle/instantclient_19_12 was added to the /etc/ld.so.conf file and checked using the root user that it is picked up, it seems that the file is either not readable, not used or not useable by the postgres user process.
Currently I am working around the problem by copying the dependencies of the oracle_fdw extension to the /lib64 directory on the system. That allows PostgreSQL to load the extension successfully. I can then do the conversion, then reinit the cluster and rebuild the 2nd cluster node from the first node.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0