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