POSTGRESQL 逻辑复制与CDC捕捉构建实时数据分析平台

大部分数据库都提供CDC 的功能, change data capture, DB的同学可能要问,为什么要这个功能, ORACLE 有DG 数据复制, SQL SERVER 有replication , MYSQL 有 binlog 复制, PG 逻辑,物理复制都有,CDC是不是多余的.

答案是NO , CDC 是跟踪数据库操作的一种方式,也是不少类似获取数据库变化时的一种方式, 更多的是对于某些数据获取后,可以进行一些后续性的触发性或判断性的操作.

更重要的是CDC 对于系统的更改会比较少,并且不会太大影响数据库的性能.

实际上也有其他的方式来进行数据同步,例如有些数据库的binlog 和 trigger 都可以对数据库的数据进行捕捉和记录.

这里选择的方法是POSTGRESQL 的逻辑复制+ 2nd 的audit-trigger的方式.

PostgreSQL 本身支持物理复制,同时也支持逻辑复制,这里通过逻辑复制的方式来讲表的DML 操作进行一个可视化的操作和可提取的操作.

首先需要对需要进行CDC 的POSTGRESQL 的配置进行相关的设置

1 需要打开POSTGRESQL 的逻辑复制


2 在修改后我们需要对服务器进行重新启动


针对要复制的表创建 publication

create publication cdc for table test1;

同时需要创建具有读取test1 表权限的账号,并且也具有replication 的权限,当然如果你想偷懒 SUPERUSER 可以作为测试时的一个选择.

然后需要在接受数据的"从库",创建和主库对应的库和表结构


CREATE SUBSCRIPTION cdc CONNECTION 'dbname=test host=192.168.198.100 user=admin password=1234.com port=5432' PUBLICATION cdc;


创建相关的subscription 在接收信息的数据库中.

可以看到数据已经从数据publication 到了 subscription


这里需要注意publication 和 subscription 的表的结构必须是一致的,否则会出现问题.

后面需要安装 2ndQuadrant audit 组件, 下载这个组件并将其安装在数据接收端.


在安装中可能会出现问题,下图是未安装 hstore,所以安装audit.sql出现问题


在执行完 \i /home/postgres/audit.sql 后


安装完毕后,直接在主库删除一条数据


那么在从库直接查看相关的audit.logged_actions 中是否有这条记录


基本上可以通过这样的数据记录配合其他程序来获取PG某张表的数据变动的情况,辅助自研的程序去捕捉这张表中的数据对需要同步数据到其他数据库,提供了一种方式.

实际上这里主要应用的是PG 的逻辑复制,逻辑复制相对于PG的物理复制要灵活,例如数据的聚合


实际上这个方式稍加利用就可以成为一个以PG数据库为基础的整体的数据汇聚和数据分发的平台.

首先通过PG 本身的逻辑复制,将需要在多个数据库系统(一般是不同业务系统的数据库)将这些需要数据分析的表进行逻辑复制到PG 汇聚的机器上,然后产生CDC的数据,通过程序二次开发的方式将这些数据任意存储到其他的数据库或大数据平台,这样的好处也是显而易见,由于现在ETL抽取数据时,大部分的ETL支持软件(实时数据)的软件并不是太多并且基本没有免费的,这样做基本上就可以支持一个业务实时数据分析的需求. 相对于其他数据库来说, 整体利用PG来搭建和完成这项工作要简单,省钱的多. 并且整体的架构也不是很复杂.