PostgreSQL的外部數(shù)據(jù)封裝器fdw用法

更新時(shí)間：2021年01月19日 09:02:42 作者：朝聞道-夕死可矣

這篇文章主要介紹了PostgreSQL的外部數(shù)據(jù)封裝器fdw用法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

數(shù)據(jù)封裝器fdw（Foreign Data Wrappers）在PostgreSQL中相當(dāng)于oracle中的dblink，可以很方便的操作其他數(shù)據(jù)庫中的數(shù)據(jù)。

場(chǎng)景，在本地的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)識(shí)將在遠(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)要對(duì)應(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è)簡(jiǎn)稱，叫做外部封裝數(shù)據(jù)。

用命令行登陸postgresql:

plsql -h ip地址 -p 端口號(hào) -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ù)庫庫名、端口號(hào)（同一個(gè)遠(yuǎn)程數(shù)據(jù)庫只需執(zhí)行一次）:

-- 創(chuàng)建ods_server服務(wù)對(duì)接遠(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ù)不會(huì)隨著原表的變化而變化，需要手動(dòng)刷新數(shù)據(jù)。

-- 全量刷新(先清空表再重新映射數(shù)據(jù),刷新時(shí)阻塞select! 較快!)
refresh materialized view gzk.student;
-- 增量刷新(全表數(shù)據(jù)對(duì)比更新,刷新時(shí)不會(huì)阻塞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ò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: