PostgreSQL是使用最广泛的开源关系数据库之一。其全面的查询层和符合技术标准的特性使其成为OLTP工作负载，数据仓库和分析用例等各种用例的最爱。在典型的体系结构中，组织将有一个关系数据库来处理事务负载，然后有一个单独的数据仓库来执行所有分析用例和汇总报告。这里的挑战是确保数据仓库具有事务数据库中存在的最新数据版本。通常有时间紧迫的报告要求，无法在这些数据库之间进行每小时或每天的批处理同步。使用基于变更数据捕获范例的实例之间的连续同步来处理此类需求。这篇文章是关于PostgreSQL CDC及其实现方法的。

在本文中，您将学习：

• 实现PostgreSQL CDC的方法
• 方法1 –使用自定义代码的Postgres CDC
• 方法1 –使用审核触发器
• 方法2：使用Postgres逻辑解码
• 方法3：使用时间戳列
• 方法2 –实施PostgreSQL CDC的简便方法

实施PostgreSQL变化数据捕获（CDC）的方法
有很多实现Postgres CDC的方法。 该博客讨论了以下两种方法：

方法1：构建自定义代码以实现PostgreSQL CDC
我们将在此博客中讨论以下方法：

• 使用Postgres审核触发器
• 使用Postgres逻辑解码
• 使用时间戳列

方法2：使用完全自动化的数据管道解决方案，如Hevo Data
尽管构建自定义代码可能看起来是非常有利可图的选择，但仍有许多障碍，挑战和限制使其变得很费力。 实施类似Hevo的解决方案有助于实现PostgreSQL CDC到您选择的任何目标，而无需编写任何代码。

Postgres使用自定义代码更改数据捕获

方法1：使用Postgres审核触发器(Audit Triggers)

基于触发器的方法涉及在数据库上创建审核触发器，以捕获与INSERT，UPDATE和DELETE方法有关的所有事件。这种方法的优点是，一切都发生在SQL级别，并且开发人员只需要读取一个包含所有审核日志的单独表。

要在Postgres表中创建触发器，请执行以下命令。

SELECT audit.audit_table('target_table');

该语句将以具有访问审核模式权限的用户角色执行。写入表的应用程序一定不能使用超级用户角色连接到数据库。

如果要求不需要跟踪不经常更改的字段，则可以在特定字段的情况下关闭这些审核日志。

然后，我们有了一个捕获审计日志的表。开发人员仍然需要编写程序来读取和解析这些日志并将其推送到目标数据库中。此逻辑的复杂性还将取决于将数据推送到的目标数据库或数据仓库。

在PostgreSQL CDC中使用Postgres审计触发器的局限性

这种方法的缺点是触发器会影响数据库的性能。避免这种性能下降的常见做法是拥有一个单独的表来跟踪主表，并在第二个表上具有触发器。可以使用Postgres逻辑复制功能完成主表和辅助表之间的同步。

方法2：使用Postgres逻辑解码( Logical Decoding)

逻辑解码使用预写日志的内容来创建数据库中发生的活动的日志。预写日志是内部日志，描述了存储级别上的数据库更改。这种方法的优点是它不会以任何方式影响数据库的性能。

该方法基于输出插件的安装而工作。为了启用逻辑解码，需要在Postgres配置中设置以下参数。

wal_level = logical           

max_replication_slots = 10

设置完这些参数后，执行以下命令以创建逻辑解码槽。

SELECT * FROM pg_create_logical_replication_slot('slot_repl', 'decode_test');

这将确保对表的每次更新都将创建可在视图选择语句中访问的审计事件。 逻辑解码支持两种选择语句来访问这些事件。 以下命令可用于窥视事件，

SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);

要使用或获取结果，请使用以下命令。

SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);

“消费”和“窥视”之间的区别在于，第二条命令在消费事件后便将其删除。 让我们尝试通过一个例子来理解这一点。

假设有一个学生表，我们在该表中插入了一个条目。

Insert into students (id, name, age) values (1, ‘Peter’, '30');

一旦执行了peek命令，结果将如下所示。

SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);

0/16CEE88 | 102 | BEGIN 102

0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:'
Peter' age[integer]:30

0/16CEFA0 | 102 | COMMIT 102

同样，如果我们执行相同的命令，您仍然会看到相同的审核事件。

SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);

0/16CEE88 | 102 | BEGIN 102

0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:'
Peter' age[integer]:30

0/16CEFA0 | 102 | COMMIT 102

现在执行get命令。

SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);

0/16CEE88 | 102 | BEGIN 102

0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:'
Peter' age[integer]:30

0/16CEFA0 | 102 | COMMIT 102

结果是一样的。 但是，如果再次执行相同的命令或peek命令，则会发现结果为零。

SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);

(0 rows)

这意味着当执行get命令时，将提供结果并删除结果，从而大大增强了我们编写使用这些事件创建表副本的逻辑的能力。

Postgres还支持使用输出插件-基本上是c程序，可以基于INSERT，UPDATE和DELETE事件执行，并将事件转换为更加用户友好的格式。 Wal2json是一个这样的插件，可以以JSON格式输出这些事件。大多数Degrecium之类的Postgres CDC附件都广泛使用了此插件。

这里要注意的一点是，这些事件不会捕获表的创建和修改步骤。

对Postgres CDC使用Postgres逻辑解码的缺点

同样，在这种情况下，开发人员需要编写复杂的逻辑来处理这些事件，然后将其转换为目标数据库的语句。根据您要解决的用例，这可能会增加项目时间表。

方法3：使用时间戳列

Postgres引擎提供了以上两种方法来实现CDC。如果您在表中具有timestamp列的灵活性，那么还有一个稍微复杂的自定义方法。这意味着开发人员将需要定期查询表并监视对timestamp列的更改。当检测到更改时，脚本可以创建适当的数据库语句以将这些更改写入目标数据库。但是，这种方法需要大量的精力，并且需要开发人员花费大量的时间和精力。

简而言之，即使Postgres提供了通过触发器，逻辑解码或通过自定义逻辑的连续同步支持，开发人员仍然需要捕获这些事件并将其转换为目标数据库。根据用例，将需要为不同的目标数据库专门编写此逻辑。一种替代方法是使用像Hevo这样的基于云的服务，该服务可以使用Postgres CDC将数据连续同步到大多数目的地。

设置POSTGRESQL更改数据捕获的简便方法

Hevo是一个完全托管的数据集成平台，可以帮助您将数据从PostgreSQL实时加载到您选择的任何目标，而无需编写任何代码。

Hevo开箱即用地支持Postgres CDC，并提供了一个点击式视觉界面来启用更改数据捕获。 您只需要将Hevo指向Postgres数据库中的auto-crementing列或timestamp列即可。

Hevo将完成所有艰苦的工作，并确保您的Postgres数据始终在目标数据库/数据仓库中是最新的。

本文：http://jiagoushi.pro/node/1041

讨论：请加入知识星球【首席架构师圈】或者小号【cea_csa_cto】