【PostgreSQL】PostgreSQL变更数据捕获（CDC）：完整指南

本指南帮助您开始将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，并降低操作复杂性。试试看！