PostgreSQL 通過 jdbc_fdw 連接查詢 DB2/Oracle 異構數據源

PostgreSQL 通過 jdbc_fdw 連接查詢 DB2/Oracle 異構數據源

jdbc_fdw 是 PostgreSQL 的外部數據包裝器，基於 JDBC 驅動實現異構數據庫（DB2、Oracle、MySQL、SQL Server 等）的只讀/讀寫查詢。本文記錄編譯安裝、DB2/Oracle 連接配置、外部表創建全過程。

一、環境準備

• PostgreSQL 9.5.5（已安裝至 /usr/local/pg9.5.5）
• JDK 1.6+（已安裝 /usr/local/jdk1.6.0_45）
• DB2 JDBC 驅動：db2jcc4.jar
• Oracle JDBC 驅動：ojdbc6.jar

二、配置 JVM 庫路徑

# 定位 libjvm.so
locate libjvm.so
# 選擇 server 版本（性能優於 client）
ln -s /usr/local/jdk1.6.0_45/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so
ldconfig

三、編譯安裝 jdbc_fdw

git clone git://github.com/atris/JDBC_FDW.git
cd JDBC_FDW
# 使用 PostgreSQL 的 pgxs 編譯
sudo PATH=/usr/local/pg9.5.5/bin/:$PATH make USE_PGXS=1
# 安裝擴展文件
cp jdbc_fdw.control /usr/local/pg9.5.5/share/postgresql/extension/
cp jdbc_fdw--1.0.sql /usr/local/pg9.5.5/share/postgresql/extension/
cp jdbc_fdw.so /usr/local/pg9.5.5/lib/postgresql/

四、創建 DB2 外部表

1. 檢查 DB2 目標表結構

db2 describe table TABLEA
# ID INTEGER, NAME CHAR(10), SALARY INTEGER

2. PostgreSQL 中創建擴展、服務器、用戶映射、外部表

-- 1. 創建擴展
CREATE EXTENSION jdbc_fdw;

-- 2. 創建外部服務器（DB2）
CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw
OPTIONS (
    drivername 'com.ibm.db2.jcc.DB2Driver',
    url 'jdbc:db2://108.88.3.106:60000/test',
    querytimeout '15',
    jarfile '/usr/local/pg9.5.5/lib/db2jcc4.jar',
    maxheapsize '600'
);

-- 3. 用戶映射
CREATE USER MAPPING FOR postgres SERVER jdbc_serv4
OPTIONS (username 'db2inst4', password 'db2inst4');

-- 4. 創建外部表（列定義需與遠程表匹配）
CREATE FOREIGN TABLE ghan_table (
    ID integer NOT NULL,
    NAME varchar(30),
    SALARY integer
) SERVER jdbc_serv4
OPTIONS (table 'TABLEA');

-- 5. 查詢驗證
SELECT * FROM ghan_table;

查詢結果

 id | name | salary
----+------+--------
1001 | SAM  |  10000
... (20 rows)

五、創建 Oracle 外部表

-- 1. 外部服務器（Oracle）
CREATE SERVER jdbc_serv5 FOREIGN DATA WRAPPER jdbc_fdw
OPTIONS (
    drivername 'oracle.jdbc.driver.OracleDriver',
    url 'jdbc:oracle:thin:@108.88.3.247:1521:orcl',
    querytimeout '15',
    jarfile '/usr/local/pg9.5.5/lib/ojdbc6.jar',
    maxheapsize '600'
);

-- 2. 用戶映射
CREATE USER MAPPING FOR postgres SERVER jdbc_serv5
OPTIONS (username 'zabbix', password 'zabbix');

-- 3. 外部表
CREATE FOREIGN TABLE ghan_ora (
    ID integer NOT NULL,
    NAME varchar(30)
) SERVER jdbc_serv5
OPTIONS (table 'test');

-- 4. 查詢
SELECT * FROM ghan_ora;

六、關鍵參數說明

七、常見問題與優化

• 字符集：確保 PostgreSQL client_encoding、JDBC URL characterEncoding、遠程數據庫字符集一致（建議 UTF-8）。
• 類型映射：DB2 CHAR -> PG varchar；Oracle NUMBER -> PG numeric；需手動對應。
• 性能：jdbc_fdw 目前不支持下推 WHERE 子句（版本依賴），全表掃描後在 PG 端過濾。大表建議在遠程創建視圖/物化視圖預聚合。
• 事務：外部表默認不支持分布式事務，寫入操作謹慎使用。
• 連接池：生產環境建議配合 PgBouncer 或 JDBC 連接池（HikariCP）複用連接。

八、替代方案對比

對於「 PostgreSQL 為主、異蹤源為輔、查詢量中等、實時性要求不極高」的場景，jdbc_fdw 是性價比最高的選擇。