对于 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 用户进程的路径。
