主页 / dba / 问题 / 310152
Accepted
0xCAFEBABE
Asked: 2022-03-25 23:24:57 +0800 CST

PostgreSQL-Patroni-Cluster：安装oracle_fdw，依赖问题

  • 772

对于 Oracle 到 PostgreSQL 的迁移，我们目前正在研究 ora2pg。但是，使用写入文件或直接从 Oracle 读取并写入 PostgreSQL 的性能都非常差劲。关于这方面的信息并不好，虽然我发现的一些部分表明它可能与 Oracle-Perl-Driver 有关。

在新版本的 ora2pg 中，它引入了一种使用 Foreign Data Wrapper 转换数据的新方法oracle_fdw。它是用 C 语言编写的，性能基准似乎表明性能要高得多。为此，我们想使用oracle_fdw. 我们转换的目标是使用 etcd 的具有 2 个节点和第 3 个见证节点的 Patroni 集群设置。

然而，添加oracle_fdw到 Patroni 集群一直很困难，而且到目前为止还没有成功。编译 oracle_fdw 后，尝试加载它会给出以下消息：

错误：无法加载库“/usr/lib/postgresql14/lib64/oracle_fdw.so”：libclntsh.so.19.1：无法打开共享对象文件：没有这样的文件或目录

该库存在于系统上，但位于 Oracle Instantclient ( /dbdata/oracle/instantclient_19_12) 的目录中。为了找到该库，我们认为我们需要将以下标准环境变量添加到postgresPatroni 启动的进程中：

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

然而，我们将这些变量注入到由 Patroni 启动的 postgress 进程中完全没有成功。该过程使用系统用户启动postgres，如下所示：

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

已尝试以下方法：

  • 将导出语句添加到.bashrc用户postgres的
  • 将导出语句添加到.bash_profile用户postgres的
  • 将导出语句添加到/etc/profile服务器的全局
  • 将/dbdata/oracle/instantclient_19_12/路径添加到/etc/ld.so.conf

每次重新启动 Patroni 时，Postgres 也是如此（由不断变化的 PID 检查），但环境总是如下所示：

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):

Patroni 文档有大量关于 Patroni 特定环境变量的信息，但到目前为止我还没有找到任何有助于将变量传递给 Postgres 进程的文档。

复制依赖关系可能会解决问题，但是，添加变量将是解决问题的首选方法。是否有正确或受支持的方式将环境变量添加到由 Patroni 启动的 postgres 进程？

编辑 1：安装过程是使用用户签出存储库完成的root，在设置上述导出后，然后使用make和编译make install。这是当前两个命令的输出：

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

编辑2：我再次尝试安装，使用和make clean过程：makemake install

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):

编辑 3：添加了第 4 种方法来获取进入 postgres 用户进程的路径。

postgresql environment-variables

2 个回答

  1. 阅读oracle_fdw 文档：

    由于 Oracle 客户端共享库可能不在标准库路径中，因此您必须确保 PostgreSQL 服务器能够找到它。如何做到这一点因操作系统而异；在 Linux 上，您可以设置LD_LIBRARY_PATH或使用/etc/ld.so.conf.

    PostgreSQL 服务器可能必须重新启动才能使其生效。

    • 0
  2. Best Answer

    在没有 Patroni 监督的情况下手动启动 PostgreSQL 实例，然后使用 添加环境变量su - postgres --whitelist-environment=ORACLE_HOME,LD_LIBRARY_PATH,PATH后，我意识到共享动态库的加载是使用路径完成的/etc/ld.so.conf（也使用检查ldd）。

    尽管路径/dbdata/oracle/instantclient_19_12已添加到/etc/ld.so.conf文件中并使用root用户检查它是否被拾取，但似乎该文件要么不可读，要么不被postgres用户进程使用或不可使用。

    目前我正在通过将oracle_fdw扩展的依赖项复制到/lib64系统上的目录来解决这个问题。这允许 PostgreSQL 成功加载扩展。然后我可以进行转换，然后重新初始化集群并从第一个节点重建第二个集群节点。

    • 0

相关问题