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

数据封装器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扩展与授权

1	`CREATE` `EXTENSION postgres_fdw;`

如果需要授权

1	`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,注意结构要对应

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扩展（仅需第一次执行）：

									-- 创建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的方式