postgres外部表之-oracle_fdw
1. 安装Oracle客户端工具
编译安装oracle_fdw之前，需要安装Oracle的客户端程序；步骤略
下载地址：http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html
2. 安装oracle_fdw
下载地址：http://pgxn.org/dist/oracle_fdw/
[root@Postgres201 opt]# unzip oracle_fdw-2.0.0.zip [root@Postgres201 opt]# cd oracle_fdw-2.0.0
#加载环境变量后执行 pg_config是否在对应PGHOME/bin目录下。编译后会在对应的目录下面
[root@Postgres201 oracle_fdw-2.0.0]# source /home/postgres/.bashrc [root@Postgres201 oracle_fdw-2.0.0]# pg_config [root@Postgres201 oracle_fdw-2.0.0]# make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/rdbms/public -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/opt/pgsql96/include/server -I/opt/pgsql96/include/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 -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/rdbms/public -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/opt/pgsql96/include/server -I/opt/pgsql96/include/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 -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/rdbms/public -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/opt/pgsql96/include/server -I/opt/pgsql96/include/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 -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/pgsql96/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql96/lib',--enable-new-dtags -L/u01/app/oracle -L/u01/app/oracle/bin -L/u01/app/oracle/lib -lclntsh -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 /usr/bin/ld: cannot find -lclntsh collect2: ld returned 1 exit status make: *** [oracle_fdw.so] Error 1
FAQ：执行make若出现“/usr/bin/ld: cannot find -lclntsh”；原因是找不到库liblclntsh文件；
解决方案：
1. 检查环境变量，看ORACLE有关的环境变量是否设置正确
2. 是否文件名字后有oracle版本信息；需要改名字
该文件在oracle安装目录下；本例是需要改名字即可
[root@Postgres201 oracle]# ln -sv libclntsh.so.11.1 libclntsh.so [root@Postgres201 oracle_fdw-2.0.0]# make [root@Postgres201 oracle_fdw-2.0.0]# make install /bin/mkdir -p '/opt/pgsql96/lib' /bin/mkdir -p '/opt/pgsql96/share/extension' /bin/mkdir -p '/opt/pgsql96/share/extension' /bin/mkdir -p '/opt/pgsql96/share/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/opt/pgsql96/lib/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/opt/pgsql96/share/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/opt/pgsql96/share/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/opt/pgsql96/share/doc/extension/' [root@Postgres201 oracle_fdw-2.0.0]# ll /opt/pgsql96/lib/oracle_fdw.so -rwxr-xr-x. 1 root root 156608 May 29 23:15 /opt/pgsql96/lib/oracle_fdw.so
安装完成！
3. 创建oracle_fdw外部表
本例oracle服务器是在192.168.1.221上；oracle_fdw是通过oci接口访问Oracle了，所以需要配置$ORACLE_HOME/network/admin/tnsnames.ora。
[root@Postgres201 admin]# vi tnsnames.ora ora221 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )
3.1 创建oracle_fdw
lottu=# create extension oracle_fdw; CREATE EXTENSION
查看以加载扩展模块
lottu=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- oracle_fdw | 1.1 | lottu | foreign data wrapper for Oracle access pg_stat_statements | 1.4 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers lottu=# select * from pg_foreign_data_wrapper ; fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions --------------+----------+------------+--------------+--------+------------ postgres_fdw | 10 | 41021 | 41022 | | oracle_fdw | 16384 | 49212 | 49213 | | (2 rows)
3.2 在本地库创建SERVER
#采用//IP|解析主机名/实例名 CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle221/orcl'); #采用解析tnsnames.ora文件获取服务名 CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ora221'); lottu=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ora221'); CREATE SERVER lottu=# GRANT USAGE ON FOREIGN SERVER oradb TO lottu; GRANT
查看信息
lottu=# select * from pg_foreign_server where srvname = 'oradb'; srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions ---------+----------+--------+---------+------------+-----------------+------------------- oradb | 16384 | 49216 | | | {lottu=U/lottu} | {dbserver=ora221} lottu=# \des List of foreign servers Name | Owner | Foreign-data wrapper -------+-------+---------------------- lottu | lottu | postgres_fdw oradb | lottu | oracle_fdw
3.3 在本地库创建user mapping
lottu=# CREATE USER MAPPING FOR lottu SERVER oradb OPTIONS (user 'lottu', password 'li0924'); CREATE USER MAPPING
查看信息
lottu=# select * from pg_user_mappings where srvname = 'oradb'; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+---------+------------------------------ 49218 | 49217 | oradb | 16384 | lottu | {user=lottu,password=li0924} lottu=# \deu List of user mappings Server | User name --------+----------- lottu | lottu oradb | lottu
3.4 创建foreign table
在oracle中有表oratab
SQL> conn lottu/li0924 Connected. SQL> desc oratab Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER TEXT VARCHAR2(30)
创建外部表
lottu=# CREATE FOREIGN TABLE pgtab(id int OPTIONS(key 'true'), text varchar(30)) server oradb OPTIONS (schema 'LOTTU', table 'ORATAB');
查看信息
lottu=# select * from pg_foreign_table; ftrelid | ftserver | ftoptions ---------+----------+------------------------------------------ 41032 | 41027 | {schema_name=lottu,table_name=user_info} 49225 | 49217 | {schema=LOTTU,table=ORATAB} (2 rows) lottu=# \det List of foreign tables Schema | Table | Server --------+-----------+-------- lottu | pgtab | oradb lottu | user_info | lottu (2 rows)
注意点
1. CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，
2. 原因是出现在OPTIONS (schema '×××', table '×××');里面的schema/table需要用大写标注
3. 在postgres9.3版本以后oracle_fdw支持对外部表的 Insert ,delete ,update ;增加表操作项 options(key 'true') (当值设置为 true|on|yes 表示不可以做增删改操作)
