编译并安装 make --> make install, 可使用ldd oracle_fdw.so查看依赖
PostgreSQL中配置oracle_fdw
配置oracle动态链接库,否则在创建extension时会报错
cd /etc/ld.so.conf.d/
echo "/usr/local/oracle/instantclient_19_8" > oracle-x86_64.conf
ldconfig
创建oracle_fdw和外部server
/*注意修改以下代码的ip、用户名和密码*/
SELECT * FROM pg_available_extensions a WHERE a.name LIKE '%oracle%';
/*创建oracle fdw*/
CREATE extension oracle_fdw;
create server testserver foreign data wrapper oracle_fdw options(dbserver '192.168.1.10:1521/orcl');
grant usage on foreign server testserver to postgres;
create user mapping for postgres server testserver options(user 'USERNAME',password '123456');
创建外部表,注意schema和table要大写,server与上一步骤创建的server相同
DROP FOREIGN TABLE if exists test_table;
create foreign TABLE IF NOT exists test_table(
ID VARCHAR(50)
) server testserver options(schema 'USERNAME',table 'TEST_TABLE');
SELECT * FROM test_table;