本指南帮助您开始将CDC与PostgreSQL数据库系统一起使用。
目录
- 介绍
- PostgreSQL中使用触发器的变更数据捕获
- PostgreSQL中使用查询进行变更数据捕获
- PostgreSQL中带有逻辑复制的变更数据捕获
- 总结
介绍
PostgreSQL是一个著名的开源数据库管理系统,目前正在众多企业生产。在典型的设置中,PostgreSQL管理应用程序的事务数据,例如电子商务商店中的产品,并集成第三方数据系统用于其他目的,例如用于分析的数据仓库、用于报告的BI工具等。
连接PostgreSQL与其他数据存储的传统方法是基于批处理的。数据管道时不时地从PostgreSQL中提取所有数据并发送到下游的数据存储中,这不仅效率低下,而且容易出错。
变更数据捕获(CDC)是一种现代的替代方案,可以从PostgreSQL中实时提取记录级变更事件(插入、更新和删除)。变更数据捕获的主要好处是:
- CDC实时捕获更改事件,保持下游系统(如数据仓库)始终与PostgreSQL同步,并启用完全事件驱动的数据体系结构。
- 使用CDC可以减少PostgreSQL的负载,因为只处理相关信息,例如更改。
- CDC支持高效地实现需要访问PostgreSQL更改事件(如审核或更改日志)的用例,而无需修改应用程序代码。
在本文中,我们将提供一个完整的介绍,介绍如何将变更数据捕获与PostgreSQL结合使用。我们介绍了实现更改数据捕获的三种常用方法:触发器、查询和逻辑复制。虽然每种方法都有各自的优缺点,但在DataCate,我们最喜欢的是使用逻辑复制的基于日志的CDC。
在PostgreSQL中使用触发器更改数据捕获
使用PostgreSQL的触发器特性,我们可以侦听感兴趣的表中发生的所有插入、更新和删除事件,并为每个事件将一行插入第二个表中,从而构建变更日志。
PostgreSQL社区提供了一个通用触发器函数(代码),该函数支持PostgreSQL 9.1版及更高版本,并将所有更改事件存储在表audit.logged\u actions中。如果希望为表public.users启用基于触发器的更改数据捕获,则可以运行以下SQL语句:
SELECT audit.audit_table('public.users');
为表public.users启用基于触发器的CDC。
CDC的这种方法只在PostgreSQL中存储捕获的事件。如果要将更改事件同步到其他数据系统(如数据仓库),则必须重复查询包含更改事件的PostgreSQL表(此处为audit.logged\u操作),这会增加实现的复杂性。
让我们比较一下在PostgreSQL中使用触发器实现更改数据捕获的优缺点:
- 可以立即捕获更改,从而实现更改事件的实时处理。
- 触发器可以捕获所有事件类型:插入、更新和删除。
- 默认情况下,此处使用的PostgreSQL触发器函数将有用的元数据添加到事件中,例如导致更改的语句、事务ID或会话用户名。
- 触发器会增加原始语句的执行时间,从而影响PostgreSQL的性能。
- 触发器需要更改PostgreSQL数据库。
- 如果更改事件应同步到同一PostgreSQL数据库以外的数据存储,则需要设置单独的数据管道,该管道轮询触发器函数(此处为audit.logged\u actions)填充的表。
- 创建和管理触发器会导致额外的操作复杂性。
在PostgreSQL中使用查询更改数据捕获
使用PostgreSQL实现更改数据捕获的第二种方法是基于查询的。
如果受监视数据库表的模式具有一个timestamp列,该列指示上一次更改行的时间,那么我们可以使用该列重复查询PostgreSQL,并请求自上次查询PostgreSQL以来已修改的所有记录。假设有一个名为public.users的表和一个名为updated\u at的时间戳列,这样的查询可以按如下方式实现:
SELECT * FROM public.users WHERE updated_at > 'TIMESTAMP_LAST_QUERY';
示例:提取自上次访问时间以来已修改的所有用户的SQL查询(请替换TIMESTAMP\u last\u查询)。
请注意,基于查询的CDC无法捕获删除(除非使用软删除),但仅限于插入和更新事件。
- 如果模式具有指示行修改时间的timestamp列,则可以实现基于查询的CDC,而无需对PostgreSQL进行任何更改。
- 基于查询的CDC实现使用查询层提取数据,这给PostgreSQL带来了额外的负载。
- 基于查询的CDC需要对受监视表(此处为public.users)进行周期性轮询,如果数据很少更改,这将浪费资源。
- 基于查询的CDC需要一个列(此处为updated\u at)来跟踪记录上次修改的时间。
- 基于查询的CDC无法捕获删除事件(除非应用程序使用软删除)。
在PostgreSQL中使用逻辑复制更改数据捕获
自9.4版以来,PostgreSQL提供了逻辑复制,以便在可能不同的物理机器上的不同PostgreSQL实例之间高效、安全地复制数据。从技术上讲,它是一个磁盘上的预写日志,它保存更改PostgreSQL数据库数据的所有事件,例如插入、更新和删除。
PostgreSQL使用具有发布服务器和订阅服务器的订阅模型来实现逻辑复制。为了实现变更数据捕获,我们可以使用感兴趣的数据库作为发布者并订阅其日志。
虽然许多数据库系统可能已经使用复制,但默认情况下不启用复制。您可以通过对配置文件postgresql.conf引入以下更改来启用逻辑复制。
wal_level = logical
在postgresql.conf中启用逻辑复制。
在下一步中,您需要修改配置文件pga\u hba.conf以允许复制(请参阅PostgreSQL文档了解各个配置):
host all repuser 0.0.0.0/0 md5
允许在pga\u hba.conf中进行逻辑复制。
假设您要从public.users表中捕获更改。您可以通过创建新发布为该表启用CDC,如下所示:
CREATE PUBLICATION newpub FOR TABLE public.users;
为表public.users创建发布。
在下一步中,您可以开始订阅此出版物。订阅从初始快照开始,然后复制所有增量更改。如果要使用另一个PostgreSQL实例中的事件,可以按如下方式创建订阅:
CREATE SUBSCRIPTION newsub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION newpub;
为使用更改创建订阅。
从技术上讲,逻辑复制是通过逻辑解码插件实现的。如果您使用的PostgreSQL版本早于10,则需要在PostgreSQL数据库中手动安装插件,例如wal2json或decoderbufs。从版本10开始,PostgreSQL默认提供插件pgoutput。
对于基于日志的变更数据捕获的技术实现,我们强烈建议使用现有的开源项目之一,例如Debezium。DataCate的PostgreSQL源连接器基于Debezium。
据我们所知,大多数托管PostgreSQL服务都提供对逻辑复制的支持,例如AWS RDS、Google Cloud SQL或Azure数据库。
下表显示了使用PostgreSQL的逻辑复制实现CDC的优缺点:
- 基于日志的CDC支持实时捕获事件驱动的数据更改。下游应用程序始终可以访问来自PostgreSQL的最新数据。
- 基于日志的CDC可以检测PostgreSQL中的所有更改事件类型:插入、更新和删除。
- 通过逻辑复制使用事件归结为直接访问文件系统,这不会影响PostgreSQL数据库的性能。
- 非常旧版本的PostgreSQL(早于9.4)不支持逻辑复制。
总结
在比较使用PostgreSQL实现变更数据捕获的三种方法时,使用逻辑复制显然是胜利者。它不仅高效、实时捕获所有事件类型而不损害PostgreSQL数据库的性能,而且广泛可用(无论您使用的是自管理还是托管PostgreSQL安装),并且在不更改数据库模式的情况下适用。
CDC连接器通常比传统的SELECT*FROM表更复杂;查询。在DataCate中,我们提供用于更改数据捕获的即插即用连接器,它使您能够在几分钟内使用PostgreSQL设置基于日志的CDC,并降低操作复杂性。试试看!
Tags
最新内容
- 4 days 6 hours ago
- 4 days 6 hours ago
- 4 days 6 hours ago
- 4 days 6 hours ago
- 5 days 11 hours ago
- 1 week 6 days ago
- 1 week 6 days ago
- 1 week 6 days ago
- 2 weeks 2 days ago
- 2 weeks 2 days ago