PostgreSQL中使用FDW+MV玩转数据同步
作者介绍
齐健，2019年7月校招加入Qunar，现负责公司PostgreSQL运维工作，对数据库日常运维和调优有一定实践经验。
一、问题的提出
经常有这样的 case ，业务线开发同学需要 DBA 帮助其在不同的 PostgreSQL 数据库或集群之间同步数据。
数据量较小时，使用 pg_dump 工具或 copy 命令即可完成同步需求。但当数据量较大时，直接同步数据容易引发源 DB 或目标 DB 出现 slow query 或主从同步延迟等问题，容易对数据库的性能及稳定性造成不良影响。
而且无论数据量大小与否，有时候手工或写定时任务同步数据都很容易出错，且目标DB的数据如果恰好有人在使用极易与同步数据的任务相互阻塞。
是否存在其他数据同步方案避免上述问题呢？通过调研加实践，PostgreSQL中运用FDW与Materialized View可以较好的优化上述问题。
二、FDW
2003年，SQL/MED（SQL Management of External Data）作为新规范被添加到 SQL 标准中，它是处理从 SQL 数据库访问远程对象的标准化方法。
PostgreSQL实现了部分的SQL/MED规范，2011年 PostgreSQL 9.1提供了对该规范的只读支持，2013年 PostgreSQL 9.3 增加了对写的支持。SQL/MED 规范的实现，允许我们使用普通 SQL 查询来访问位于 PostgreSQL 之外的数据。
PostgreSQL 通过 FDW（Foreign Data Wrapper)，即外部数据包装器来访问外部数据。FDW 是一个可以与外部数据源通信的库，它隐藏了连接到数据源并从中获取数据的细节。
PostgreSQL 官方提供的 FDW 有 postgres_fdw 和 file_fdw ，其他类型的 FDW 由第三方扩展实现。通过不同的 FDW ， PostgreSQL 能够访问不同的外部同构/异构数据源，除了 PostgreSQL、Oracle、MySQL 等关系型数据库，还支持访问 MongoDB、Redis、Hive/Hadoop/HDFS、文件等远程数据存储。
无论使用什么类型的FDW，外部数据表只能从外部数据源获取数据，或者在更新命令的情况下传送数据到外部数据源。
在 FDW 出现之前，dblink 也可以实现远端数据库的访问，但 FDW 是一种更现代和更加兼容标准的架构，提供了更为强大的功能。
比如 dblink 无法使用源 DB 表上的 index ，而 FDW 会区分 where 或 join 等限制条件，将能够发送到 remote 端的限制条件发送到 remote 端执行，此时就只需从 remote 端仅取回需要的数据。
PostgreSQL 9.5 支持使用 IMPORT FOREIGN SCHEMA 命令导入整个 schema，PostgreSQL 9.6 已经支持 join 条件的 remote 端下推， PostgreSQL 10 已支持聚合函数的下推。PostgreSQL 14 还增加了使用 FDW 查询 remote Database时的并行执行功能，postgres_fdw 还支持批量插入，使用 IMPORT FOREIGN SCHEMA 命令导入分区表，以及外部表上的 TRUNCATE 命令。
可见 PostgreSQL 对 FDW 的支持越来越完善，功能越来越强大。所以还在使用 PostgreSQL 9.x 的用户，升级至 PostgreSQL 11+，是十分有必要且明智的选择！
FDW同步数据示例：
school_info=# \! hostnameremotehost--外部数据school_info=# select * from instructor;id | name | dept_name | salary----+------------+-----------+--------1 | einstein | physics | 950002 | wu | finance | 900003 | elsaid | history | 600004 | katz | comp.sci. | 750005 | kim | elec.eng. | 800006 | crick | biology | 720007 | srinivasan | comp.sci. | 650008 | califieri | history | 620009 | brandt | comp.sci. | 9200010 | mozart | music | 4000011 | gold | physics | 8700012 | singh | finance | 80000(12 rows)school_info_backup=# \! hostnamelocalhost--定义一个新的外部服务器school_info_backup=# create server school foreign data wrapper postgres_fdw options(host 'remotehost',port '5432',dbname 'school_info');CREATE SERVER--定义一个用户到一个外部服务器的新映射school_info_backup=# create user mapping for local_dba server school options(user 'remote_dba',password 'xxxxxx');CREATE USER MAPPING--定义一个新的外部表school_info_backup=# create foreign table instructor_fdw(id int,name varchar(20),dept_name varchar(20),salary int)server school options(table_name 'instructor');CREATE FOREIGN TABLE--从外部表获取数据，用法和本地表没有区别school_info_backup=# select * from instructor_fdw;id | name | dept_name | salary----+------------+-----------+--------1 | einstein | physics | 950002 | wu | finance | 900003 | elsaid | history | 600004 | katz | comp.sci. | 750005 | kim | elec.eng. | 800006 | crick | biology | 720007 | srinivasan | comp.sci. | 650008 | califieri | history | 620009 | brandt | comp.sci. | 9200010 | mozart | music | 4000011 | gold | physics | 8700012 | singh | finance | 80000(12 rows)
在 PostgreSQL 服务器中外部表不存储数据，只是指向外部数据源的一个链接，数据依旧存储在外部数据源中。在本地库通过访问这张外部表，就可以访问外部数据源中相对应的待同步的表。
使用 FDW 同步数据时，真实的数据依然在源 DB ，主要的同步数据应用场景：
目标 DB 需要跨 DB 获取实时数据，必须每次都使用FDW获取最新数据
目标 DB 端的应用的 query 的速度不需要像运行在本地表上一样快，且参数不固定，可能涉及到全量原始数据
但是有些不需要获取源DB中实时数据的数据同步场景，尤其统计/报表等数据仓库类的应用，基本是同步T-1的数据即可满足需求，需要某一维度(比如按日期或按用户)的聚合后的数据，且查询的query 频率虽然较高，但是参数和结果在一定时间内相对固定，有没有更优的方案呢？
经过调研，答案是肯定的！
接下来我们就来了解一下开篇提到的 Materialized View 。
三、MV
View（视图）是表示数据库查询结果的虚拟表。当定义一个视图的时候，数据库只存储定义该视图的查询语句，每当查询涉及该视图时，数据库就会将其转换为已存储的查询表达式。因此无论我们何时执行这个查询，视图关系都被重新计算。
MV（Materialized View），即物化视图，是一个其内容已计算并存储的视图。MV将查询结果缓存为一张具体的表，可以不时地从定义视图的实际关系中更新。这使得访问更加有效，但代价是额外的存储和一些可能过期的数据。2013年 PostgreSQL 9.3 提供了对物化视图的支持。
MV 带来一个问题，就是它们必须能够在视图定义所使用的数据变化时保持更新。这种保持MV与原始数据同步更新的任务称作视图维护（ View Maintenance ）。不同的数据库系统采取不同的视图维护策略，PostgreSQL 中使用 REFRESH MATERIALIZED VIEW语句更新MV。
REFRESH MATERIALIZED VIEW table_name ;REFRESH MATERIALIZED VIEW CONCURRENTLY table_name ;使用 PostgreSQL 9.4 新支持的 CONCURRENTLY 选项可以在 refresh MV 时不阻塞对该物化视图的查询 query ，虽然 refresh 的速度会变的稍慢，但是这种以时间来换取查询锁还是值得的。
物化视图使用示例：
school_info=# select * from department;dept_name | building | budget-----------+----------+--------comp.sci. | taylor | 100000biology | watson | 90000elec.eng. | taylor | 85000music | packard | 80000finance | painter | 120000(5 rows)--定义一个物化视图school_info=# create materialized view department_mv as select * from department ;SELECT 5school_info=# insert into department values ('history','painter',50000);INSERT 0 1school_info=# select * from department_mv ;dept_name | building | budget-----------+----------+--------comp.sci. | taylor | 100000biology | watson | 90000elec.eng. | taylor | 85000music | packard | 80000finance | painter | 120000(5 rows)--刷新物化视图school_info=# refresh materialized view department_mv;REFRESH MATERIALIZED VIEWschool_info=# select * from department_mv;dept_name | building | budget-----------+----------+--------comp.sci. | taylor | 100000biology | watson | 90000elec.eng. | taylor | 85000music | packard | 80000finance | painter | 120000history | painter | 50000(6 rows)school_info=# insert into department values ('physics','watson',70000);INSERT 0 1school_info=# select * from department_mv ;dept_name | building | budget-----------+----------+--------comp.sci. | taylor | 100000biology | watson | 90000elec.eng. | taylor | 85000music | packard | 80000finance | painter | 120000history | painter | 50000(6 rows)--并行刷新物化视图school_info=# refresh materialized view concurrently department_mv;REFRESH MATERIALIZED VIEW--在PostgreSQL 14中，REFRESH MATERIALIZED VIEW 命令也可以使用并行查询school_info=# select * from department_mv ;dept_name | building | budget-----------+----------+--------comp.sci. | taylor | 100000biology | watson | 90000elec.eng. | taylor | 85000music | packard | 80000finance | painter | 120000history | painter | 50000physics | watson | 70000(7 rows)
如何运用 FDW 与 Materialized View 二者的组合拳来解决前述场景的数据同步问题呢？
数据同步方案的架构图如下：
在该方案中，通过外部数据包装器postgres_fdw访问外部数据，左侧是一个外部数据源，包含一张待同步的表（instructor），右侧是在本地创建的一张外部表（instructor_fdw）。
方案具体示例如下：
school_info_backup=# \dew+List of foreign-data wrappersName | Owner | Handler | Validator | Access privileges | FDW options | Description--------------+----------+----------------------+------------------------+-------------------+-------------+-------------postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator | | |(1 row)school_info_backup=# \des+List of foreign serversName | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description--------+----------+----------------------+---------------------+------+---------+----------------------------------------------------------------------------+-------------school | postgres | postgres_fdw | postgres=U/postgres+| | | (host 'remotehost', port '5432', dbname 'school_info') || | | local_dba=U/postgres| | | |(1 row)school_info_backup=# \deu+List of user mappingsServer | User name | FDW options--------+-----------+--------------------------------------------------------------------school | local_dba | ("user" 'remote_dba', password 'xxxxxx')(1 row)school_info_backup=# \det+List of foreign tablesSchema | Table | Server | FDW options | Description--------+----------------+--------+---------------------------+-------------public | instructor_fdw | school | (table_name 'instructor') |(1 row)school_info_backup=# \dm+List of relationsSchema | Name | Type | Owner | Size | Description--------+---------------+-------------------+----------+-------+-------------public | instructor_mv | materialized view | local_dba| 32 kB |(1 row)
此后日常的数据同步将变得很简单，只需要定时 refresh MV 即可。
五、小结
现简要对比总结一下各种数据同步的方案的适用主要场景：
| 方案 | 适用场景 |
| pg_dump&&reload |
|
| FDW |
|
| FDW+MV |
|
实例
--源DBorder_product=# \! hostnameremotehostorder_product=# \dt+ order_detailList of relationsSchema | Name | Type | Owner | Size | Description--------+--------------+-------+----------+-------+-------------public | order_detail | table | postgres | 20 GB |(1 row)--目标DBb2c_product=# \desList of foreign serversName | Owner | Foreign-data wrapper---------------------+-------+----------------------remote_order_server | pgdba | postgres_fdw(1 row)b2c_product=# \deuList of user mappingsServer | User name---------------------+-----------remote_order_server | pgdba(1 row)b2c_product=# \det+ order_detailList of foreign tablesSchema | Table | Server | FDW options | Description--------+--------------+---------------------+---------------------------------------------------+-------------------public | order_detail | remote_order_server | (schema_name 'public', table_name 'order_detail') | 远程订单库订单明细外部表(1 row)b2c_product=# \dm+ order_product_reportList of relationsSchema | Name | Type | Owner | Size | Description--------+----------------------+-------------------+-------+---------+-------------public | order_product_report | materialized view | pgdba | 0 bytes | 产品当年销量物化视图(1 row)b2c_product=# \d+ order_product_reportMaterialized view "public.order_product_report"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description------------+---------+-----------+----------+---------+---------+--------------+-------------product_id | integer | | | | plain | |count | bigint | | | | plain | |View definition:SELECT order_detail.product_id,count(1) AS countFROM order_detailWHERE order_detail.create_time >= '2021-01-01 00:00:00+08'::timestamp with time zoneGROUP BY order_detail.product_id;b2c_product=# refresh materialized view order_product_report;REFRESH MATERIALIZED VIEWTime: 542.331 ms--refresh materialized view CONCURRENTLY 必须有unique indexb2c_product=# refresh materialized view CONCURRENTLY order_product_report;ERROR: cannot refresh materialized view "public.order_product_report" concurrentlyHINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.Time: 0.375 msb2c_product=# create unique index CONCURRENTLY ON order_product_report(product_id);CREATE INDEXTime: 25.973 msb2c_product=# refresh materialized view CONCURRENTLY order_product_report;REFRESH MATERIALIZED VIEWTime: 681.510 msb2c_product=# select count(1) from order_product_report;count-------666888(1 row)Time: 4.488 ms--查询当年销量大于100单的产品b2c_product=# select titlefrom product pjoin order_product_report oon p.id = o.product_idwhere count >100 and p.title ~ '北京'order by random()limit 1;title---------------------------------------北京八达岭长城+颐和园+鸟巢纯玩品质跟团一日游(1 row)Time: 11.478 ms
可见，源 DB 中 20 GB 的表，通过 FDW +MV ，完美的将所需的外部远程实例订单库中的数据正确且高效的同步至产品库中。
与传统的数据同步方案相比，在性能和可维护性等方面具有非常强大的优势！
参考：
《数据库系统概念》 (美)Abraham Silberschatz / (美)Henry F.Korth / (美)S.Sudarshan
https://www.postgresql.org/docs/current/postgres-fdw.html
https://www.postgresql.org/docs/current/dblink.html
https://wiki.postgresql.org/wiki/Main_Page
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
前往“发现”-“看一看”浏览“朋友在看”