oracle_fdw部署配置

oracle_fdw部署配置

一、下载oracle客户端安装包和oracle_fdw源码包

step 1：oracle客户端下载地址：https://www.oracle.com/cn/database/technologies/instant-client/linux-x86-64-downloads.html

step 2：oracle_fdw下载地址：https://github.com/laurenz/oracle_fdw/

二、安装oracle客户端

step 3： 创建用户和目录：

groupadd oracle
useradd -G oralce oracle
passwd oracle
mkdir /opt/oracle
chown oracle:oracle /opt/oracle

step 4：解压oracle客户端文件到/opt/oracle

su oracle
unzip instantclient-basic-linux.x64-19.19.0.0.0dbru.zip -d /opt/oracle
unzip instantclient-sdk-linux.x64-19.19.0.0.0dbru.zip -d /opt/oracle
unzip instantclient-sqlplus-linux.x64-19.19.0.0.0dbru.zip -d /opt/oracle

step 5：配置环境

vi ~/.bash_profile

export ORACLE_HOME=/opt/oracle/instantclient_11_2
export SQLPATH=/opt/oracle/instantclient_11_2
export TNS_ADMIN=/opt/oracle/instantclient_11_2
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
export PATH=$ORACLE_HOME:$PATH

step 6：配置tns

vi /opt/oracle/instantclient_11_2/network/admin/tnsnames.ora

FDW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.106.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

step 7：用sqlplus连接数据库

sqlplus czt/JxGlpassw0rd@LNJX

三、安装oracle_fdw

step 8：解压源码包

unzip oracle_fdw-master.zip

step 9：编译安装

cd oracle_fdw-master
make PG_CONFIG=/opt/postgresql/bin/pg_config
make install PG_CONFIG=/opt/postgresql/bin/pg_config

step 10：配置oracle的环境变量到pg的数据库用户

vi /opt/postgresql/.bash_profile
export ORACLE_HOME=/opt/oracle/instantclient_11_2
export SQLPATH=/opt/oracle/instantclient_11_2
export TNS_ADMIN=/opt/oracle/instantclient_11_2
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
export PATH=$ORACLE_HOME:$PATH
source /opt/postgresql/.bash_profile

四、创建FDW外部表

step 11：创建fdw扩展、远程服务和用户映射

psql -U jxkh -d test
set search-path=gdmp;
CREATE EXTENSION oracle_fdw;
CREATE SERVER ora_test FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//10.24.0.239:1521/lnjx');
CREATE USER MAPPING FOR jxkh SERVER ora_test OPTIONS (user 'czt',password 'JxGlpassw0rd');

step 12：创建外部表

import FOREIGN SCHEMA "CZT" limit to (中央项目库分类) from server ora_test into gdmp;

注意：模式名和表名必须大写

step 13：其他语法

a. 定义外部表
  对于少量要查询的外部表

CREATE FOREIGN TABLE fdw_xssuj(
        "_id" varchar(64) NOT NULL,
    cls_id varchar(64) NOT NULL,
    销售日期 int8 NULL,
    品牌 text NULL,
    数量 int8 NULL,
    销售金额 numeric(38, 8) NULL,
    状态 text NULL,
    货物编号 text NULL,
    销售季度 text NULL,
    销售区域 text NULL,
    性别 text NULL,
    "ID" text NOT NULL,
    价格 int8 NULL,
    tenant varchar(64) NULL,
    create_user varchar(64) NULL,
    create_time int8 NULL,
    last_update_user varchar(64) NULL,
    last_update_time int8 NULL,
    ci_source varchar(64) NULL,
    ci_status varchar(128) NULL,
    t_version varchar(64) NULL
) SERVER server_pg options (schema_name 'gdmp_5in1',table_name '销售数据');

   b. 直接导入外部表
   更多的时候，我们仅需要直接使用源表的结构。这种场景下，通过直接导入的方式即可批量建立外部表。
   注意：如果源表的结构发生变化，大概率可能会造成查询失败。需要重新导入一次表结构。
指定表

– 仅导入指定的表，lahuobao库下的account，waybill两张表到src_lhb 模式(schema)下

import foreign schema lahuobao limit to (account,waybill) from server server_lhb into src_lhb;

一次性导入指定DB下的所有表

import foreign schema lahuobao from server server_lhb into src_lhb;

查看外部表信息
– 查看一下已经有哪些外部表

demo=> select * from information_schema.foreign_tables; foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name-----------------------+----------------------+--------------------+------------------------+--------------------- demo                  | src_lhb              | account_ft         | demo                   | server_lhb demo                  | src_lhb              | account            | demo                   | server_lhb demo                  | src_lhb              | waybill            | demo                   | server_lhb(3 rows)

  删除外部表

– 指定表名称，删除多个表

drop foreign table src_lhb.account_ft, src_lhb.account, src_lhb.waybill;

构造sql语句批量删除

select     'drop foreign table ' || t.table_schema || '.' || t.table_name || ';' as drop_sqlfrom information_schema.tables twhere t.table_type in ('FOREIGN')    and t.table_schema in ('src_lhb', 'public');

直接删除FDW扩展来删除所有外部表(必须是owner，这里就是admin用户)

drop extension mysql_fdw cascade;

查询数据

select * from fdw_xssuj

删除FDW时，执行以下命令：

drop user mapping for jxkh SERVER server_pg;
drop FOREIGN TABLE fdw_xssuj
drop server server_pg;

EOF