数据 封装器 fdw （Foreign Data Wrappers）在 PostgreSQL 中相当于oracle中的dblink，可以很方便的操作其他数据库中的数据。
场景，在本地的test库中通过外部数据封装器fdw访问本地的testdb中的t2表
本地库test用户u1，远程库test用户dbuser
版本：
|
1 2 3 4 5 |
postgres=# select version(); version ------------------------------------------------------------ PostgreSQL 11.1, compiled by Visual C++ build 1914, 64- bit (1 行记录) |
1,安装postgres_fdw扩展与授权
|
1 |
CREATE EXTENSION postgres_fdw; |
如果需要授权
|
1 |
grant usage on foreign data wrapper postgres_fdw to u1; |
2，然后使用CREATE SERVER创建一个外部服务器。
|
1 2 3 |
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1' , port '5432' , dbname 'testdb' ); |
3，用CREATE USER MAPPING 定义的用户映射来标识将在远程服务器上使用的角色：
|
1 2 3 |
CREATE USER MAPPING FOR public SERVER foreign_server OPTIONS ( user 'dbuser' , password '123456' ); |
4，用CREATE FOREIGN TABLE创建外部表了。
在这个例子中我们希望访问远程服务器上名为 some_schema.some_table的表。它的本地名称是 foreign_table,注意结构要对应
|
1 2 |
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等来操作别的数据库
补充：PostgreSQL数据库插件fdw使用心得—mysql_fdw、oracle_fdw、postgresql_fdw
fdw是foreign data wrapper的一个简称，叫做外部封装数据。
用命令行登陆postgresql:
plsql -h ip地址 -p 端口号 -U 用户名 -d 库名
1、安装fdw
fdw工具是pg自带扩展工具，pg10版本之后不需要再单独安装，一般分为postgresql_fdw、mysql_fdw、file_fdw、oracle_fdw等。可以将不同的异构数据源当成pg的外表。
2、创建库连接
postgresql_fdw
一. 创建postgres_fdw扩展（仅需第一次执行）：
|
1 2 3 4 5 6 |
-- 创建postgresql_fdw扩展 -- Foreign Data Wrapper: postgres_fdw CREATE FOREIGN DATA WRAPPER postgres_fdw
-- 删除该扩展 -- DROP FOREIGN DATA WRAPPER postgres_fdw |
二. 创建远程服务器，这里需要定义远程主机ip、数据库库名、端口号（同一个远程数据库只需执行一次）:
|
1 2 3 4 5 6 7 8 |
-- 创建ods_server服务对接远程数据库的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; |
三. 指定连接远程数据库的用户, 和创建的远程服务器一致（同一个远程数据库只需执行一次）：
|
1 2 3 4 5 6 7 |
-- 指定连接远程 -- User Mapping : xdh CREATE USER MAPPING FOR ods_db SERVER ods_server OPTIONS ( "user" 'xha' , password '123456' );
-- 删除该指定用户 -- DROP USER MAPPING FOR creditrisk SERVER cscs |
四. 在接受数据的pg数据库创建外部表：
|
1 2 3 4 5 6 7 8 9 10 11 |
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的物化视图，以便进行数据查询等（同一张表只需执行一次）：
|
1 2 3 4 5 6 7 8 9 10 |
-- Materialized View: gzk.student -- DROP MATERIALIZED VIEW gzk.student; CREATE MATERIALIZED VIEW gzk.student AS SELECT id, name , age, sex from zha.student; |
六. 刷新物化视图的数据：
普通视图的数据只是一个select，可以随原表数据的变化而变化，但物化视图类似于一个真正的表，可以创建索引，数据不会随着原表的变化而变化，需要手动刷新数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 全量刷新(先清空表再重新映射数据,刷新时阻塞select! 较快!) refresh materialized view gzk.student; -- 增量刷新(全表数据对比更新,刷新时不会阻塞select! 较慢!) -- 只有当物化视图中存在unique index(唯一索引)的时候， -- refresh物化视图才能使用增量更新，加入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. -- 在物化视图上创建unique index(唯一索引,可以以自增主键id为唯一索引) create unique index uidx_mv_id on gzk.student(id); refresh materiallized view concurrently gzk.student; -- 执行成功 -- 实际业务中可选增量刷新，定时执行refresh的方式 |
七. 可以把物化视图刷新sql放到pgAgent job里, 定时刷新数据.
八. mysql_fdw、oracle_fdw、file_fdw用法类似,不做赘述。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/jc_benben/article/details/84317849
查看更多关于PostgreSQL的外部数据封装器fdw用法的详细内容...