您现在的位置是:首页 > 博客网站制作 > 心得笔记心得笔记

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

杨青青2022-02-20【心得笔记】人已围观

简介数据封装器fdw(ForeignDataWrappers)在PostgreSQL中相当于oracle中的dblink,可以很方便的操作其他数据库中的数据。场景,在本地的test库中通过外部数据封装器fdw访问本地的testd

数据封装器fdw(ForeignDataWrappers)在PostgreSQL中相当于oracle中的dblink,可以很方便的操作其他数据库中的数据。

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

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

版本:

postgres=#selectversion();
version
------------------------------------------------------------
PostgreSQL11.1,compiledbyVisualC++build1914,64-bit
(1行记录)

1,安装postgres_fdw扩展与授权

1CREATEEXTENSIONpostgres_fdw;

如果需要授权

1grantusageonforeigndatawrapperpostgres_fdwtou1;

2,然后使用CREATESERVER创建一个外部服务器。

CREATESERVERforeign_server
FOREIGNDATAWRAPPERpostgres_fdw
OPTIONS(host'127.0.0.1',port'5432',dbname'testdb');

3,用CREATEUSERMAPPING定义的用户映射来标识将在远程服务器上使用的角色:

CREATEUSERMAPPINGFORpublic
SERVERforeign_server
OPTIONS(user'dbuser',password'123456');

4,用CREATEFOREIGNTABLE创建外部表了。

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

CREATEFOREIGNTABLEforeign_table_t2(idint,namevarchar(10))
SERVERforeign_serveroptions(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是foreigndatawrapper的一个简称,叫做外部封装数据。

用命令行登陆postgresql:

plsql-hip地址-p端口号-U用户名-d库名

1、安装fdw

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

2、创建库连接

postgresql_fdw

一.创建postgres_fdw扩展(仅需第一次执行):

--创建postgresql_fdw扩展
--ForeignDataWrapper:postgres_fdw
CREATEFOREIGNDATAWRAPPERpostgres_fdw

--删除该扩展
--DROPFOREIGNDATAWRAPPERpostgres_fdw

--删除该扩展--DROPFOREIGNDATAWRAPPERpostgres_fdw

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

--创建ods_server服务对接远程数据库的ods_db库
--ForeignServer:ods_server
CREATESERVERods_server
FOREIGNDATAWRAPPERpostgres_fdw
OPTIONS(host'68.26.1.1',dbname'ods_db',port'5432');

--删除该服务
--DROPSERVERods_server;

--删除该服务--DROPSERVERods_server;

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

--指定连接远程
--UserMapping:xdh
CREATEUSERMAPPINGFORods_dbSERVERods_server
OPTIONS("user"'xha',password'123456');

--删除该指定用户
--DROPUSERMAPPINGFORcreditriskSERVERcscs

--删除该指定用户--DROPUSERMAPPINGFORcreditriskSERVERcscs

四.在接受数据的pg数据库创建外部表:

CREATEFOREIGNTABLEzha.student(
idinteger,
namevarchar(50),
ageinteger,
sexvarchar(20)
)
SERVERods_server
OPTIONS(schema_name'ods',table_name'student_ods');
--将该外表的权限按需要赋权给其它用户
GRANTALLONTABLEzha.studentTOzha;
GRANTSELECTONTABLEzha.studentTOzhb;

MaterializedView物化视图

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

--MaterializedView:gzk.student
--DROPMATERIALIZEDVIEWgzk.student;
CREATEMATERIALIZEDVIEWgzk.student
AS
SELECT
id,
name,
age,
sex
fromzha.student;

六.刷新物化视图的数据:

普通视图的数据只是一个select,可以随原表数据的变化而变化,但物化视图类似于一个真正的表,可以创建索引,数据不会随着原表的变化而变化,需要手动刷新数据。

--全量刷新(先清空表再重新映射数据,刷新时阻塞select!较快!)
refreshmaterializedviewgzk.student;
--增量刷新(全表数据对比更新,刷新时不会阻塞select!较慢!)
--只有当物化视图中存在uniqueindex(唯一索引)的时候,
--refresh物化视图才能使用增量更新,加入concurrently参数。否则报错。
refreshmateriallizedviewconcurrentlygzk.student;
--报错
--ERROR:cannotrefreshmaterializedview"gzk.student"concurrently
--HINT:CreateauniqueindexwithnoWHEREclauseonone
--ormorecolumnsofthematerializedview.
--在物化视图上创建uniqueindex(唯一索引,可以以自增主键id为唯一索引)
createuniqueindexuidx_mv_idongzk.student(id);
refreshmateriallizedviewconcurrentlygzk.student;
--执行成功
--实际业务中可选增量刷新,定时执行refresh的方式

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

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

Tags:

很赞哦! ()

随机图文

文章评论

站点信息

  • 建站时间:2011年01月12日
  • 网站程序:帝国CMS7.5
  • 主题模板《今夕何夕》
  • 文章统计625篇文章
  • 标签管理标签云
  • 微信公众号:扫描二维码,关注我们