大數據就業實戰培訓 Oracle就業實戰培訓
PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。
第26講：PG-FDW部署
內容1 : Foreign Data Wrappers(FDW)簡介與特性
內容2 : Postgres_FDW部署
內容3 : FDW執行原理
內容4 : 本地外部表操作
Foreign Data Wrappers
Foreign Data Wrappers (FDW)
FDW部署
部署FDW(在客戶端部署)
1、編譯postgres_fdw
cd /soft/postgresql-12.2/contrib/postgres_fdw
make
make install
2、安裝postgres_fdw (哪個database上使用，就在哪個database上安裝)
create extension postgres_fdw;
3、創建fdw服務器
CREATE SERVER pgdb FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'pg2',port '1922',dbname 'testdb');
Host：遠程主機名、ip地址
Port：遠程數據庫監聽端口
Dbname：遠程服務器名字
4、授權
GRANT USAGE ON FOREIGN SERVER pgdb TO pg_fdw1;
5、創建用戶映射(本地用戶與遠程用戶映射)
CREATE USER MAPPING FOR pg_fdw1 SERVER pgdb
OPTIONS (user 'scott', password 'tiger');
user：遠程數據庫用戶
password：用戶密碼
6、創建FDW表(以pg_fdw1用戶創建)
GRANT USAGE ON FOREIGN SERVER pgdb TO scott_pg;
6、創建FDW表(以pg_fdw1用戶創建)
CREATE FOREIGN TABLE emp_fdw (
EMPNO int ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE date,
SAL float4,
COMM float4,
DEPTNO int
) SERVER pgdb OPTIONS (schema_name 'public', table_name 'emp');
Schema_name：public，特定schema用戶創建的表，則寫該schema名字
Table_name：需要訪問的表表名
7、創建FDW表(以pg_fdw1用戶創建)
CREATE FOREIGN TABLE dept_fdw (
deptno integer,
dname character varying(14),
loc character varying(13)
)SERVER pgdb OPTIONS (schema_name ‘public', table_name 'dept');;
Schema_name：public，特定schema用戶創建的表，則寫該schema名字
Table_name：需要訪問的表表名
8、訪問FDW表(以pg_fdw1用戶訪問)
SELECT * FROM emp_fdw;
SELECT * FROM dept_fdw;
*訪問FDW表的語法與訪問本地表一樣。
FDW原理
FDW執行流程
FDW執行流程
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從通過解析和分析創建的查詢樹中重新創建一個純文本文件，在PostgreSQL中稱為deparsing。)
5、Sending SQL Statements and Receiving Result
FDW執行流程(PG-PG)
在遠程服務器端打開日志，可以查看到訪問流程：
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log
log_filename = 'postgresql-%Y-%m-%d'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_error_verbosity = verbose
log_statement = all
各個版本功能演進
執行DML操作(PG-PG支持DML操作，其它不支持)
PostgreSQL_FDW不會檢測死鎖
localdb=# -- Client A
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# -- Client B
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1
多表查詢
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，則計劃器將通過執行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)
本地外部表
本地外部表
1、--添加擴展
CREATE EXTENSION file_fdw;
2、--創建SERVER FOR file
create server pg_file_server foreign data wrapper file_fdw;
3、--創建外部表，與外部文件結構一致
create foreign table emp_file_fdw
(EMPNO int,
ENAME varchar(10),
JOB varchar(9),
MGR int,
HIREDATE DATE,
SAL int,
COMM int,
DEPTNO int)
server pg_file_server
options(filename '/home/postgres/emp.csv',format 'csv',header 'true',delimiter ',');
以上就是【PostgreSQL從小白到專家】第26講 - PG-FDW部署 的內容，歡迎一起探討交流釘釘交流群：35，82，24，60，往期視頻及文檔內容聯系CUUG
