數(shù)據(jù)封裝器fdw（Foreign Data Wrappers）在PostgreSQL中相當(dāng)于oracle中的dblink，可以很方便的操作其他數(shù)據(jù)庫中的數(shù)據(jù)。
場景，在本地的test庫中通過外部數(shù)據(jù)封裝器fdw訪問本地的testdb中的t2表
本地庫test用戶u1，遠(yuǎn)程庫test用戶dbuser
版本：
postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit
(1 行記錄)
1,安裝postgres_fdw擴(kuò)展與授權(quán)
CREATE EXTENSION postgres_fdw;
如果需要授權(quán)
grant usage on foreign data wrapper postgres_fdw to u1;
2，然后使用CREATE SERVER創(chuàng)建一個(gè)外部服務(wù)器。
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'testdb');
3，用CREATE USER MAPPING 定義的用戶映射來標(biāo)識將在遠(yuǎn)程服務(wù)器上使用的角色：
CREATE USER MAPPING FOR public
SERVER foreign_server
OPTIONS (user 'dbuser', password '123456');
4，用CREATE FOREIGN TABLE創(chuàng)建外部表了。
在這個(gè)例子中我們希望訪問遠(yuǎn)程服務(wù)器上名為 some_schema.some_table的表。它的本地名稱是 foreign_table,注意結(jié)構(gòu)要對應(yīng)
CREATE FOREIGN TABLE foreign_table_t2(id int,name varchar(10))
SERVER foreign_server options(schema_name 'public',table_name 't2');
這樣就可以通過foreign_table_t2來操作遠(yuǎn)程的表t2了
當(dāng)然可以使用oracle_fdw,mysql_fdw，tds_fdw，redis_fdw等來操作別的數(shù)據(jù)庫
補(bǔ)充：PostgreSQL數(shù)據(jù)庫插件fdw使用心得—mysql_fdw、oracle_fdw、postgresql_fdw
fdw是foreign data wrapper的一個(gè)簡稱，叫做外部封裝數(shù)據(jù)。
用命令行登陸postgresql:
plsql -h ip地址 -p 端口號 -U 用戶名 -d 庫名
1、安裝fdw
fdw工具是pg自帶擴(kuò)展工具，pg10版本之后不需要再單獨(dú)安裝，一般分為postgresql_fdw、mysql_fdw、file_fdw、oracle_fdw等?？梢詫⒉煌漠悩?gòu)數(shù)據(jù)源當(dāng)成pg的外表。
2、創(chuàng)建庫連接
postgresql_fdw
一. 創(chuàng)建postgres_fdw擴(kuò)展（僅需第一次執(zhí)行）：
-- 創(chuàng)建postgresql_fdw擴(kuò)展
-- Foreign Data Wrapper: postgres_fdw
CREATE FOREIGN DATA WRAPPER postgres_fdw
-- 刪除該擴(kuò)展
-- DROP FOREIGN DATA WRAPPER postgres_fdw
二. 創(chuàng)建遠(yuǎn)程服務(wù)器，這里需要定義遠(yuǎn)程主機(jī)ip、數(shù)據(jù)庫庫名、端口號（同一個(gè)遠(yuǎn)程數(shù)據(jù)庫只需執(zhí)行一次）:
-- 創(chuàng)建ods_server服務(wù)對接遠(yuǎn)程數(shù)據(jù)庫的ods_db庫
-- Foreign Server: ods_server
CREATE SERVER ods_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '68.26.1.1', dbname 'ods_db', port '5432');
-- 刪除該服務(wù)
-- DROP SERVER ods_server;
三. 指定連接遠(yuǎn)程數(shù)據(jù)庫的用戶, 和創(chuàng)建的遠(yuǎn)程服務(wù)器一致（同一個(gè)遠(yuǎn)程數(shù)據(jù)庫只需執(zhí)行一次）：
-- 指定連接遠(yuǎn)程
-- User Mapping : xdh
CREATE USER MAPPING FOR ods_db SERVER ods_server
OPTIONS ("user" 'xha', password '123456');
-- 刪除該指定用戶
-- DROP USER MAPPING FOR creditrisk SERVER cscs
四. 在接受數(shù)據(jù)的pg數(shù)據(jù)庫創(chuàng)建外部表：
CREATE FOREIGN TABLE zha.student(
id integer,
name varchar(50),
age integer,
sex varchar(20)
)
SERVER ods_server
OPTIONS(schema_name'ods',table_name 'student_ods');
-- 將該外表的權(quán)限按需要賦權(quán)給其它用戶
GRANT ALL ON TABLE zha.student TO zha;
GRANT SELECT ON TABLE zha.student TO zhb;
Materialized View 物化視圖
五. 可將外部表映射成pg的物化視圖，以便進(jìn)行數(shù)據(jù)查詢等（同一張表只需執(zhí)行一次）：
-- Materialized View: gzk.student
-- DROP MATERIALIZED VIEW gzk.student;
CREATE MATERIALIZED VIEW gzk.student
AS
SELECT
id,
name,
age,
sex
from zha.student;
六. 刷新物化視圖的數(shù)據(jù)：
普通視圖的數(shù)據(jù)只是一個(gè)select，可以隨原表數(shù)據(jù)的變化而變化，但物化視圖類似于一個(gè)真正的表，可以創(chuàng)建索引，數(shù)據(jù)不會隨著原表的變化而變化，需要手動刷新數(shù)據(jù)。
-- 全量刷新(先清空表再重新映射數(shù)據(jù),刷新時(shí)阻塞select! 較快!)
refresh materialized view gzk.student;
-- 增量刷新(全表數(shù)據(jù)對比更新,刷新時(shí)不會阻塞select! 較慢!)
-- 只有當(dāng)物化視圖中存在unique index(唯一索引)的時(shí)候，
-- refresh物化視圖才能使用增量更新，加入concurrently參數(shù)。否則報(bào)錯(cuò)。
refresh materiallized view concurrently gzk.student;
-- 報(bào)錯(cuò)
-- ERROR: cannot refresh materialized view "gzk.student" concurrently
-- HINT: Create a unique index with no WHERE clause on one
-- or more columns of the materialized view.
-- 在物化視圖上創(chuàng)建unique index(唯一索引,可以以自增主鍵id為唯一索引)
create unique index uidx_mv_id on gzk.student(id);
refresh materiallized view concurrently gzk.student;
-- 執(zhí)行成功
-- 實(shí)際業(yè)務(wù)中可選增量刷新，定時(shí)執(zhí)行refresh的方式
七. 可以把物化視圖刷新sql放到pgAgent job里, 定時(shí)刷新數(shù)據(jù).
八. mysql_fdw、oracle_fdw、file_fdw用法類似,不做贅述。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL 中的postgres_fdw擴(kuò)展詳解
- PostgreSQL的B-tree索引用法詳解
- Postgresql 如何選擇正確的關(guān)閉模式
- PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法
- PostgreSQL copy 命令教程詳解
- Postgresql在mybatis中報(bào)錯(cuò):操作符不存在:character varying == unknown的問題
- postgresql 如何關(guān)閉自動提交
