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 https://github.com/laurenz/oracle_fdw 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.



  • Read the https://github.com/laurenz/oracle_fdw#installing-the-extension :

    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_PATH or use /etc/ld.so.conf.

    The PostgreSQL server may have to get restarted to make this effective.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2