PostgreSQL中使用FDW+MV玩转数据同步

Qunar技术沙龙 2021-09-24

2413

作者介绍

齐健，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=# \! hostname
remotehost
 
--外部数据
school_info=# select * from instructor;
 id |    name    | dept_name | salary
----+------------+-----------+--------
  1 | einstein   | physics   |  95000
  2 | wu         | finance   |  90000
  3 | elsaid     | history   |  60000
  4 | katz       | comp.sci. |  75000
  5 | kim        | elec.eng. |  80000
  6 | crick      | biology   |  72000
  7 | srinivasan | comp.sci. |  65000
  8 | califieri  | history   |  62000
  9 | brandt     | comp.sci. |  92000
 10 | mozart     | music     |  40000
 11 | gold       | physics   |  87000
 12 | singh      | finance   |  80000
(12 rows)
 
school_info_backup=# \! hostname
localhost
 
--定义一个新的外部服务器
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   |  95000
  2 | wu         | finance   |  90000
  3 | elsaid     | history   |  60000
  4 | katz       | comp.sci. |  75000
  5 | kim        | elec.eng. |  80000
  6 | crick      | biology   |  72000
  7 | srinivasan | comp.sci. |  65000
  8 | califieri  | history   |  62000
  9 | brandt     | comp.sci. |  92000
 10 | mozart     | music     |  40000
 11 | gold       | physics   |  87000
 12 | singh      | finance   |  80000
(12 rows)

可见，有了 FDW ，可以实现跨 DB ，或跨 DB 集群，甚至跨DBMS（比如Qunar的广告系统通过 Oracle_fdw 实现 PostgreSQL 访问 Oracle 中的数据）的数据同步。

在 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 ;

不指定 CONCURRENTLY 选项时，使用更少的资源并且完成地更迅速，但是会锁定其他应用从该物化视图读数据的连接。

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   | 100000
 biology   | watson   |  90000
 elec.eng. | taylor   |  85000
 music     | packard  |  80000
 finance   | painter  | 120000
(5 rows)
 
--定义一个物化视图
school_info=# create materialized view department_mv as select * from department ;
SELECT 5
 
school_info=# insert into department values ('history','painter',50000);
INSERT 0 1
 
school_info=# select * from department_mv ;
 dept_name | building | budget
-----------+----------+--------
 comp.sci. | taylor   | 100000
 biology   | watson   |  90000
 elec.eng. | taylor   |  85000
 music     | packard  |  80000
 finance   | painter  | 120000
(5 rows)
 
--刷新物化视图
school_info=# refresh materialized view department_mv;
REFRESH MATERIALIZED VIEW
 
school_info=# select * from department_mv;
 dept_name | building | budget
-----------+----------+--------
 comp.sci. | taylor   | 100000
 biology   | watson   |  90000
 elec.eng. | taylor   |  85000
 music     | packard  |  80000
 finance   | painter  | 120000
 history   | painter  |  50000
(6 rows)
 
school_info=# insert into department values ('physics','watson',70000);
INSERT 0 1
 
school_info=# select * from department_mv ;
 dept_name | building | budget
-----------+----------+--------
 comp.sci. | taylor   | 100000
 biology   | watson   |  90000
 elec.eng. | taylor   |  85000
 music     | packard  |  80000
 finance   | painter  | 120000
 history   | 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   | 100000
 biology   | watson   |  90000
 elec.eng. | taylor   |  85000
 music     | packard  |  80000
 finance   | painter  | 120000
 history   | painter  |  50000
 physics   | watson   |  70000
(7 rows)

四、FDW+MV

如何运用 FDW 与 Materialized View 二者的组合拳来解决前述场景的数据同步问题呢？

数据同步方案的架构图如下：

在该方案中，通过外部数据包装器postgres_fdw访问外部数据，左侧是一个外部数据源，包含一张待同步的表（instructor），右侧是在本地创建的一张外部表（instructor_fdw）。

方案具体示例如下：

school_info_backup=# \dew+
                                              List of foreign-data wrappers
     Name     |  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 servers
  Name  |  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 mappings
 Server | User name |                            FDW options                            
--------+-----------+--------------------------------------------------------------------
 school | local_dba | ("user" 'remote_dba', password 'xxxxxx')
(1 row)
 
school_info_backup=# \det+
                            List of foreign tables
 Schema |     Table      | Server |        FDW options        | Description
--------+----------------+--------+---------------------------+-------------
 public | instructor_fdw | school | (table_name 'instructor') |
(1 row)
 
school_info_backup=# \dm+
                             List of relations
 Schema |     Name      |       Type        |  Owner   | Size  | Description
--------+---------------+-------------------+----------+-------+-------------
 public | instructor_mv | materialized view | local_dba| 32 kB |
(1 row)

可见，在外部表 instructor_fdw 上定义一个 MV ： instructor_mv ，即将符合业务需求的数据以 MV 的形式周期性持久化在本地库中，至此数据同步的目标已实现。

此后日常的数据同步将变得很简单，只需要定时 refresh MV 即可。

五、小结

现简要对比总结一下各种数据同步的方案的适用主要场景：

方案	适用场景
pg_dump&&reload	同步数据量较小，dump && reload一次至目标DB所耗时间和资源成本低目标DB端的应用可以接受一定的数据时延 query五花八门，且参数不固定，需要源DB全量数据，甚至需要和其他本地表join，且对速度有一定要求
FDW	源DB数据量巨大，dump && reload一次至目标DB太耗时且耗资源目标DB需要跨DB获取实时数据，即使数据量不是特别大，但必须每次都使用FDW获取最新数据，如订单detail页相关的查询目标DB端的应用的query的速度不需要像本地一样快，且参数不固定，可能涉及到全量数据
FDW+MV	源DB数据量巨大，dump && reload一次至目标DB太耗时且耗资源目标DB端的应用可以接受一定的数据时延，如数据仓库类的应用，同步T-1的数据目标端需要通过FDW运算数据，甚至是通过FDW和本地表join后才可以得到特定的聚合数据目标端应用对query速度有一定要求，需要将结果数据持久化至本地

通过运用 PostgreSQL 中独有的 FDW 与 MV ，为特定场景下的数据同步提供了一种全新的方案， FDW+MV 二者灵活组合运用，效果更佳！

实例

Qunar有很多运用FDW+MV组合拳进行数据同步的实例，下面看一个某业务线订单相关数据同步到产品实例的实践案例。



--源DB
order_product=# \! hostname
remotehost
order_product=# \dt+ order_detail
                       List of relations
 Schema |     Name     | Type  |  Owner   | Size  | Description
--------+--------------+-------+----------+-------+-------------
 public | order_detail | table | postgres | 20 GB |
(1 row)
 
 
 
--目标DB
b2c_product=# \des
            List of foreign servers
        Name         | Owner | Foreign-data wrapper
---------------------+-------+----------------------
 remote_order_server | pgdba | postgres_fdw
(1 row)
 
b2c_product=# \deu
    List of user mappings
      Server         | User name
---------------------+-----------
 remote_order_server | pgdba
(1 row)
 
b2c_product=# \det+ order_detail
                                             List of foreign tables
 Schema |    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_report
                                 List of relations
 Schema |         Name         |       Type        | Owner |  Size   | Description
--------+----------------------+-------------------+-------+---------+-------------
 public | order_product_report | materialized view | pgdba | 0 bytes | 产品当年销量物化视图
(1 row)
 
b2c_product=# \d+ order_product_report
                       Materialized 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 count
   FROM order_detail
  WHERE order_detail.create_time >= '2021-01-01 00:00:00+08'::timestamp with time zone
  GROUP BY order_detail.product_id;
 
b2c_product=# refresh materialized view order_product_report;
REFRESH MATERIALIZED VIEW
Time: 542.331 ms
 
--refresh materialized view CONCURRENTLY 必须有unique index
b2c_product=# refresh materialized view CONCURRENTLY order_product_report;
ERROR:  cannot refresh materialized view "public.order_product_report" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
Time: 0.375 ms
 
b2c_product=# create unique index CONCURRENTLY ON order_product_report(product_id);
CREATE INDEX
Time: 25.973 ms
 
b2c_product=# refresh materialized view CONCURRENTLY order_product_report;
REFRESH MATERIALIZED VIEW
Time: 681.510 ms
 
b2c_product=# select count(1) from order_product_report;
 count
-------
666888
(1 row)
 
Time: 4.488 ms
 
--查询当年销量大于100单的产品
b2c_product=# select title 
from product p
join order_product_report o
  on  p.id = o.product_id
where 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

招募贴：

END

postgresql

文章转载自Qunar技术沙龙，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL中使用FDW+MV玩转数据同步

五、小结

评论