PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續(xù)關注CUUG PG技術大講堂。

第27講：Oracle-FDW部署

內容1 : Foreign Data Wrappers(FDW)簡介與特性

內容2 : Oracle_FDW部署

內容3 : FDW執(zhí)行原理

Foreign Data Wrappers

Foreign Data Wrappers (FDW)

FDW部署

部署FDW(在客戶端部署)

1、安裝 oracle 19c client(postgres用戶)

mkdir /usr/local/oracle

unzip -d /usr/local/oracle instantclient-basic-linux.x64-19.6.0.0.0dbru.zip

unzip -d /usr/local/oracle instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip

2、配置環(huán)境變量(臨時設置)

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pg12.2/lib:/usr/local/oracle/instantclient_19_6/

export PATH=$PATH:/usr/local/pg12.2/bin

export ORACLE_HOME=/usr/local/oracle/instantclient_19_6

3、編譯oracle_fdw(需要單獨下載)

unzip oracle_fdw-2.2.0.zip

cd oracle_fdw-2.2.0

make

make install

4、安裝oracle_fdw插件(哪個database上使用，就在哪個database上安裝)

create extension oracle_fdw;

執(zhí)行會報錯：

ERROR: could not load library "/usr/local/pgsql-10.6/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

1、通過以下步驟解決(root)，編輯 /etc/ld.so.conf.d/oracle-x86_64.conf

/usr/local/oracle/instantclient_11_2

-------------------------------------

/usr/local/oracle/instantclient_19_6

2、執(zhí)行

ldconfig

5、創(chuàng)建fdw服務器

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw

OPTIONS (dbserver '//pg1:1521/PROD1');

pg1：遠程oracle主機名

1521：遠程oracle數據庫監(jiān)聽端口

PROD1：遠程oracle service名字

6、授權

GRANT USAGE ON FOREIGN SERVER pgdb TO oracle_fdw1;

7、創(chuàng)建用戶映射(本地用戶與遠程用戶映射)

CREATE USER MAPPING FOR oracle_fdw1 SERVER oradb

OPTIONS (user SCOTT', password ‘TIGER');

user：遠程數據庫用戶

password：用戶密碼

8、創(chuàng)建FDW表(以oracle_fdw1用戶創(chuàng)建)

CREATE FOREIGN TABLE emp_fdw (

EMPNO int ,

ENAME VARCHAR(10),

JOB VARCHAR(9),

MGR int,

HIREDATE date,

SAL float4,

COMM float4,

DEPTNO int

) SERVER oradb OPTIONS (schema 'SCOTT', table 'EMP');

Schema_name：必須大寫，因為Oracle數據字典中默認是大寫

Table_name：需要訪問的表表名

9、創(chuàng)建FDW表(以oracle_fdw1用戶創(chuàng)建)

CREATE FOREIGN TABLE dept_fdw (

deptno integer,

dname character varying(14),

loc character varying(13)

)SERVER oradb OPTIONS (schema 'SCOTT', table 'DEPT');

Schema_name：public，特定schema用戶創(chuàng)建的表，則寫該schema名字

Table_name：需要訪問的表表名

10、訪問FDW表(以oracle_fdw1用戶訪問)

SELECT * FROM emp_fdw;

SELECT * FROM dept_fdw;

*訪問FDW表的語法與訪問本地表一樣。

FDW原理

FDW執(zhí)行流程

FDW執(zhí)行流程

1、Creating a Query Tree(訪問pg_catalog.pg_class和pg_catalog.pg_foreign_table)

2、Connecting to the Remote Server(使用libpq庫)

3、Creating a Plan Tree Using EXPLAIN Commands (Optional)(訪問pg_catalog.pg_user_mapping和pg_catalog.pg_foreign_server)

4、Deparesing(postgres_fdw從通過解析和分析創(chuàng)建的查詢樹中重新創(chuàng)建一個純文本文件，在PostgreSQL中稱為deparsing。)

5、Sending SQL Statements and Receiving Result

各個版本功能演進

執(zhí)行DML操作(PG-Oracle不支持DML操作)

多表查詢

Version 9.6以前版本

localdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND a.id < 200;

QUERY PLAN

------------------------------------------------------------------------------

Merge Join (cost=532.31..700.34 rows=10918 width=16)

Merge Cond: (a.id = b.id)

-> Sort (cost=200.59..202.72 rows=853 width=8)

Sort Key: a.id

-> Foreign Scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

-> Sort (cost=331.72..338.12 rows=2560 width=8)

Sort Key: b.id

-> Foreign Scan on tbl_b b (cost=100.00..186.80 rows=2560 width=8)

(8 rows)

多表查詢

(PG-PG)如果使用ALTER SERVER命令將use_remote_estimate選項設置為on，則計劃器將通過執(zhí)行EXPLAIN命令向遠程服務器查詢計劃的成本，此時連接操作在遠程進行，提高性能。

ALTER SERVER pgdb OPTIONS (use_remote_estimate 'on');

\des+

localdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND a.id < 200;

QUERY PLAN

-----------------------------------------------------------

Foreign Scan (cost=134.35..244.45 rows=80 width=16)

Relations: (public.tbl_a a) INNER JOIN (public.tbl_b b)

(2 rows)

排序操作

在9.5或更早版本中：

localdb=# EXPLAIN SELECT * FROM tbl_a AS a WHERE a.id < 200 ORDER BY a.id;

QUERY PLAN

-----------------------------------------------------------------------

Sort (cost=200.59..202.72 rows=853 width=8)

Sort Key: id

-> Foreign Scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

(3 rows)

排序操作

在9.6或以后版本中：

localdb=# EXPLAIN SELECT * FROM tbl_a AS a WHERE a.id < 200 ORDER BY a.id;

QUERY PLAN

-----------------------------------------------------------------

Foreign Scan on tbl_a a (cost=100.00..167.46 rows=853 width=8)

(1 row)

聚組函數操作

在9.6或更早版本中：

localdb=# EXPLAIN SELECT AVG(data) FROM tbl_a AS a WHERE a.id < 200;

QUERY PLAN

-----------------------------------------------------------------------

Aggregate (cost=168.50..168.51 rows=1 width=4)

-> Foreign Scan on tbl_a a (cost=100.00..166.06 rows=975 width=4)

(2 rows)

聚組函數操作

在10或以后版本中：

localdb=# EXPLAIN SELECT AVG(data) FROM tbl_a AS a WHERE a.id < 200;

QUERY PLAN

-----------------------------------------------------

Foreign Scan (cost=102.44..149.03 rows=1 width=32)

Relations: Aggregate on (public.tbl_a a)

(2 rows)

以上就是【PostgreSQL從小白到專家】第27講 - Oracle-FDW部署? 的內容，歡迎一起探討交流釘釘交流群：35，82，24，60，往期視頻及文檔內容聯(lián)系CUUG