对于DBA来说，pg和oracle之间，必备技能之一就是学会用插件oracle_fdw，此文章可供入门参考。
环境介绍：
pg端：redhat7.4+pg12.4+oracle_fdw2.4通过repmgr管理的主备两节点
oracle端：oracle11.2.0.4，oradb,orauser/abcd1234
项目需求：目前主库pg和oracle之间通过每天同步一次的方法更新，数据不实时，且每需一个数据就要开发一次程序。很麻烦，效率低。
解决方案：PG库直接通过dblink访问oracle数据库，根据需要查询实时信息
安装步骤：(以下只需要在pg端做)
1.下载oracle12.2.0.1客户端（建议11g以上版本)
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
instantclient-basic-linux.x64-12.2.0.1.0.zip --必须
instantclient-sdk-linux.x64-12.2.0.1.0.zip --必须
instantclient-sqlplus-linux.x64-12.2.0.1.0.zip --可选，方便直接连oracle查看库表信息，连接命令参考：sqlplus orauser/password@//192.168.208.40:1521/oradb
2.下载oracle_fdw最新源码，以及官方的安装参考
https://github.com/laurenz/oracle_fdw
3.介质上传到pg端,比如/opt/oracle下，解压改名，添加环境变量/etc/profile,编译oracle_fdw
vi /etc/profile
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$GCC_HOME/bin:$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PROTOBUFC_HOME/bin:$ORACLE_HOME:$PATH
4.登录pg库，创建扩展插件oracle_fdw–此操作只需在主库操作即可，备库自动同步。
create extension oracle_fdw;
5.创建连接oracle的外部表相关–此操作只需在主库操作即可，备库自动同步。
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.207.39:1521/oradb');
GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'orauser', password 'abcd1234');
两种创建方式：第2种更方便，无需列出字段及类型。
1） CREATE FOREIGN TABLE TEST1(
LOGDATE DATE ,
FLDID VARCHAR,
xxx... ) SERVER oradb OPTIONS (SCHEMA 'ORAUSER', TABLE 'TEST1');--注意大写字母SCHEMA 'ORAUSER', TABLE 'TEST1'
2）IMPORT FOREIGN SCHEMA "ORAUSER" LIMIT TO (TEST1,TEST2) FROM SERVER oradb INTO public;--SCHEMA需要注意大写和双引号，否则失败，除了可导入表还可以是视图，很方便。如需要，可考虑增加option参数：
OPTIONS (case 'lower', readonly 'true',max_long '32767',sample_percent '100', prefetch '0');
如下：
testdb=# IMPORT FOREIGN SCHEMA ORAUSER LIMIT TO (TEST1) FROM SERVER oradb INTO public;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
testdb=# IMPORT FOREIGN SCHEMA "orauser" LIMIT TO (TEST1) FROM SERVER oradb INTO public;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
testdb=# IMPORT FOREIGN SCHEMA orauser LIMIT TO (TEST1) FROM SERVER oradb INTO public;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
检查配置
\det+
\des+
\deu+
\dew+
关于IMPORT FOREIGN SCHEMA问题，在问答中有参考：https://www.modb.pro/issue/13960
以下为实操记录（已替换敏感信息，遇到的问题及处理都按如下操作记录展示）
[user@pgdb ~]$ sudo -i -u root
[sudo] password for user:
Last login: Mon Mar 7 15:59:49 CST 2022 on pts/3
[root@pgdb ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-root 20G 9.9G 11G 50% /
devtmpfs 48G 0 48G 0% /dev
tmpfs 48G 616K 48G 1% /dev/shm
tmpfs 48G 4.1G 44G 9% /run
tmpfs 48G 0 48G 0% /sys/fs/cgroup
/dev/sda1 1011M 179M 833M 18% /boot
/dev/mapper/sftp-lv_sftp 1.1T 784G 316G 72% /sftp
/dev/mapper/VolGroup-home 10G 2.6G 7.5G 26% /home
/dev/mapper/VolGroup-tmp 10G 33M 10G 1% /tmp
/dev/mapper/VolGroup-opt 40G 379M 40G 1% /opt
/dev/mapper/VolGroup-app 609G 465G 145G 77% /app
/dev/mapper/VolGroup-var 10G 3.3G 6.8G 33% /var
tmpfs 9.5G 0 9.5G 0% /run/user/2001
tmpfs 9.5G 0 9.5G 0% /run/user/1000
tmpfs 9.5G 0 9.5G 0% /run/user/1011
tmpfs 9.5G 0 9.5G 0% /run/user/1016
tmpfs 9.5G 0 9.5G 0% /run/user/1015
tmpfs 9.5G 0 9.5G 0% /run/user/1014
tmpfs 9.5G 0 9.5G 0% /run/user/2002
tmpfs 9.5G 0 9.5G 0% /run/user/1010
[root@pgdb ~]# cd /opt
[root@pgdb opt]# mkdir oracle
[root@pgdb opt]# chmod -R 777 /opt/oracle
[root@pgdb opt]# cd oracle/
[root@pgdb oracle]# ll
total 69044
-rw-r--r-- 1 user user 68965195 Mar 9 16:35 instantclient-basic-linux.x64-12.2.0.1.0.zip
-rw-r--r-- 1 user user 674743 Mar 9 16:35 instantclient-sdk-linux.x64-12.2.0.1.0.zip
-rw-r--r-- 1 user user 904309 Mar 9 16:35 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
-rw-r--r-- 1 user user 150692 Mar 9 16:40 oracle_fdw-2.4.0.zip
[root@pgdb oracle]# mv oracle_fdw-2.4.0.zip ../
[root@pgdb oracle]# unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
Archive: instantclient-basic-linux.x64-12.2.0.1.0.zip
inflating: instantclient_12_2/adrci
inflating: instantclient_12_2/BASIC_README
inflating: instantclient_12_2/genezi
inflating: instantclient_12_2/libclntshcore.so.12.1
inflating: instantclient_12_2/libclntsh.so.12.1
inflating: instantclient_12_2/libipc1.so
inflating: instantclient_12_2/libmql1.so
inflating: instantclient_12_2/libnnz12.so
inflating: instantclient_12_2/libocci.so.12.1
inflating: instantclient_12_2/libociei.so
inflating: instantclient_12_2/libocijdbc12.so
inflating: instantclient_12_2/libons.so
inflating: instantclient_12_2/liboramysql12.so
inflating: instantclient_12_2/ojdbc8.jar
inflating: instantclient_12_2/uidrvci
inflating: instantclient_12_2/xstreams.jar
[root@pgdb oracle]# unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
Archive: instantclient-sdk-linux.x64-12.2.0.1.0.zip
creating: instantclient_12_2/sdk/
inflating: instantclient_12_2/sdk/ott
extracting: instantclient_12_2/sdk/ottclasses.zip
inflating: instantclient_12_2/sdk/SDK_README
creating: instantclient_12_2/sdk/demo/
inflating: instantclient_12_2/sdk/demo/setuporamysql.sh
inflating: instantclient_12_2/sdk/demo/occiobj.typ
inflating: instantclient_12_2/sdk/demo/occidml.cpp
inflating: instantclient_12_2/sdk/demo/occidemo.sql
inflating: instantclient_12_2/sdk/demo/occiobj.cpp
inflating: instantclient_12_2/sdk/demo/occidemod.sql
inflating: instantclient_12_2/sdk/demo/oraaccess.xml
inflating: instantclient_12_2/sdk/demo/demo.mk
inflating: instantclient_12_2/sdk/demo/cdemo81.c
creating: instantclient_12_2/sdk/include/
inflating: instantclient_12_2/sdk/include/occiControl.h
inflating: instantclient_12_2/sdk/include/oro.h
inflating: instantclient_12_2/sdk/include/ociapr.h
inflating: instantclient_12_2/sdk/include/occiCommon.h
inflating: instantclient_12_2/sdk/include/occiData.h
inflating: instantclient_12_2/sdk/include/oci8dp.h
inflating: instantclient_12_2/sdk/include/ociextp.h
inflating: instantclient_12_2/sdk/include/orl.h
inflating: instantclient_12_2/sdk/include/nzt.h
inflating: instantclient_12_2/sdk/include/ldap.h
inflating: instantclient_12_2/sdk/include/occi.h
inflating: instantclient_12_2/sdk/include/ociap.h
inflating: instantclient_12_2/sdk/include/odci.h
inflating: instantclient_12_2/sdk/include/ocixstream.h
inflating: instantclient_12_2/sdk/include/nzerror.h
inflating: instantclient_12_2/sdk/include/oci1.h
inflating: instantclient_12_2/sdk/include/ori.h
inflating: instantclient_12_2/sdk/include/ocixmldb.h
inflating: instantclient_12_2/sdk/include/ocidem.h
inflating: instantclient_12_2/sdk/include/occiAQ.h
inflating: instantclient_12_2/sdk/include/ocidef.h
inflating: instantclient_12_2/sdk/include/occiObjects.h
inflating: instantclient_12_2/sdk/include/oci.h
inflating: instantclient_12_2/sdk/include/oratypes.h
inflating: instantclient_12_2/sdk/include/orid.h
inflating: instantclient_12_2/sdk/include/xa.h
inflating: instantclient_12_2/sdk/include/ocikpr.h
inflating: instantclient_12_2/sdk/include/ocidfn.h
inflating: instantclient_12_2/sdk/include/ort.h
creating: instantclient_12_2/sdk/admin/
inflating: instantclient_12_2/sdk/admin/oraaccess.xsd
[root@pgdb oracle]# unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
Archive: instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
inflating: instantclient_12_2/glogin.sql
inflating: instantclient_12_2/libsqlplusic.so
inflating: instantclient_12_2/libsqlplus.so
inflating: instantclient_12_2/sqlplus
inflating: instantclient_12_2/SQLPLUS_README
[root@pgdb oracle]# ll
total 68900
drwxr-xr-x 3 root root 4096 Mar 10 09:35 instantclient_12_2
-rw-r--r-- 1 user user 68965195 Mar 9 16:35 instantclient-basic-linux.x64-12.2.0.1.0.zip
-rw-r--r-- 1 user user 674743 Mar 9 16:35 instantclient-sdk-linux.x64-12.2.0.1.0.zip
-rw-r--r-- 1 user user 904309 Mar 9 16:35 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
[root@pgdb oracle]# mv instantclient_12_2/ instantclient
[root@pgdb oracle]# vi /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.
pathmunge () {
case ":${PATH}:" in
*:"$1":*)
;;
*)
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
esac
}
if [ -x /usr/bin/id ]; then
if [ -z "$EUID" ]; then
# ksh workaround
EUID=`/usr/bin/id -u`
UID=`/usr/bin/id -ru`
fi
USER="`/usr/bin/id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
fi
# Path manipulation
if [ "$EUID" = "0" ]; then
pathmunge /usr/sbin
pathmunge /usr/local/sbin
else
pathmunge /usr/local/sbin after
pathmunge /usr/sbin after
fi
HOSTNAME=`/usr/bin/hostname 2>/dev/null`
HISTSIZE=1000
if [ "$HISTCONTROL" = "ignorespace" ] ; then
export HISTCONTROL=ignoreboth
else
export HISTCONTROL=ignoredups
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`/usr/bin/id -gn`" = "`/usr/bin/id -un`" ]; then
umask 002
else
umask 022
fi
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
if [ "${-#*i}" != "$-" ]; then
. "$i"
else
. "$i" >/dev/null
fi
fi
done
unset i
unset -f pathmunge
export TZ=Asia/Shanghai
export TMOUT=0
PATH=$PATH:/app/pgsql/bin
export PROTOBUF_HOME=/usr/local/protobuf-3.10.1
export PATH=$GCC_HOME/bin:$PROTOBUF_HOME/bin:$PATH
export PROTOBUF_HOME=/usr/local/protobuf-3.10.1
export PROTOBUFC_HOME=/usr/local/protobuf-c-1.3.2
export PATH=$PROTOBUFC_HOME/bin:$GCC_HOME/bin:$PROTOBUF_HOME/bin:$PROTOBUFC_HOME/bin:$PATH
export CMAKE_HOME=/usr/local/cmake-3.21.1
export PATH=$GCC_HOME/bin:$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PROTOBUFC_HOME/bin:$PATH
export PKG_CONFIG_PATH=/usr/local/protobuf-c-1.3.2/lib/pkgconfi:/usr/local/protobuf-3.10.1/lib/pkgconfig:/usr/local/sqlite/lib/pkgconfig:$PKG_CONFIG_PATH
export PGDATA=/app/pgsql/data
export PGHOME=/app/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$GCC_HOME/bin:$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PROTOBUFC_HOME/bin:$ORACLE_HOME:$PATH
"/etc/profile" 96L, 2850C written
[root@pgdb oracle]# source /etc/profile
[root@pgdb oracle]# echo $ORACLE_HOME
/opt/oracle/instantclient
[root@pgdb oracle]# pwd
/opt/oracle
[root@pgdb oracle]# cd /opt
[root@pgdb opt]# ll
total 148
drwxrwxrwx 5 root root 125 Sep 8 2021 dcits
drwxrwxrwx 3 root root 183 Mar 10 09:35 oracle
-rw-r--r-- 1 user user 150692 Mar 9 16:40 oracle_fdw-2.4.0.zip
drwxr-xr-x. 2 root root 6 Mar 26 2015 rh
[root@pgdb opt]# unzip oracle_fdw-2.4.0.zip
Archive: oracle_fdw-2.4.0.zip
creating: oracle_fdw-2.4.0/
inflating: oracle_fdw-2.4.0/TODO
creating: oracle_fdw-2.4.0/expected/
inflating: oracle_fdw-2.4.0/expected/oracle_join.out
inflating: oracle_fdw-2.4.0/expected/oracle_fdw.out
inflating: oracle_fdw-2.4.0/expected/oracle_gis.out
inflating: oracle_fdw-2.4.0/expected/oracle_import.out
creating: oracle_fdw-2.4.0/msvc/
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.sln
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.vcxproj
inflating: oracle_fdw-2.4.0/msvc/oracle_msvc.c
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.props
inflating: oracle_fdw-2.4.0/Makefile
inflating: oracle_fdw-2.4.0/CHANGELOG
inflating: oracle_fdw-2.4.0/oracle_fdw.h
inflating: oracle_fdw-2.4.0/oracle_fdw--1.0--1.1.sql
inflating: oracle_fdw-2.4.0/oracle_fdw--1.1--1.2.sql
inflating: oracle_fdw-2.4.0/oracle_fdw.c
inflating: oracle_fdw-2.4.0/oracle_fdw.control
inflating: oracle_fdw-2.4.0/oracle_fdw--1.2.sql
inflating: oracle_fdw-2.4.0/oracle_gis.c
inflating: oracle_fdw-2.4.0/META.json
creating: oracle_fdw-2.4.0/sql/
inflating: oracle_fdw-2.4.0/sql/oracle_join.sql
inflating: oracle_fdw-2.4.0/sql/oracle_gis.sql
inflating: oracle_fdw-2.4.0/sql/oracle_fdw.sql
inflating: oracle_fdw-2.4.0/sql/oracle_import.sql
inflating: oracle_fdw-2.4.0/oracle_utils.c
inflating: oracle_fdw-2.4.0/LICENSE
inflating: oracle_fdw-2.4.0/README.md
inflating: oracle_fdw-2.4.0/README.oracle_fdw
[root@pgdb opt]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
[root@pgdb opt]# cd oracle_fdw-2.4.0/
[root@pgdb oracle_fdw-2.4.0]# make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient/sdk/include" -I"/opt/oracle/instantclient/oci/include" -I"/opt/oracle/instantclient/rdbms/public" -I"/opt/oracle/instantclient/" -I/usr/include/oracle/21/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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/app/pgsql/include/server -I/app/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_fdw.o oracle_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient/sdk/include" -I"/opt/oracle/instantclient/oci/include" -I"/opt/oracle/instantclient/rdbms/public" -I"/opt/oracle/instantclient/" -I/usr/include/oracle/21/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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/app/pgsql/include/server -I/app/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_utils.o oracle_utils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient/sdk/include" -I"/opt/oracle/instantclient/oci/include" -I"/opt/oracle/instantclient/rdbms/public" -I"/opt/oracle/instantclient/" -I/usr/include/oracle/21/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/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/app/pgsql/include/server -I/app/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_gis.o oracle_gis.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/app/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/app/pgsql/lib',--enable-new-dtags -L"/opt/oracle/instantclient/" -L"/opt/oracle/instantclient/bin" -L"/opt/oracle/instantclient/lib" -L"/opt/oracle/instantclient/lib/amd64" -lclntsh -L/usr/lib/oracle/21/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 -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib
/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
make: *** [oracle_fdw.so] Error 1
[root@pgdb oracle_fdw-2.4.0]# cd /opt/oracle/instantclient/
[root@pgdb instantclient]# ln -sv libclntsh.so.12.1 libclntsh.so
‘libclntsh.so’ -> ‘libclntsh.so.12.1’
[root@pgdb instantclient]# ll libclntsh.so
lrwxrwxrwx 1 root root 17 Mar 10 09:48 libclntsh.so -> libclntsh.so.12.1
[root@pgdb instantclient]# cd /opt/oracle_fdw-2.4.0
[root@pgdb oracle_fdw-2.4.0]# make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/app/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/app/pgsql/lib',--enable-new-dtags -L"/opt/oracle/instantclient/" -L"/opt/oracle/instantclient/bin" -L"/opt/oracle/instantclient/lib" -L"/opt/oracle/instantclient/lib/amd64" -lclntsh -L/usr/lib/oracle/21/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 -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib
[root@pgdb oracle_fdw-2.4.0]# make install
/bin/mkdir -p '/app/pgsql/lib'
/bin/mkdir -p '/app/pgsql/share/extension'
/bin/mkdir -p '/app/pgsql/share/extension'
/bin/mkdir -p '/app/pgsql/share/doc/extension'
/bin/install -c -m 755 oracle_fdw.so '/app/pgsql/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/app/pgsql/share/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/app/pgsql/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/app/pgsql/share/doc/extension/'
[root@pgdb oracle_fdw-2.4.0]#
[root@pgdb oracle_fdw-2.4.0]# ll oracle_fdw.so
-rwxr-xr-x 1 root root 165504 Mar 10 09:48 oracle_fdw.so
[root@pgdb oracle_fdw-2.4.0]#
[root@pgdb oracle_fdw-2.4.0]# su - postgres
Last login: Thu Mar 10 09:45:01 CST 2022
[postgres@pgdb ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+------------------------------------------------------------------
1 | pgdb | primary | * running | | default | 100 | 11 | host=192.168.208.7 user=postgres dbname=postgres connect_timeout=2
2 | pg08 | standby | running | pgdb | default | 100 | 11 | host=192.168.208.8 user=postgres dbname=postgres connect_timeout=2
[postgres@pgdb ~]$ psql -h pgdb
Password for user postgres:
psql (12.4)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | pubuser=CTc/postgres +
| | | | | jzshuser=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
wyzcdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
| | | | | wyzc=CTc/postgres
(5 rows)
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+------------------------------------------------------------
pgrouting | 2.6.3 | public | pgRouting Extension
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.1.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.1.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.1.3 | public | PostGIS SFCGAL functions
(5 rows)
testdb=# create extension oracle_fdw;
ERROR: could not load library "/app/pgsql/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory
testdb=# \q
[postgres@pgdb ~]$ exit
logout
处理办法：
----------------------------------------------------------------------------------上述
[root@pgdb oracle_fdw-2.4.0]# echo $ORACLE_HOME > /etc/ld.so.conf.d/oracle.conf
[root@pgdb oracle_fdw-2.4.0]# cat /etc/ld.so.conf.d/oracle.conf
/opt/oracle/instantclient
[root@pgdb oracle_fdw-2.4.0]# ldconfig
----------------------------------------------------------------------------------
[root@pgdb oracle_fdw-2.4.0]# su - postgres
Last login: Thu Mar 10 09:50:01 CST 2022
[postgres@pgdb ~]$ psql -h pgdb
Password for user postgres:
psql (12.4)
Type "help" for help.
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+------------------------------------------------------------
pgrouting | 2.6.3 | public | pgRouting Extension
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.1.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.1.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.1.3 | public | PostGIS SFCGAL functions
(5 rows)
testdb=# create extension oracle_fdw;
CREATE EXTENSION
testdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+------------------------------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
pgrouting | 2.6.3 | public | pgRouting Extension
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.1.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.1.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.1.3 | public | PostGIS SFCGAL functions
(6 rows)
testdb=#
pg配置连接oracle数据库的操作：
testdb=> \c testdb postgres
Password for user postgres:
You are now connected to database "testdb" as user "postgres".
testdb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+-------------------------------------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
pg_trgm | 1.4 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
testdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.208.40:1521/oradb');
CREATE SERVER
testdb=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
------------+----------+--------------------+----------------------
oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)
testdb=# GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
GRANT
testdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
GRANT
testdb=# \deu
List of user mappings
Server | User name
--------+-----------
(0 rows)
testdb=# \def
invalid command \def
Try \? for help.
testdb=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
------------+----------+----------------------
oradb | postgres | oracle_fdw
(1 row)
testdb=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------------+----------+----------------------+---------------------+------+---------+--------------------------------------------+-------------
oradb | postgres | oracle_fdw | postgres=U/postgres+| | | (dbserver '//192.168.208.40:1521/oradb') |
| | | pguser=U/postgres | | | |
(1 row)
testdb=# \det
List of foreign tables
Schema | Table | Server
----------+--------------------------+------------
pguser | TEST1 | oradb
(1 row)
testdb=# \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
----------+--------------------------+------------+-----------------------------------------------------+-------------
pguser | TEST1 | oradb | (schema 'orauser', "table" 'TEST1') |
(1 row)
testdb=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------------+----------+----------------------+---------------------+------+---------+--------------------------------------------+-------------
oradb | postgres | oracle_fdw | postgres=U/postgres+| | | (dbserver '//192.168.208.40:1521/oradb') |
| | | pguser=U/postgres | | | |
(1 row)
testdb=# \deu+
List of user mappings
Server | User name | FDW options
------------+-----------+-------------------------------------------
oradb | pguser | ("user" 'orauser', password 'abcd1234')
oradb | postgres | ("user" 'orauser', password 'abcd1234')
(2 rows)
testdb=# \dew+
List of foreign-data wrappers
Name | Owner | Handler | Validator | Access privileges | FDW options | Description
------------+----------+---------------------------+-----------------------------+-------------------+-------------+-----------------------------
oracle_fdw | postgres | public.oracle_fdw_handler | public.oracle_fdw_validator | | | Oracle foreign data wrapper
(1 row)
testdb=# set search_path=pguser;
SET
testdb=# show search_path;
search_path
-------------
pguser
(1 row)
testdb=# \dt
List of relations
Schema | Name | Type | Owner
----------+----------------------------------+-------+----------
pguser | j | table | pguser
(36 rows)
testdb=# \c
You are now connected to database "testdb" as user "postgres".
testdb=# IMPORT FOREIGN SCHEMA "orauser" LIMIT TO (TEST1) FROM SERVER oradb INTO pguser;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
[postgres@ydw02 ~]$ psql -h 192.168.208.7 -U postgres testdb
Password for user postgres:
psql (12.4)
Type "help" for help.
testdb=# show search_path;
search_path
-----------------
"$user", public
(1 row)
testdb=# set search_path=pguser;
SET
testdb=# \dt
List of relations
Schema | Name | Type | Owner
----------+----------------------------------+-------+----------
pguser | j | table | pguser
testdb=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
------------+----------+----------------------
oradb | postgres | oracle_fdw
(1 row)
testdb=#CREATE FOREIGN TABLE TEST1(
LOGDATE DATE ,
FLDID VARCHAR,
FLDTAG VARCHAR,
ENBCODE VARCHAR,
ENBNAME VARCHAR,
STATION_ID VARCHAR,
VENDORNAME VARCHAR,
METYPE VARCHAR,
IPADDR VARCHAR,
NETWORKMASK VARCHAR,
GATEWAYIP VARCHAR,
EGRESSMBR VARCHAR,
REMOTEADDR1 VARCHAR,
REMOTEADDR2 VARCHAR,
SWVERSION VARCHAR,
RADIOMODE VARCHAR,
OPERATIONALSTATE VARCHAR,
STATE VARCHAR,
SOURCE_IDENTIFICATION VARCHAR,
SOURCE_TYPE VARCHAR,
SOURCE_VENDER VARCHAR,
SOURCE_NAME VARCHAR,
DISTRICT VARCHAR,
MAINTENANCE_AREA VARCHAR,
MAINTENANCE VARCHAR,
TENDERS VARCHAR,
AREA VARCHAR,
COVER_TYPE VARCHAR,
BTSID VARCHAR,
BSC VARCHAR,
RADIUS VARCHAR,
LONGITUDE numeric,
LATITUDE numeric,
CPU_LOAD VARCHAR,
CPU_LOAD_PJ VARCHAR,
BTSTYPENAME VARCHAR,
OBJECTID VARCHAR,
CHECK_STATE VARCHAR,
SHAPE_PARAM VARCHAR,
ISADD VARCHAR,
ISPROSTATE VARCHAR,
L800 VARCHAR,
USERLABEL VARCHAR,
SALES_UNIT VARCHAR,
CREATETIME DATE ,
ISDELETE VARCHAR,
SITE VARCHAR,
INTERSECTION VARCHAR,
INTERSECTION_TIME VARCHAR,
SITE_NAME VARCHAR,
USERLABEL_XY VARCHAR,
USER_TYPES VARCHAR,
SOURCE_NUMBER numeric,
COMMUNITY_TYPE VARCHAR,
COMMUNITY_RRU numeric,
STATION_NAME VARCHAR,
UPDATEMAIN_DATE DATE ,
IMPTYPE VARCHAR,
GRID_LAYOUT VARCHAR,
IS_TY_CHANGE VARCHAR,
OLD_STATE VARCHAR,
BREAKDAYS numeric,
BREAKTIME DATE ,
BUILDER VARCHAR,
BREAKREASON VARCHAR,
SJ_LONGITUDE numeric,
SJ_LATITUDE numeric,
WG_LONGITUDE numeric,
WG_LATITUDE numeric,
COOR_TYPE VARCHAR,
CURRMONTH_BREAKDAYS numeric,
PROGRAMENUM VARCHAR,
DESIGNER VARCHAR,
SUPERVISOR VARCHAR,
DCIP VARCHAR,
DCOMC VARCHAR,
BSNUM VARCHAR
) SERVER oradb OPTIONS (SCHEMA 'ORAUSER', TABLE 'TEST1');
testdb=# select * from TEST1 limit 2 offset 0;
logdate | fldid | fldtag | enbcode | enbname | station_id | vendorname | metype | ipaddr | networkmask | gatewayip | egressmbr | remoteaddr1
| remoteaddr2 | swversion | radiomode | operationalstate | state | source_identification | source_type | source_vender | source_name | district | maintenance_a
rea | maintenance | tenders | area | cover_type | btsid | bsc | radius | longitude | latitude | cpu_load | cpu_load_pj | btstypename | objectid | check_state | shape_p
aram | isadd | isprostate | l800 | userlabel | sales_unit | createtime | isdelete | site | intersection | intersection_time | site_name |
userlabel_xy | user_types | source_number | community_type | community_rru | station_name | updatemain_date | imptype | grid_layout | is_ty_change | old_state | breakdays | break
time | builder | breakreason | sj_longitude | sj_latitude | wg_longitude | wg_latitude | coor_type | currmonth_breakdays | programenum | designer | supervisor | dcip | dcomc | bs
num
------------+-------------------+---------+---------+------------------+------------+------------+--------+-------------+-----------------+-------------+-----------+-------------
+-------------+------------------------------+-----------+------------------+-------+-----------------------+-------------+---------------+-------------+----------+--------------
----+-------------+---------+--------+------------+--------+-----+--------+-----------+----------+----------+-------------+-------------+----------+-------------+----------------
-------------+-------+------------+------+-----------+--------------+------------+----------+--------------------+--------------+---------------------+-------------------------+-
-------------+------------+---------------+----------------+---------------+--------------+-----------------+---------+-------------+--------------+-----------+-----------+------
-----+---------+-------------+--------------+-------------+--------------+-------------+-----------+---------------------+-------------+----------+------------+------+-------+---
----
2020-05-07 | 99999999182369779 | orauser002 | 113847 | ZHT_DX大兴 | 20248811 | ZTE | BS8700 | 6.1.10.206 | 255.255.255.252 | 6.1.103.205 | 1000 | 6...
(2 rows)
testdb=# \det TEST1
List of foreign tables
Schema | Table | Server
----------+--------------------------+------------
pguser | TEST1 | oradb
(1 row)
testdb=# \dt
List of relations
Schema | Name | Type | Owner
----------+----------------------------------+-------+----------
pguser | j | table | pguser
(36 rows)
testdb=# select * from TEST1 limit 1 offset 0;
logdate | fldid | fldtag | enbcode | enbname | station_id | vendorname | metype | ipaddr | networkmask | gatewayip | egressmbr | remoteaddr1
| remoteaddr2 | swversion | radiomode | operationalstate | state | source_identification | source_type | source_vender | source_name | district | maintenance_a
rea | maintenance | tenders | area | cover_type | btsid | bsc | radius | longitude | latitude | cpu_load | cpu_load_pj | btstypename | objectid | check_state | shape_p
aram | isadd | isprostate | l800 | userlabel | sales_unit | createtime | isdelete | site | intersection | intersection_time | site_name |
userlabel_xy | user_types | source_number | community_type | community_rru | station_name | updatemain_date | imptype | grid_layout | is_ty_change | old_state | breakdays | break
time | builder | breakreason | sj_longitude | sj_latitude | wg_longitude | wg_latitude | coor_type | currmonth_breakdays | programenum | designer | supervisor | dcip | dcomc | bs
num
------------+-------------------+---------+---------+------------------+------------+------------+--------+-------------+-----------------+-------------+-----------+-------------
+-------------+------------------------------+-----------+------------------+-------+-----------------------+-------------+---------------+-------------+----------+--------------
----+-------------+---------+--------+------------+--------+-----+--------+-----------+----------+----------+-------------+-------------+----------+-------------+----------------
-------------+-------+------------+------+-----------+--------------+------------+----------+--------------------+--------------+---------------------+-------------------------+-
-------------+------------+---------------+----------------+---------------+--------------+-----------------+---------+-------------+--------------+-----------+-----------+------
-----+---------+-------------+--------------+-------------+--------------+-------------+-----------+---------------------+-------------+----------+------------+------+-------+---
----
2020-05-07 | 99999999182369779 | orauser002 | 113847 | ZHT_DX大兴 | 20248811 | ZTE | BS8700 | 6.1.10.206 | 255.255.255.252 | 6.1.103.205 | 1000 | 6
(1 row)
testdb=# show search_path;
search_path
-------------
pguser
(1 row)
testdb=#
testdb=# IMPORT FOREIGN SCHEMA "orauser"
testdb-# LIMIT TO (TEST1)
testdb-# FROM SERVER oradb
testdb-# INTO public
testdb-# OPTIONS (case 'lower', readonly 'true',max_long '32767',sample_percent '1', prefetch '0');
IMPORT FOREIGN SCHEMA
testdb=# \det
List of foreign tables
Schema | Table | Server
--------+--------------------------+------------
public | TEST1 | oradb
(1 row)
testdb=# select * from TEST1 limit 1 offset 0;
logdate | fldid | fldtag | enbcode | enbname | station_id | vendorname | metype | ipaddr | networkmask | gatewayip | egressmbr | remoteaddr
1 | remoteaddr2 | swversion | radiomode | operationalstate | state | source_identification | source_type | source_vender | source_name | district | maintenance_area |
maintenance | tenders | area | cover_type | btsid | bsc | radius | longitude | latitude | cpu_load | cpu_load_pj | btstypename | objectid | check_state | shape_param
| isadd | isprostate | l800 | userlabel | sales_unit | createtime | isdelete | site | intersection | intersection_time | site_name | user
label_xy | user_types | source_number | community_type | community_rru | station_name | updatemain_date | imptype | grid_layout | is_ty_change | old_state | breakdays | breaktime |
builder | breakreason | sj_longitude | sj_latitude | wg_longitude | wg_latitude | coor_type | currmonth_breakdays | programenum | designer | supervisor | dcip | dcomc | bsnum
---------------------+-------------------+---------+---------+------------------+------------+------------+--------+-------------+-----------------+-------------+-----------+-----------
--+-------------+------------------------------+-----------+------------------+-------+-----------------------+-------------+---------------+-------------+----------+------------------+
-------------+---------+--------+------------+--------+-----+--------+--------------+-------------+----------+-------------+-------------+----------+-------------+----------------------
-------+-------+------------+------+-----------+--------------+---------------------+----------+--------------------+--------------+---------------------+-------------------------+-----
---------+------------+---------------+----------------+---------------+--------------+---------------------+---------+-------------+--------------+-----------+-----------+-----------+-
--------+-------------+--------------+-------------+--------------+-------------+-----------+---------------------+-------------+----------+------------+------+-------+-------
2020-05-07 | 99999999182369779 | orauser002 | 113847 | ZHT_DX大兴 | 20248811 | ZTE | BS8700 | 6.1.10.206 | 255.255.255.252 | 6.1.103.205 | 1000 | 6
(1 row)
testdb=# drop foreign table TEST1;
DROP FOREIGN TABLE
testdb=# IMPORT FOREIGN SCHEMA "ORAUSER" LIMIT TO (TEST1) FROM SERVER oradb INTO public;
IMPORT FOREIGN SCHEMA
testdb=# drop foreign table TEST1;
DROP FOREIGN TABLE
testdb=# IMPORT FOREIGN SCHEMA orauser LIMIT TO (TEST1) FROM SERVER oradb INTO public;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
testdb=# IMPORT FOREIGN SCHEMA "orauser" LIMIT TO (TEST1) FROM SERVER oradb INTO public;
ERROR: remote schema "orauser" does not exist
HINT: Enclose the schema name in double quotes to prevent case folding.
testdb=# create schema jyc;
CREATE SCHEMA
testdb=# IMPORT FOREIGN SCHEMA "ORAUSER" LIMIT TO (TEST1) FROM SERVER oradb INTO jyc;
IMPORT FOREIGN SCHEMA
testdb=# set search_path=jyc;
SET
testdb=# \dt
Did not find any relations.
testdb=# \det
List of foreign tables
Schema | Table | Server
--------+--------------------------+------------
jyc | TEST1 | oradb
(1 row)
testdb=# select * from TEST1 limit 1;
logdate | fldid | fldtag | enbcode | enbname | station_id | vendorname | metype | ipaddr | networkmask | gatewayip | egressmbr | remoteaddr
1 | remoteaddr2 | swversion | radiomode | operationalstate | state | source_identification | source_type | source_vender | source_name | district | maintenance_area |
maintenance | tenders | area | cover_type | btsid | bsc | radius | longitude | latitude | cpu_load | cpu_load_pj | btstypename | objectid | check_state | shape_param
| isadd | isprostate | l800 | userlabel | sales_unit | createtime | isdelete | site | intersection | intersection_time | site_name | user
label_xy | user_types | source_number | community_type | community_rru | station_name | updatemain_date | imptype | grid_layout | is_ty_change | old_state | breakdays | breaktime |
builder | breakreason | sj_longitude | sj_latitude | wg_longitude | wg_latitude | coor_type | currmonth_breakdays | programenum | designer | supervisor | dcip | dcomc | bsnum
---------------------+-------------------+---------+---------+------------------+------------+------------+--------+-------------+-----------------+-------------+-----------+-----------
--+-------------+------------------------------+-----------+------------------+-------+-----------------------+-------------+---------------+-------------+----------+------------------+
-------------+---------+--------+------------+--------+-----+--------+--------------+-------------+----------+-------------+-------------+----------+-------------+----------------------
-------+-------+------------+------+-----------+--------------+---------------------+----------+--------------------+--------------+---------------------+-------------------------+-----
---------+------------+---------------+----------------+---------------+--------------+---------------------+---------+-------------+--------------+-----------+-----------+-----------+-
--------+-------------+--------------+-------------+--------------+-------------+-----------+---------------------+-------------+----------+------------+------+-------+-------
2020-05-07 | 99999999182369779 | orauser002 | 113847 | ZHT_DX大兴 | 20248811 | ZTE | BS8700 | 6.1.10.206 | 255.255.255.252 | 6.1.103.205 | 1000 | 6
(1 row)
testdb=#
testdb=# SELECT a.attnum,
testdb-# a.attname AS field,
testdb-# t.typname AS type,
testdb-# a.attlen AS length,
testdb-# a.atttypmod AS lengthvar,
testdb-# a.attnotnull AS notnull,
testdb-# b.description AS comment
testdb-# FROM pg_class c,
testdb-# pg_attribute a
testdb-# LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
testdb-# pg_type t
testdb-# WHERE c.relname = 'TEST1'
testdb-# and a.attnum > 0
testdb-# and a.attrelid = c.oid
testdb-# and a.atttypid = t.oid
testdb-# ORDER BY a.attnum;
attnum | field | type | length | lengthvar | notnull | comment
--------+-----------------------+-----------+--------+-----------+---------+---------
1 | logdate | date | 4 | -1 | f |
1 | logdate | timestamp | 8 | 0 | f |
2 | fldid | varchar | -1 | -1 | f |
2 | fldid | varchar | -1 | 36 | t |
3 | fldtag | varchar | -1 | 68 | f |
3 | fldtag | varchar | -1 | -1 | f |
4 | enbcode | varchar | -1 | 260 | f |
4 | enbcode | varchar | -1 | -1 | f |
5 | enbname | varchar | -1 | 260 | f |
5 | enbname | varchar | -1 | -1 | f |
6 | station_id | varchar | -1 | 260 | f |
6 | station_id | varchar | -1 | -1 | f |
7 | vendorname | varchar | -1 | 260 | f |
7 | vendorname | varchar | -1 | -1 | f |
8 | metype | varchar | -1 | 260 | f |
8 | metype | varchar | -1 | -1 | f |
9 | ipaddr | varchar | -1 | 260 | f |
9 | ipaddr | varchar | -1 | -1 | f |
10 | networkmask | varchar | -1 | -1 | f |
10 | networkmask | varchar | -1 | 260 | f |
11 | gatewayip | varchar | -1 | -1 | f |
11 | gatewayip | varchar | -1 | 260 | f |
12 | egressmbr | varchar | -1 | 260 | f |
12 | egressmbr | varchar | -1 | -1 | f |
13 | remoteaddr1 | varchar | -1 | -1 | f |
13 | remoteaddr1 | varchar | -1 | 260 | f |
14 | remoteaddr2 | varchar | -1 | 260 | f |
14 | remoteaddr2 | varchar | -1 | -1 | f |
15 | swversion | varchar | -1 | -1 | f |
15 | swversion | varchar | -1 | 260 | f |
16 | radiomode | varchar | -1 | 260 | f |
16 | radiomode | varchar | -1 | -1 | f |
17 | operationalstate | varchar | -1 | -1 | f |
17 | operationalstate | varchar | -1 | 260 | f |
18 | state | varchar | -1 | 260 | f |
18 | state | varchar | -1 | -1 | f |
19 | source_identification | varchar | -1 | -1 | f |
19 | source_identification | varchar | -1 | 260 | f |
20 | source_type | varchar | -1 | 260 | f |
20 | source_type | varchar | -1 | -1 | f |
21 | source_vender | varchar | -1 | -1 | f |
21 | source_vender | varchar | -1 | 260 | f |
22 | source_name | varchar | -1 | -1 | f |
22 | source_name | varchar | -1 | 260 | f |
23 | district | varchar | -1 | -1 | f |
23 | district | varchar | -1 | 260 | f |
24 | maintenance_area | varchar | -1 | -1 | f |
24 | maintenance_area | varchar | -1 | 260 | f |
25 | maintenance | varchar | -1 | -1 | f |
25 | maintenance | varchar | -1 | 260 | f |
26 | tenders | varchar | -1 | -1 | f |
26 | tenders | varchar | -1 | 260 | f |
27 | area | varchar | -1 | 260 | f |
27 | area | varchar | -1 | -1 | f |
28 | cover_type | varchar | -1 | -1 | f |
28 | cover_type | varchar | -1 | 260 | f |
29 | btsid | varchar | -1 | 260 | f |
29 | btsid | varchar | -1 | -1 | f |
30 | bsc | varchar | -1 | -1 | f |
30 | bsc | varchar | -1 | 260 | f |
31 | radius | varchar | -1 | -1 | f |
31 | radius | varchar | -1 | 260 | f |
32 | longitude | numeric | -1 | 1048588 | f |
32 | longitude | numeric | -1 | -1 | f |
33 | latitude | numeric | -1 | 1048588 | f |
33 | latitude | numeric | -1 | -1 | f |
34 | cpu_load | varchar | -1 | 260 | f |
34 | cpu_load | varchar | -1 | -1 | f |
35 | cpu_load_pj | varchar | -1 | 260 | f |
35 | cpu_load_pj | varchar | -1 | -1 | f |
36 | btstypename | varchar | -1 | -1 | f |
36 | btstypename | varchar | -1 | 260 | f |
37 | objectid | varchar | -1 | -1 | f |
37 | objectid | varchar | -1 | 24 | f |
38 | check_state | varchar | -1 | 68 | f |
38 | check_state | varchar | -1 | -1 | f |
39 | shape_param | varchar | -1 | -1 | f |
39 | shape_param | varchar | -1 | 132 | f |
40 | isadd | varchar | -1 | 36 | f |
40 | isadd | varchar | -1 | -1 | f |
41 | isprostate | varchar | -1 | 36 | f |
41 | isprostate | varchar | -1 | -1 | f |
42 | l800 | varchar | -1 | 68 | f |
42 | l800 | varchar | -1 | -1 | f |
43 | userlabel | varchar | -1 | 260 | f |
43 | userlabel | varchar | -1 | -1 | f |
44 | sales_unit | varchar | -1 | -1 | f |
44 | sales_unit | varchar | -1 | 260 | f |
45 | createtime | timestamp | 8 | 0 | f |
45 | createtime | date | 4 | -1 | f |
46 | isdelete | varchar | -1 | 68 | f |
46 | isdelete | varchar | -1 | -1 | f |
47 | site | varchar | -1 | -1 | f |
47 | site | varchar | -1 | 68 | f |
48 | intersection | varchar | -1 | 260 | f |
48 | intersection | varchar | -1 | -1 | f |
49 | intersection_time | varchar | -1 | -1 | f |
49 | intersection_time | varchar | -1 | 1028 | f |
50 | site_name | varchar | -1 | 1028 | f |
50 | site_name | varchar | -1 | -1 | f |
51 | userlabel_xy | varchar | -1 | -1 | f |
51 | userlabel_xy | varchar | -1 | 1028 | f |
52 | user_types | varchar | -1 | 1028 | f |
52 | user_types | varchar | -1 | -1 | f |
53 | source_number | numeric | -1 | -1 | f |
53 | source_number | numeric | -1 | 1572868 | f |
54 | community_type | varchar | -1 | 1028 | f |
54 | community_type | varchar | -1 | -1 | f |
55 | community_rru | numeric | -1 | 1572868 | f |
55 | community_rru | numeric | -1 | -1 | f |
56 | station_name | varchar | -1 | 1028 | f |
56 | station_name | varchar | -1 | -1 | f |
57 | updatemain_date | timestamp | 8 | 0 | f |
57 | updatemain_date | date | 4 | -1 | f |
58 | imptype | varchar | -1 | -1 | f |
58 | imptype | varchar | -1 | 68 | f |
59 | grid_layout | varchar | -1 | -1 | f |
59 | grid_layout | varchar | -1 | 1028 | f |
60 | is_ty_change | varchar | -1 | 1028 | f |
60 | is_ty_change | varchar | -1 | -1 | f |
61 | old_state | varchar | -1 | 260 | f |
61 | old_state | varchar | -1 | -1 | f |
62 | breakdays | int4 | 4 | -1 | f |
62 | breakdays | numeric | -1 | -1 | f |
63 | breaktime | date | 4 | -1 | f |
63 | breaktime | timestamp | 8 | 0 | f |
64 | builder | varchar | -1 | 132 | f |
64 | builder | varchar | -1 | -1 | f |
65 | breakreason | varchar | -1 | 132 | f |
65 | breakreason | varchar | -1 | -1 | f |
66 | sj_longitude | numeric | -1 | 1048588 | f |
66 | sj_longitude | numeric | -1 | -1 | f |
67 | sj_latitude | numeric | -1 | 1048588 | f |
67 | sj_latitude | numeric | -1 | -1 | f |
68 | wg_longitude | numeric | -1 | -1 | f |
68 | wg_longitude | numeric | -1 | 1048588 | f |
69 | wg_latitude | numeric | -1 | -1 | f |
69 | wg_latitude | numeric | -1 | 1048588 | f |
70 | coor_type | varchar | -1 | 14 | f |
70 | coor_type | varchar | -1 | -1 | f |
71 | currmonth_breakdays | int2 | 2 | -1 | f |
71 | currmonth_breakdays | numeric | -1 | -1 | f |
72 | programenum | varchar | -1 | -1 | f |
72 | programenum | varchar | -1 | 68 | f |
73 | designer | varchar | -1 | -1 | f |
73 | designer | varchar | -1 | 68 | f |
74 | supervisor | varchar | -1 | -1 | f |
74 | supervisor | varchar | -1 | 68 | f |
75 | dcip | varchar | -1 | -1 | f |
75 | dcip | varchar | -1 | 260 | f |
76 | dcomc | varchar | -1 | 260 | f |
76 | dcomc | varchar | -1 | -1 | f |
77 | bsnum | varchar | -1 | 128 | f |
77 | bsnum | varchar | -1 | -1 | f |
(154 rows)
testdb=# set search_path=pguser;
SET
testdb=# \det
List of foreign tables
Schema | Table | Server
----------+--------------------------+------------
pguser | TEST1 | oradb
(1 row)
检查表结构的SQL语句参考：
testdb=# SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 'TEST1'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;
attnum | field | type | length | lengthvar | notnull | comment
--------+-----------------------+-----------+--------+-----------+---------+---------
1 | logdate | date | 4 | -1 | f |
1 | logdate | timestamp | 8 | 0 | f |
2 | fldid | varchar | -1 | -1 | f |
2 | fldid | varchar | -1 | 36 | t |
3 | fldtag | varchar | -1 | 68 | f |
3 | fldtag | varchar | -1 | -1 | f |
4 | enbcode | varchar | -1 | 260 | f |
4 | enbcode | varchar | -1 | -1 | f |
5 | enbname | varchar | -1 | 260 | f |
5 | enbname | varchar | -1 | -1 | f |
6 | station_id | varchar | -1 | 260 | f |
6 | station_id | varchar | -1 | -1 | f |
7 | vendorname | varchar | -1 | 260 | f |
7 | vendorname | varchar | -1 | -1 | f |
8 | metype | varchar | -1 | 260 | f |
8 | metype | varchar | -1 | -1 | f |
9 | ipaddr | varchar | -1 | 260 | f |
9 | ipaddr | varchar | -1 | -1 | f |
10 | networkmask | varchar | -1 | -1 | f |
10 | networkmask | varchar | -1 | 260 | f |
11 | gatewayip | varchar | -1 | -1 | f |
11 | gatewayip | varchar | -1 | 260 | f |
12 | egressmbr | varchar | -1 | 260 | f |
12 | egressmbr | varchar | -1 | -1 | f |
13 | remoteaddr1 | varchar | -1 | -1 | f |
13 | remoteaddr1 | varchar | -1 | 260 | f |
14 | remoteaddr2 | varchar | -1 | 260 | f |
14 | remoteaddr2 | varchar | -1 | -1 | f |
15 | swversion | varchar | -1 | -1 | f |
15 | swversion | varchar | -1 | 260 | f |
16 | radiomode | varchar | -1 | 260 | f |
16 | radiomode | varchar | -1 | -1 | f |
17 | operationalstate | varchar | -1 | -1 | f |
17 | operationalstate | varchar | -1 | 260 | f |
18 | state | varchar | -1 | 260 | f |
18 | state | varchar | -1 | -1 | f |
19 | source_identification | varchar | -1 | -1 | f |
19 | source_identification | varchar | -1 | 260 | f |
20 | source_type | varchar | -1 | 260 | f |
20 | source_type | varchar | -1 | -1 | f |
21 | source_vender | varchar | -1 | -1 | f |
21 | source_vender | varchar | -1 | 260 | f |
22 | source_name | varchar | -1 | -1 | f |
22 | source_name | varchar | -1 | 260 | f |
23 | district | varchar | -1 | -1 | f |
23 | district | varchar | -1 | 260 | f |
24 | maintenance_area | varchar | -1 | -1 | f |
24 | maintenance_area | varchar | -1 | 260 | f |
25 | maintenance | varchar | -1 | -1 | f |
25 | maintenance | varchar | -1 | 260 | f |
26 | tenders | varchar | -1 | -1 | f |
26 | tenders | varchar | -1 | 260 | f |
27 | area | varchar | -1 | 260 | f |
27 | area | varchar | -1 | -1 | f |
28 | cover_type | varchar | -1 | -1 | f |
28 | cover_type | varchar | -1 | 260 | f |
29 | btsid | varchar | -1 | 260 | f |
29 | btsid | varchar | -1 | -1 | f |
30 | bsc | varchar | -1 | -1 | f |
30 | bsc | varchar | -1 | 260 | f |
31 | radius | varchar | -1 | -1 | f |
31 | radius | varchar | -1 | 260 | f |
32 | longitude | numeric | -1 | 1048588 | f |
32 | longitude | numeric | -1 | -1 | f |
33 | latitude | numeric | -1 | 1048588 | f |
33 | latitude | numeric | -1 | -1 | f |
34 | cpu_load | varchar | -1 | 260 | f |
34 | cpu_load | varchar | -1 | -1 | f |
35 | cpu_load_pj | varchar | -1 | 260 | f |
35 | cpu_load_pj | varchar | -1 | -1 | f |
36 | btstypename | varchar | -1 | -1 | f |
36 | btstypename | varchar | -1 | 260 | f |
37 | objectid | varchar | -1 | -1 | f |
37 | objectid | varchar | -1 | 24 | f |
38 | check_state | varchar | -1 | 68 | f |
38 | check_state | varchar | -1 | -1 | f |
39 | shape_param | varchar | -1 | -1 | f |
39 | shape_param | varchar | -1 | 132 | f |
40 | isadd | varchar | -1 | 36 | f |
40 | isadd | varchar | -1 | -1 | f |
41 | isprostate | varchar | -1 | 36 | f |
41 | isprostate | varchar | -1 | -1 | f |
42 | l800 | varchar | -1 | 68 | f |
42 | l800 | varchar | -1 | -1 | f |
43 | userlabel | varchar | -1 | 260 | f |
43 | userlabel | varchar | -1 | -1 | f |
44 | sales_unit | varchar | -1 | -1 | f |
44 | sales_unit | varchar | -1 | 260 | f |
45 | createtime | timestamp | 8 | 0 | f |
45 | createtime | date | 4 | -1 | f |
46 | isdelete | varchar | -1 | 68 | f |
46 | isdelete | varchar | -1 | -1 | f |
47 | site | varchar | -1 | -1 | f |
47 | site | varchar | -1 | 68 | f |
48 | intersection | varchar | -1 | 260 | f |
48 | intersection | varchar | -1 | -1 | f |
49 | intersection_time | varchar | -1 | -1 | f |
49 | intersection_time | varchar | -1 | 1028 | f |
50 | site_name | varchar | -1 | 1028 | f |
50 | site_name | varchar | -1 | -1 | f |
51 | userlabel_xy | varchar | -1 | -1 | f |
51 | userlabel_xy | varchar | -1 | 1028 | f |
52 | user_types | varchar | -1 | 1028 | f |
52 | user_types | varchar | -1 | -1 | f |
53 | source_number | numeric | -1 | -1 | f |
53 | source_number | numeric | -1 | 1572868 | f |
54 | community_type | varchar | -1 | 1028 | f |
54 | community_type | varchar | -1 | -1 | f |
55 | community_rru | numeric | -1 | 1572868 | f |
55 | community_rru | numeric | -1 | -1 | f |
56 | station_name | varchar | -1 | 1028 | f |
56 | station_name | varchar | -1 | -1 | f |
57 | updatemain_date | timestamp | 8 | 0 | f |
57 | updatemain_date | date | 4 | -1 | f |
58 | imptype | varchar | -1 | -1 | f |
58 | imptype | varchar | -1 | 68 | f |
59 | grid_layout | varchar | -1 | -1 | f |
59 | grid_layout | varchar | -1 | 1028 | f |
60 | is_ty_change | varchar | -1 | 1028 | f |
60 | is_ty_change | varchar | -1 | -1 | f |
61 | old_state | varchar | -1 | 260 | f |
61 | old_state | varchar | -1 | -1 | f |
62 | breakdays | int4 | 4 | -1 | f |
62 | breakdays | numeric | -1 | -1 | f |
63 | breaktime | date | 4 | -1 | f |
63 | breaktime | timestamp | 8 | 0 | f |
64 | builder | varchar | -1 | 132 | f |
64 | builder | varchar | -1 | -1 | f |
65 | breakreason | varchar | -1 | 132 | f |
65 | breakreason | varchar | -1 | -1 | f |
66 | sj_longitude | numeric | -1 | 1048588 | f |
66 | sj_longitude | numeric | -1 | -1 | f |
67 | sj_latitude | numeric | -1 | 1048588 | f |
67 | sj_latitude | numeric | -1 | -1 | f |
68 | wg_longitude | numeric | -1 | -1 | f |
68 | wg_longitude | numeric | -1 | 1048588 | f |
69 | wg_latitude | numeric | -1 | -1 | f |
69 | wg_latitude | numeric | -1 | 1048588 | f |
70 | coor_type | varchar | -1 | 14 | f |
70 | coor_type | varchar | -1 | -1 | f |
71 | currmonth_breakdays | int2 | 2 | -1 | f |
71 | currmonth_breakdays | numeric | -1 | -1 | f |
72 | programenum | varchar | -1 | -1 | f |
72 | programenum | varchar | -1 | 68 | f |
73 | designer | varchar | -1 | -1 | f |
73 | designer | varchar | -1 | 68 | f |
74 | supervisor | varchar | -1 | -1 | f |
74 | supervisor | varchar | -1 | 68 | f |
75 | dcip | varchar | -1 | -1 | f |
75 | dcip | varchar | -1 | 260 | f |
76 | dcomc | varchar | -1 | 260 | f |
76 | dcomc | varchar | -1 | -1 | f |
77 | bsnum | varchar | -1 | 128 | f |
77 | bsnum | varchar | -1 | -1 | f |
(154 rows)
testdb=#
小结：
使用oracle_fdw可以很方便的从pg库直接查询oracle中表和视图的信息，甚至对表dml增删改的操作。
另外对于迁移数据也很方便：创建外部表后，可以直接ctas的方式创建出符合pg格式的表。结合ora2pg工具https://ora2pg.darold.net/还可以大大提升迁移数据的速度http://www.migops.com/blog/2021/07/01/ora2pg-now-supports-oracle_fdw-to-increase-the-data-migration-speed/
最后修改时间：2022-03-11 15:27:34
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
