欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-13 15:00:23
数据封装器fdw(foreign data wrappers)在postgresql中相当于oracle中的dblink,可以很方便的操作其他数据库中的数据。场景,在本地的test库中通过外部数据封装...

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

场景,在本地的test库中通过外部数据封装器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创建一个外部服务器。

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创建外部表了。

在这个例子中我们希望访问远程服务器上名为 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等来操作别的数据库

补充: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扩展(仅需第一次执行):

-- 创建postgresql_fdw扩展
-- foreign data wrapper: postgres_fdw
create foreign data wrapper postgres_fdw
  
-- 删除该扩展
-- drop foreign data wrapper postgres_fdw

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

-- 创建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;

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

-- 指定连接远程
-- 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数据库创建外部表:

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的物化视图,以便进行数据查询等(同一张表只需执行一次):

-- materialized view: gzk.student
-- drop materialized view gzk.student;
create materialized view gzk.student
as
  select
    id,
    name,
    age,
    sex
  from zha.student;

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

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

-- 全量刷新(先清空表再重新映射数据,刷新时阻塞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用法类似,不做赘述。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。