--pg extension oracle_fdw编译安装与使用
1.下载oracle依赖包，下载oracle_fdw源码包
oracle依赖包下载地址:https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
下载两个包:
instantclient-basic-linux.x64-21.4.0.0.0dbru.zip
instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip
oracle_fdw下载地址:https://github.com/laurenz/oracle_fdw/tags
2.oracle依赖包和oracle_fdw源码包上传并解压
#oracle_fdw编译和后续使用都会依赖oracle的两个包
cd /app/pg12
unzip instantclient-basic-linux.x64-21.4.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip
tar -xvf oracle_fdw-ORACLE_FDW_2_4_0.tar.gz
#检查/app/pg12/instantclient_21_4是否存在libclntsh.so,不存在就创建软连接(编译时会使用)
cd /app/pg12/instantclient_21_4
ln -s libclntsh.so.21.1 libclntsh.so
3.设置环境变量
export ORACLE_HOME=/app/pg12/instantclient_21_4
export PG_HOME=/app/pg12
export LD_LIBRARY_PATH=$PG_HOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$PG_HOME/bin:$PATH.
#如pg为测试环境请重启数据库,否则可能会导致后续excreate extension oracle_fdw失败
#如pg为生成环境不方便重启,可在/etc/ld.so.conf.d/目录创建文件oracle-x86_64.conf,并加载动态库
echo "/app/pg12/instantclient_21_4">>etc/ld.so.conf.d/oracle-x86_64.conf
ldconfig
4.编译安装
cd /app/pg12/oracle_fdw-ORACLE_FDW_2_4_0
make && make install
5.oracle_fdw使用
#创建oracle_fdw扩展,并赋权给普通用户
create extension oracle_fdw;
grant usage on foreign data wrapper oracle_fdw to user_name; --赋权给所有用户user_name使用public
#创建server
create server server_name foreign data wrapper oracle_fdw OPTIONS (dbserver '//ip:port/service_name');
#创建user mapping (pg和ora user关联关系)
create user mapping for user_name server server_name OPTIONS (user 'ora_user_name',password 'password');
#创建外部表(注意SCHEMA_NAME和TABLE_NAME使用大写)
#两种方式创建外部表,第一种必须写表结构且与ora表结构一致(表名可以不一致),第二种导入的方式不用指定表结构(表名一致)
create foreign table foreign_table_name (column_name column_type) server server_name OPTIONS (schema 'SCHEMA_NAME',table 'TABLE_NAME');
import FOREIGN SCHEMA "SCHEMA_NAME" limit to (TABLE_NAME) from server server_name into local_schema_name;
#此时外部表只可以select操作,如需update和delete需设置某一列为主键
#创建外部表时设置
create foreign table foreign_table_name (column_name column_type options (key 'yes')) server server_name OPTIONS (schema 'SCHEMA_NAME',table 'TABLE_NAME');
#或者创建后设置
alter foreign table table_name alter column_name options (key 'yes');
--pg extension postgres_fdw扩展使用
create extension postgres_fdw;
grant usage on foreign data wrapper postgres_fdw to user_name;
create server pg_fdw_testdb foreign data wrapper postgres_fdw OPTIONS (host 'ip',port 'port',dbname 'dbname');
create user MAPPING FOR postgres SERVER pg_fdw_testdb OPTIONS ( user 'user_name',password 'password');
create foreign table foreign_table_name (column_name column_type) server pg_fdw_testdb options (schema_name 'public',table_name 'test');
--pg extension dblink扩展使用
create extension dblink;
grant usage on foreign data wrapper dblink_fdw to user_name;
select dblink_connect('dblink_name','hostaddr=ip port=port dbname=dbname user=user_name password=password');
select * from dblink('dblink_name','query sql') as (column_name column_type,column_name column_type...);--字段要对应
