PG-跨库操作-postgres_fdw

接上一篇《PG-跨库操作-dblink》；讲下postgres_fdw的使用；postgres_fdw工作原理详细介绍可以去看下《PostgreSQL指南》第4章；

对FDW特性；还支持在PostgreSQL异构数据库的同步、迁移的场景。FDW随着Postgres版本而升级、优化，对分布式架构也是支持的。

一、新建插件postgres_fdw

安装插件postgres_fdw

lottu01=# create extension postgres_fdw;

二、新建远程数据库服务器

2.1、在本地库创建SERVER

--1 赋予lottu01对应权限
lottu01=# grant usage on foreign data wrapper postgres_fdw to lottu01;
GRANT
lottu01=# \c lottu01 lottu01
You are now connected to database "lottu01" as user "lottu01".
--2 创建server
lottu01=> CREATE SERVER lottu FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.221', port '6000', dbname 'lottu');
CREATE SERVER

2.2、在本地库创建user mapper

lottu01=> CREATE USER MAPPING FOR lottu01 SERVER lottu OPTIONS (user 'lottu', password 'li0924');
CREATE USER MAPPING

2.3、创建外部表

lottu01=> CREATE FOREIGN TABLE t1 (id int, info text) server lottu OPTIONS (schema_name 'public', table_name 't1');
CREATE FOREIGN TABLE
lottu01=> select * from t1;
  id  | info  
------+-------
 1001 | lottu
 1002 | hello
 1003 | rax
 1004 | rax
 1005 | lottu
(5 rows)

2.4、导入外部表

CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，不过也可以为个别列附上column_name选项以表示它们在远程服务器上对应哪个列。在很多情况中，要手工构造外部表定义，使用IMPORT FOREIGN SCHEMA会更好。

lottu01=> create schema ft;
CREATE SCHEMA
lottu01=> IMPORT FOREIGN SCHEMA public FROM SERVER lottu INTO ft;
IMPORT FOREIGN SCHEMA

三、扩展

3.1、相关系统表

通过下列系统表可以查看数据库外部表信息。

| 系统表 | 简命令操作 | 含义 |
|:----😐:----😐:----😐:----😐:----|
| pg_extension | \dx | 插件 |
| pg_foreign_data_wrapper | \dew | 支持外部数据库接口 |
| pg_foreign_server | \des | 外部服务器 |
| pg_user_mappings | \deu | 用户管理 |
| pg_foreign_table | \det | 外部表 |

3.2、使用优化

减少对远端服务器的连接影响；可以使用物化视图;

--1、在本地数据库创建物化视图
create materialized view mv_t1 as select * from t1;
--2、 刷新一下本地的物化视图即可看到新进来的数据：
refresh materialized view mv_t1

四、应用场景

4.1、数据同步

本地创建外部表；可对外部表支持执行delete、update、insert语句；同时远程数据库的表也会执行相对应的操作。例如两个数据库实现token的一致。

4.2、数据迁移

在导入外部表；可以实现数据迁移。

4.3、分布式架构使用

citus的实现原理是采用FDW特性。安装很简单；但维护注意点比较多；想要更多了解可以找陈华军老师。