PostgreSQL的外部数据封装器fdw用法

数据封装器fdw（Foreign Data Wrappers）在(zai)PostgreSQL中相当于oracle中的部数dblink，可以很方便的(de)据封操作其他数据库中的数据。

场景，装器在本地的部数test库中通过外(wai)部数据封装器fdw访问本地的(de)testdb中的t2表

本地库test用户u1，远程库test用户dbuser

版本：

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 11.1,据封 compiled by Visual C++ build 1914, 64-bit
(1 行(xing)记录)

1,安装postgres_fdw扩展与授权

1CREATE EXTENSION postgres_fdw;

如果需要授权

1grant usage on foreign data wrapper postgres_fdw to u1;

2，然后使用CREATE SERVER创建一个外部服务器。装器

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1',部数 port '5432', dbname 'testdb');

3，用CREATE USER MAPPING 定义的据封用户映射来标识将在(zai)远程服务器上使用的角色：

CREATE USER MAPPING FOR public
SERVER foreign_server
OPTIONS (user 'dbuser', password '123456');

4，用CREATE FOREIGN TABLE创建外部表了。装器

在这个例子中我们希望访问远程服务器上(shang)名为(wei) some_schema.some_table的部数表(biao)。它的据封本地名称是 foreign_table,注意结构要对应

CREATE FOREIGN TABLE foreign_table_t2(id int,name varchar(10))
SERVER foreign_server options(schema_name 'public',table_name 't2');

当然可以使用oracle_fdw,mysql_fdw，tds_fdw，装器redis_fdw等(deng)来操作别的部数数据库

补充：PostgreSQL数据库插件fdw使用心得(de)—mysql_fdw、oracle_fdw、据(ju)封postgresql_fdw

用(yong)命令行登陆postgresql:

plsql -h ip地址 -p 端口号(hao) -U 用户名 -d 库名

1、安装fdw

fdw工具是pg自带扩展工具，pg10版本之(zhi)后不需要再单独安装，一般分(fen)为postgresql_fdw、mysql_fdw、file_fdw、oracle_fdw等。可(ke)以将不同的异构数(shu)据源当成pg的外表。

2、创建库连接

postgresql_fdw

一. 创建postgres_fdw扩展（仅需第一次执行）：

-- 创建postgresql_fdw扩展
-- Foreign Data Wrapper: postgres_fdw
CREATE FOREIGN DATA WRAPPER postgres_fdw

-- 删除该扩展
-- DROP FOREIGN DATA WRAPPER postgres_fdw

-- 删除该扩展-- DROP FOREIGN DATA WRAPPER postgres_fdw

二. 创建远程服务器，这里(li)需要定义远程(cheng)主机ip、数据库库名、端口号（同一个远程数据库只需执行一次）:

-- 创建ods_server服务对接(jie)远程数据库的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;

-- 删除该服务 -- DROP SERVER ods_server;

三. 指定连接远程数据库的用户, 和创建的远程服务器一致（同一个(ge)远程(cheng)数据库只(zhi)需(xu)执行一次）：

-- 指定连接远程
-- User Mapping : xdh
CREATE USER MAPPING FOR ods_db SERVER ods_server
OPTIONS ("user" 'xha', password '123456');

-- 删除该指定用户
-- DROP USER MAPPING FOR creditrisk SERVER cscs

-- 删除该指(zhi)定用(yong)户-- DROP USER MAPPING FOR creditrisk SERVER cscs

四. 在接受数据的pg数据库创建外(wai)部表：

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');
-- 将该外表的权限按需要(yao)赋权给其它用户
GRANT ALL ON TABLE zha.student TO zha;
GRANT SELECT ON TABLE zha.student TO zhb;

Materialized View 物化视图

五. 可将外部表映射成pg的物化视图，以便进行数据查询等（同一张表(biao)只(zhi)需执行一次）：

-- Materialized View: gzk.student
-- DROP MATERIALIZED VIEW gzk.student;
CREATE MATERIALIZED VIEW gzk.student
AS
SELECT
id,
name,
age,
sex
from zha.student;

六. 刷新物化视图的数(shu)据：

-- 全量(liang)刷新(先清空表再重新映射数据,刷新(xin)时阻塞select! 较(jiao)快!)
refresh materialized view gzk.student;
-- 增量刷新(全表数据(ju)对比更新,刷新时不会阻塞select! 较慢!)
-- 只有(you)当物化视图中存在unique index(唯一索引)的时候，
-- refresh物化视图(tu)才能使用增量更新，加入concurrently参数。否则报错。
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.
-- 在(zai)物化视图上创建unique index(唯一索引,可(ke)以以(yi)自增主键id为唯一索引)
create unique index uidx_mv_id on gzk.student(id);
refresh materiallized view concurrently gzk.student;
-- 执行成功
-- 实际业务中可选增量刷新，定时执行refresh的(de)方式

七. 可以把物化视图刷新sql放到pgAgent job里, 定时刷新数据.

八. mysql_fdw、oracle_fdw、file_fdw用法类似,不做赘述。

文章来源：脚本之家

来源地址：https://www.jb51.net/article/204330.htm