数据变更捕获 (CDC)：PostgreSQL 与 ClickHouse - 第二部分

本文字数：17486；估计阅读时间：44 分钟

审校：庄晓东（魏庄）

本文在公众号【ClickHouseInc】首发

如果您希望获取本文中概念的完整示例配置，请参阅此处【https://github.com/ClickHouse/examples/tree/main/cdc-postgresql-clickhouse】。感谢我们社区的Leart Beqiraj为此所作的贡献。

简介

在我们关于构建从PostgreSQL到ClickHouse的变更数据捕获（CDC）管道的系列中，本文将重点介绍构建功能性管道所需的步骤和配置。为此，我们使用了一个示例数据集，将其加载到PostgreSQL和ClickHouse中。考虑到PostgreSQL作为事实的源，我们应用了插入、更新和删除的混合工作负载。通过使用由Debezium、ClickHouse Kafka Connect和一个物化视图构建的CDC管道，我们可以几乎实时地在ClickHouse中的一个表中反映这些变更。

对于我们的示例，我们使用了ClickHouse Cloud集群中的一个开发实例和一个AWS Aurora实例的PostgreSQL。但是，这些示例应该可以在一个大小相当的自管理集群上重新生成。或者，立即启动您的ClickHouse Cloud集群，并获得300美元的信用额度。让我们担心基础设施并开始查询！

示例数据集

对于我们的示例数据集，我们使用了流行的英国房价数据集。这是一个适度大小的数据集（2800万行），其架构易于理解。每一行代表了过去20年英国的一次房屋销售，字段表示价格、日期和位置。字段的完整描述可以在这里找到（https://www.gov.uk/guidance/about-the-price-paid-data#explanations-of-column-headers-in-the-ppd）。我们将这个数据集加载到PostgreSQL和ClickHouse中，然后对前者进行随机插入、更新和删除。这些变更应该由Debezium捕获，并用于几乎实时地更新ClickHouse。

PostgreSQL模式和数据加载

下面是PostgreSQL的模式。请注意，我们将序列ID字段用作主键。虽然主键不是强制的，但是Debezium需要额外的PostgreSQL配置才能工作。

CREATE TABLE uk_price_paid                                                                                                                                                               (
   id serial,
   price INTEGER,
   date Date,
   postcode1 varchar(8),
   postcode2 varchar(3),
   type varchar(13),
   is_new SMALLINT,
   duration varchar(9),
   addr1 varchar(100),
   addr2 varchar(100),
   street varchar(60),
   locality varchar(35),
   town varchar(35),
   district varchar(40),
   county varchar(35),
   primary key(id)
);

我们将此数据集提供为与PostgreSQL兼容的SQL格式，已准备好进行插入，可从此处下载。加载数据只需几个简单的命令，假设已经通过环境变量配置了psql客户端：

wget
https://datasets-documentation.s3.eu-west-3.amazonaws.com/uk-house-prices/postgres/uk_prices.sql.tar.gz
tar -xzvf uk_prices.sql.tar.gz
psql < uk_prices.sql

INSERT 0 10000
INSERT 0 10000
INSERT 0 10000
…

postgres=> SELECT count(*) FROM uk_price_paid;
  count
----------
 27734966
(1 row)

注意：我们正在使用一个带有八个核心的AWS Aurora实例，其PostgreSQL版本为14.7。加载这些数据大约需要10分钟。

ClickHouse 模式

以下是我们文档中使用的模式的修改版本，使用了ReplacingMergeTree表引擎。

CREATE TABLE default.uk_price_paid
(
  `id` UInt64,
  `price` UInt32,
  `date` Date,
  `postcode1` LowCardinality(String),
  `postcode2` LowCardinality(String),
  `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4),
  `is_new` UInt8,
  `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2),
  `addr1` String,
  `addr2` String,
  `street` LowCardinality(String),
  `locality` LowCardinality(String),
  `town` LowCardinality(String),
  `district` LowCardinality(String),
  `county` LowCardinality(String),
  `version` UInt64,
  `deleted` UInt8
)
ENGINE = ReplacingMergeTree(version, deleted)
PRIMARY KEY (postcode1, postcode2, addr1, addr2)
ORDER BY (postcode1, postcode2, addr1, addr2, id)

虽然我们之前将先前的模式转换为ClickHouse类型以进行优化，但原始的PostgreSQL模式也可以被ClickHouse自动解释（除了序列类型）。例如，下面的DDL可以用于使用PostgreSQL类型创建表 - 这些类型将自动转换为ClickHouse类型，如下所示。需要注意的是，我们删除了主键，并手动将serial类型的id列转换为Uint64类型。

CREATE TABLE default.uk_price_paid
(
    `id` UInt64,
    `price` INTEGER,
    `date` Date,
    `postcode1` varchar(8),
    `postcode2` varchar(3),
    `type` varchar(13),
    `is_new` SMALLINT,
    `duration` varchar(9),
    `addr1` varchar(100),
    `addr2` varchar(100),
    `street` varchar(60),
    `locality` varchar(35),
    `town` varchar(35),
    `district` varchar(40),
    `county` varchar(35),
    `version` UInt64,
    `deleted` UInt8
)
ENGINE = ReplacingMergeTree(version, deleted)
PRIMARY KEY (postcode1, postcode2, addr1, addr2)
ORDER BY (postcode1, postcode2, addr1, addr2, id)

SHOW CREATE TABLE default.uk_price_paid

CREATE TABLE default.uk_price_paid
(
    `id` UInt64,
    `price` Int32,
    `date` Date,
    `postcode1` String,
    `postcode2` String,
    `type` String,
    `is_new` Int16,
    `duration` String,
    `addr1` String,
    `addr2` String,
    `street` String,
    `locality` String,
    `town` String,
    `district` String,
    `county` String,
    `version` UInt64,
    `deleted` UInt8
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', version, deleted)
PRIMARY KEY (postcode1, postcode2, addr1, addr2)
ORDER BY (postcode1, postcode2, addr1, addr2, id)
SETTINGS index_granularity = 8192

在我们之前的博客中探讨的ReplacingMergeTree引擎的概念的基础上，这里有一些重要的注意事项：

• 我们在表定义中使用了version和deleted列。这两者都是可选的。例如，如果您不需要支持删除操作，只需在模式和引擎定义中省略该列即可。

• 我们为ORDER BY子句选择了列，以优化查询访问模式。请注意，id列是如何被指定在最后的，因为我们不希望在分析查询中使用它们，但仍然需要它提供的唯一性属性 - 特别是我们的地址不超过街道级别。

• 我们使用PRIMARY KEY子句指定主键，省略id列以节省内存，对我们通常的查询没有影响。

配置CDC管道

架构概述

我们在之前的博客中提出的端到端架构如下所示。

该架构假设用户已经拥有一个运行Kafka Connect框架的Kafka实例。在我们的示例中，我们假设用户正在使用Confluent Cloud来托管Kafka，该服务会自动为事件创建相应的主题。不过，用户也可以选择自行托管Kafka。我们提出的架构将适用于任何能够写入由Debezium生成的事件的摄取管道。有关安装Debezium的指南以及有关主题配置的注意事项，请参阅此处的文档（https://debezium.io/documentation/reference/stable/install.html）。

正如我们在之前的文章中提到的，用户应确保对于每个唯一的PostgreSQL行，更改事件是按顺序传递的（如果删除事件需要被移除）。可以通过使用单个分区（Debezium源的默认设置）或者对于需要更多分区的情况使用基于哈希的分区来实现此目标。尽管后一种情况应该是较为罕见的，但是这涉及确保所有特定PostgreSQL行的更改事件都被发送到同一个分区，这是通过对其主键进行哈希来实现的。

在Kafka中使用日志压缩可以确保仅保留一行的最后事件，从而减少Kafka的存储占用。这需要在发生删除操作时，在Debezium中忽略墓碑事件。为了简化我们的管道，我们禁用了这些功能。如果用户需要使用这个高级功能，他们应该在Kafka Sink中删除这些事件。

Configuring Postgresql

PostgreSQL连接器可以与独立的PostgreSQL服务器或PostgreSQL服务器集群一起使用，但只能在主服务器上使用 - Debezium连接器无法连接到副本实例。

在Debezium文档中提到了以下几点：

“如果主服务器发生故障或被降级，连接器将停止工作。主服务器恢复后，您可以重新启动连接器。如果另一个PostgreSQL服务器被提升为主服务器，则在重新启动连接器之前需要调整连接器配置。”

确保Postgres实例已正确配置：

• 有关自管理的配置详细信息，请参阅此处。【https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-server-configuration】

• 有关基于云的环境，例如Amazon RDS，请参阅此处。【https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-in-the-cloud】

正如我们在之前的文章中提到的，我们假设使用输出插件pgoutput将数据从WAL（Write-Ahead Logging，预写式日志）内部表示转换为Debezium可以消费的格式。因此，我们下面的示例使用了逻辑复制流模式pgoutput。这个功能是内置于PostgreSQL 10+中的。较早版本的用户可以考虑使用由Debezium社区维护的decoderbufs插件或wal2json插件。我们尚未对这些配置进行过测试。

我们建议用户阅读以下关于安全性和用户配置的部分：

• 设置基本权限 - 我们在下面的示例中使用了postgres超级用户。这在生产部署中并不建议。

• 创建发布的权限 - Debezium 从为表创建的发布中流出 PostgreSQL 源表的变更事件。发布包含从一个或多个表生成的变更事件的过滤集。每个发布中的数据根据发布规范进行过滤。我们假设 Debezium 配置有足够的权限来创建这些发布。默认情况下，postgres 超级用户具有执行此操作的权限。然而，对于生产用例，我们建议用户自己创建这些发布，或者最小化分配给用于创建它们的连接器的 Debezium 用户的权限。

• 允许与 Debezium 连接器主机进行复制的权限

配置副本身份（Replica Identity）

Debezium 发送的消息内容取决于如何配置源目标的 REPLICA IDENTITY。REPLICA IDENTITY 是一个特定于 PostgreSQL 的表级设置，它确定逻辑解码插件在 UPDATE 和 DELETE 事件中的可用信息量。更具体地说，此设置控制在发生 UPDATE 或 DELETE 事件时涉及表列的先前值的哪些信息（如果有）可用。

尽管有四种不同的值可供选择，但我们建议根据您是否需要删除支持来选择以下设置：

• DEFAULT - 默认行为是，如果表具有主键，则更新和删除事件将包含表的主键列的先前值。对于UPDATE事件，只有具有更改值的主键列才会出现。如果表没有主键，则连接器不会为该表发出UPDATE或DELETE事件。只有在以下情况下才使用此设置：

  • 您的ClickHouse ORDER BY子句仅包含Postgres主键列。这种情况不太可能发生，因为通常用户会添加其他列到ORDER BY以优化在Postgres中不太可能是主键的聚合查询。

  • 您不需要支持删除操作。注意：下面使用的配置不需要更新前列的值。对于删除操作，前列的值是必需的，因为新状态为null。

• FULL - 更新和删除操作的发出事件将包含表中所有列的先前值。如果需要支持删除操作，则需要此设置。

使用ALTER命令来设置此设置。

ALTER TABLE uk_price_paid REPLICA IDENTITY FULL;

在以下文章部分中，我们假设用户需要支持删除操作。如果在不需要支持删除操作的情况下步骤有所不同，我们将提供替代配置的参考信息。

准备 ClickHouse

初始数据加载

在处理变更事件流之前，我们需要预先加载 ClickHouse 表，以确保其与 Postgres 保持一致。这可以通过几种方式实现，包括但不限于：

• 使用 postgres 表函数，直接从我们的 Postgres 实例中加载数据集，使用 INSERT INTO SELECT。这提供了一种快速简便的加载数据集的方法，但需要我们暂停 Postgres 实例上的更改。在生产环境中可能无法使用这种方法。

• 使用提供的 Postgres 导出文件。这可能需要将其转换为 ClickHouse 支持的格式，然后加载。对于大多数大型数据集来说，这种方法并不现实。

• 配置 Debezium，在首次启动时执行一致的快照。一旦快照完成，它将从生成快照的确切时间点继续流式传输变更。这使得连接器可以从一致的数据视图开始工作，忽略了在生成快照期间进行的更改。如何实现这一点的完整过程在此处有详细介绍。这个过程的结果是一系列类似于插入行时发送的读取事件。虽然下面我们的材料化视图管道可以处理这些事件，但从吞吐量的角度来看，这个过程往往不够理想。

为了速度和简便起见，我们选择了以上第一种选项。需要注意的是，我们的 INSERT INTO SELECT 语句将版本和删除列分别设置为值 1 和 0。

INSERT INTO uk_price_paid SELECT
  id,
  price,
  date,
  postcode1,
  postcode2,
  type,
  is_new,
  duration,
  addr1,
  addr2,
  street,
  locality,
  town,
  district,
  county,
  1 AS version,
  0 AS deleted
FROM postgresql('<host>', '<database>', '<table>', '<user>', '<password>')

0 rows in set. Elapsed: 80.885 sec. Processed 27.73 million rows, 5.63 GB (342.89 thousand rows/s., 69.60 MB/s.)

在没有进行调优的情况下，我们能够在 80 秒内加载完所有 2800 万行数据。

材料化视图（Materialized views）

Debezium 使用嵌套的 JSON 格式发送消息。如果正确配置（见下文），并将 REPLICA IDENTITY 设置为 FULL，变更事件将以嵌套的 JSON 形式包含行的列的前后值。这些消息的完整示例可以在此处找到，包括当不需要删除支持时将 REPLICA IDENTITY 设置为 DEFAULT 的情况。

作为示例，我们在下面展示了一个更新消息（REPLICA IDENTITY=Full）。

{
  "before": {
  "id": 50658675,
  "price": 227500,
  "date": 11905,
  "postcode1": "SP2",
  "postcode2": "7EN",
  "type": "detached",
  "is_new": 0,
  "duration": "freehold",
  "addr1": "31",
  "addr2": "",
  "street": "CHRISTIE MILLER ROAD",
  "locality": "SALISBURY",
  "town": "SALISBURY",
  "district": "SALISBURY",
  "county": "WILTSHIRE"
  },
  "after": {
  "id": 50658675,
  "price": 227500,
  "date": 11905,
  "postcode1": "SP2",
  "postcode2": "7EN",
  "type": "terraced",
  "is_new": 0,
  "duration": "freehold",
  "addr1": "31",
  "addr2": "",
  "street": "CHRISTIE MILLER ROAD",
  "locality": "SALISBURY",
  "town": "SALISBURY",
  "district": "SALISBURY",
  "county": "WILTSHIRE"
  },
  "source": {
  "version": "1.9.6.Final",
  "connector": "postgresql",
  "name": "postgres_server",
  "ts_ms": 1685378780355,
  "snapshot": "false",
  "db": "postgres",
  "sequence": "[\"247833040488\",\"247833042536\"]",
  "schema": "public",
  "table": "uk_price_paid",
  "txId": 106940,
  "lsn": 247833042536,
  "xmin": null
  },
  "op": "u",
  "ts_ms": 1685378780514,
  "transaction": null
}

这里的 op 字段表示操作，其值为 u、d 和 c，分别表示更新、删除和插入操作。source.lsn 字段提供了我们的版本值。对于删除事件，after 字段为空。相反，对于插入事件，before 字段为空。

这种消息格式与我们在 ClickHouse 中的目标表 uk_price_paid 不兼容。我们可以在插入时使用材料化视图来转换这些消息。下面是具体示例：

CREATE MATERIALIZED VIEW uk_price_paid_mv TO uk_price_paid
(
   `id` Nullable(UInt64),
   `price` Nullable(UInt32),
   `date` Nullable(Date),
   `postcode1` Nullable(String),
   `postcode2` Nullable(String),
   `type` Nullable(Enum8('other'=0, 'terraced'=1, 'semi-detached'=2, 'detached'=3, 'flat'=4)),
   `is_new` Nullable(UInt8),
   `duration` Nullable(Enum8('unknown'=0, 'freehold'=1, 'leasehold'=2)),
   `addr1` Nullable(String),
   `addr2` Nullable(String),
   `street` Nullable(String),
   `locality` Nullable(String),
   `town` Nullable(String),
   `district` Nullable(String),
   `county` Nullable(String),
   `version` UInt64,
   `deleted` UInt8
) AS
SELECT
   if(op = 'd', before.id, after.id) AS id,
   if(op = 'd', before.price, after.price) AS price,
   if(op = 'd', toDate(before.date), toDate(after.date)) AS date,
   if(op = 'd', before.postcode1, after.postcode1) AS postcode1,
   if(op = 'd', before.postcode2, after.postcode2) AS postcode2,
   if(op = 'd', before.type, after.type) AS type,
   if(op = 'd', before.is_new, after.is_new) AS is_new,
   if(op = 'd', before.duration, after.duration) AS duration,
   if(op = 'd', before.addr1, after.addr1) AS addr1,
   if(op = 'd', before.addr2, after.addr2) AS addr2,
   if(op = 'd', before.street, after.street) AS street,
   if(op = 'd', before.locality, after.locality) AS locality,
   if(op = 'd', before.town, after.town) AS town,
   if(op = 'd', before.district, after.district) AS district,
   if(op = 'd', before.county, after.county) AS county,
   if(op = 'd', source.lsn, source.lsn) AS version,
   if(op = 'd', 1, 0) AS deleted
FROM default.uk_price_paid_changes
WHERE (op = 'c') OR (op = 'r') OR (op = 'u') OR (op = 'd')

请注意，我们的材料化视图在此处根据操作选择每个列的适当值。版本是基于 source.lsn 列的，并且如果 op 列的值为 d，则将删除列设置为 1，否则设置为 0。如果需要，op = r 值还允许我们支持快照事件。如果不需要删除支持，则可以简化此材料化视图。

熟悉 ClickHouse 的读者会注意到，此视图将行插入到 uk_price_paid 表中，并从 uk_price_paid_changes 表中选择行。后者将接收来自我们的 Kafka Sink 的行插入。此表的架构必须与前述的 Debezium 消息的架构保持一致。以下是此表的架构：

CREATE TABLE uk_price_paid_changes
(
  `before.id` Nullable(UInt64),
  `before.price` Nullable(UInt32),
  `before.date` Nullable(UInt32),
  `before.postcode1` Nullable(String),
  `before.postcode2` Nullable(String),
  `before.type` Nullable(Enum8('other'=0,'terraced'=1,'semi-detached'=2,'detached'=3,'flat'=4)),
  `before.is_new` Nullable(UInt8),
  `before.duration` Nullable(Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2)),
  `before.addr1` Nullable(String),
  `before.addr2` Nullable(String),
  `before.street` Nullable(String),
  `before.locality` Nullable(String),
  `before.town` Nullable(String),
  `before.district` Nullable(String),
  `before.county` Nullable(String),
  `after.id` Nullable(UInt64),
  `after.price` Nullable(UInt32),
  `after.date` Nullable(UInt32),
  `after.postcode1` Nullable(String),
  `after.postcode2` Nullable(String),
  `after.type` Nullable(Enum8('other'=0,'terraced'=1,'semi-detached'=2,'detached'=3,'flat'=4)),
  `after.is_new` Nullable(UInt8),
  `after.duration` Nullable(Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2)),
  `after.addr1` Nullable(String),
  `after.addr2` Nullable(String),
  `after.street` Nullable(String),
  `after.locality` Nullable(String),
  `after.town` Nullable(String),
  `after.district` Nullable(String),
  `after.county` Nullable(String),
  `op` LowCardinality(String),
  `ts_ms` UInt64,
  `source.sequence` String,
  `source.lsn` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

出于调试目的，我们使用 MergeTree 引擎为此表提供支持。在生产环境中，这可以是 Null 引擎 - 这样变更将不会被持久化，但转换后的行仍然会被发送到目标表 uk_price_paid。如果不需要删除支持，并且 REPLICA IDENTITY 设置为 DEFAULT，则可以使用更简单的表。

细心的读者可能会注意到我们的架构是根据 Debezium 发送的嵌套消息展开的。我们将在我们的 Debezium 连接器中执行此操作。这种做法在架构上更简单，并且允许我们配置 Nullable 值。涉及 Tuple 的替代方案更为复杂。

配置 Debezium

在 Kafka 连接框架中部署连接器需要进行以下设置。请注意，我们假设消息以 JSON 格式发送，且没有模式：

• value.converter - org.apache.kafka.connect.json.JsonConverter

• key.converter - org.apache.kafka.connect.storage.StringConverter

• key.converter.schemas.enable - false

• value.converter.schemas.enable - false

• decimal.format - 控制此转换器序列化小数的格式。此值不区分大小写，可以是 BASE64（默认值）或 NUMERIC。应设置为 BASE64。有关小数处理的详细信息，请参见此处（https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-decimal-types）。

此管道所需的完整配置设置列表，如数据库连接详细信息等，可在此处找到（https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-connector-properties）。以下我们重点介绍对消息格式最有影响的配置。重要提示：我们配置连接器以在每个表级别跟踪更改：

• plugin.name - 安装在 PostgreSQL 服务器上的 PostgreSQL 逻辑解码插件的名称。我们建议使用 pgoutput。

• slot.name - 逻辑解码槽名称。它必须对数据库和模式是唯一的。如果只有一个，使用 debezium。

• publication.name - 在使用 pgoutput 时为流更改创建的 PostgreSQL 发布的名称。如果已将 Postgres 用户配置为具有足够权限，则在启动时将创建此发布。或者，它可以预先创建。可以使用 dbz_publication default。

• table.include.list - 可选的、逗号分隔的正则表达式列表，用于匹配要捕获更改的表的全限定表标识符。确保格式为<schema_name>.<table_name>。对于我们的示例，我们使用public.uk_price_paid。此参数只能指定一个表。

• tombstones.on.delete - 控制是否在删除事件后跟随墓碑事件。设置为 false。如果您有兴趣使用日志压缩，则可以将其设置为 true - 这需要您在 ClickHouse Sink 中删除这些墓碑。

• publication.autocreate.mode - 设置为 filtered。这会导致仅为属性 table.include.list 中的表创建发布。更多详细信息请参见此处。

• snapshot.mode - 我们使用 never 作为快照模式 - 因为我们使用 postgres 函数加载了初始数据。如果无法暂停对 Postgres 实例的更改，用户可以使用 initial 模式。

• decimal.handling.mode - 指定连接器应如何处理 DECIMAL 和 NUMERIC 列的值。默认值 precise 将这些编码为它们的二进制形式，即 java.math.BigDecimal。与上述 decimal.format 设置结合使用，这将导致它们以数字形式输出到 JSON。用户可能希望根据所需的精度进行调整。

以下设置将影响 Postgres 中更改与其到达 ClickHouse 的延迟时间。在所需的 SLA 和向 ClickHouse 的有效批处理的上下文中考虑这些设置 - 请参见其他注意事项。

• max.batch.size - 每批事件的最大大小。

• max.queue.size - 将事件发送到 Kafka 之前的队列大小。允许背压。它应大于批量大小。

• poll.interval.ms - 正整数值，指定连接器在开始处理一批事件之前等待新更改事件出现的毫秒数。默认为 500 毫秒。

Confluent 为那些使用 Confluent Kafka 或云的用户提供了额外的文档。可以在 Confluent Cloud 中配置 Debezium 连接器，如下所示。当接收到消息时，此连接器将自动创建一个 Kafka 主题。

请注意，我们在上述设置中将 after.state.only 属性设置为 false。这个设置似乎是针对 Confluent Cloud 的，必须将其设置为 false 才能确保提供行的先前值以及 LSN 编号。

此外，我们还利用 Kafka 连接的 SMT 功能展开消息，并将 Kafka 主题设置为 uk_price_paid_changes。这可以通过配置在自管理环境中实现。更多详细信息请参见 [1][2]。

在上面的示例中，我们假设目标主题只有一个分区 - 这是由 Debezium 自动创建的。正如前面所讨论的，多个分区需要使用基于哈希的路由来确保将同一行的事件传递到同一分区 - 从而确保在下游传递时按顺序传递。这超出了本文的范围，需要进一步测试。

上述配置的关联 JSON 配置可以在此处找到，并可以与官方文档中的步骤一起使用。自管理安装说明可以在此处找到（https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-deployment）。

配置 Kafka 连接器 Sink

我们可以使用 ClickHouse Kafka 连接器 Sink 从 Kafka 中读取更改事件消息并将其发送到 ClickHouse。这假设用户正在运行 Kafka 连接框架。有多种 Kafka 连接器 Sink 可以与 ClickHouse 连接，包括 Confluent HTTP 连接器。但对于我们的用例，我们选择使用 ClickHouse 的官方 Kafka 连接器 Sink。虽然 ReplacingMergeTree 的属性只要求至少一次语义，但这提供了恰好一次的语义，现在可以在 Confluent Cloud 中使用“自定义连接器”提供。

重要的是，Kafka 连接器 Sink 保证消息按分区按顺序传递。这是通过以下方式保证的：

• Kafka 连接框架仅将一个任务分配给任何给定的分区 - 虽然一个任务可能潜在地从多个分区消费。

• 在插入时，ClickHouse Kafka 连接器 Sink 将行按主题和分区分组后再插入。在另一个批次被消耗之前，批次的插入将得到确认。

这允许对主题的分区数量进行缩放，同时仍然满足我们对特定 Postgres 行的任何更改的按顺序传递的要求，假设我们可以通过从 Debezium 连接器进行哈希来确保将行的任何事件发送到相同的分区。

我们在下面展示了如何在 Confluent Cloud 中配置 ClickHouse Kafka 连接器 Sink。请注意，我们首先上传连接器包以使其可用。这可以从此处下载。我们假设用户已经配置了 Debezium 连接器以将数据发送到主题 uk_price_paid_changes。

以上配置的 JSON 表示可以在此处查看。（https://github.com/ClickHouse/examples/blob/main/cdc/postgresql/connectors/kafka_connect.json）

除了基于 Kafka 连接的方法之外，还有其他替代方案，例如用户可以使用 Vector、Kafka 表引擎或 Confluent Cloud 提供的 HTTP 连接器。

测试

为了确认我们的管道是否正常工作，我们提供了一个脚本，用于对 Postgres 表进行随机更改，包括添加、更新和删除行。具体来说，更新方面，此脚本会随机更改行的类型、价格和 is_new 列。完整的代码和依赖项可以在此处找到【https://github.com/ClickHouse/examples/blob/main/cdc/postgresql/randomize.py】。

export PGDATABASE=<database>
export PGUSER=postgres
export PGPASSWORD=<password>
export PGHOST=<host>
export PGPORT=5432
pip3 install -r requirements.txt
python randomize.py --iterations 1 --weights "0.4,0.4,0.2" --delay 0.5

请注意，weights 参数和值 0.4,0.4,0.2 表示创建、更新和删除的比例。delay 参数设置每个操作之间的时间延迟（默认 0.5 秒）。iterations 参数设置要对表进行的总更改次数。在上述示例中，我们修改了 1000 行。

脚本运行完成后，我们可以针对 Postgres 和 ClickHouse 运行以下查询以确认它们的一致性。由于更改是随机的，所以显示的响应可能与您的值不同。但两个数据库中的值应该是相同的。为了简化，我们使用 FINAL 关键字。

行数统计一致

-- Postgres
postgres=> SELECT count(*) FROM uk_price_paid;
  count
----------
 27735027
(1 row)


-- ClickHouse
SELECT count()
FROM uk_price_paid
FINAL

┌──count()─┐
│ 27735027 │
└──────────┘

价格统计相同

-- Postgres
postgres=> SELECT sum(price) FROM uk_price_paid;
     sum
---------------
5945061701495
(1 row)

-- ClickHouse
SELECT sum(price)
FROM uk_price_paid
FINAL

┌────sum(price)─┐
│ 5945061701495 │
└───────────────┘

房价分布相同

postgres=> SELECT type, count(*) c FROM uk_price_paid GROUP BY type;
   type    |  c
---------------+---------
 detached    | 6399743
 flat        | 4981171
 other       |  419212
 semi-detached | 7597039
 terraced    | 8337862
(5 rows)


-- ClickHouse
SELECT
  type,
  count() AS c
FROM uk_price_paid
FINAL
GROUP BY type

┌─type──────────┬───────c─┐
│ other       │  419212 │
│ terraced    │ 8337862 │
│ semi-detached │ 7597039 │
│ detached    │ 6399743 │
│ flat        │ 4981171 │
└───────────────┴─────────┘

其他考虑因素

在使用 Debezium 将 ClickHouse 和 Postgres 用于 CDC 管道时，还有一些其他考虑因素：

• Debezium 连接器会尽可能地对行更改进行批处理，最大批处理大小为 max.batch.size。这些批次是每个轮询间隔 poll.interval.ms（默认为 500ms）形成的。用户可以增加这些值，以获取更大、更高效的批处理，但这会增加端到端的延迟。请记住，ClickHouse 更喜欢至少有 1000 行的批次，以避免常见问题，如部分过多。对于低吞吐量环境（<每秒 100 行），批处理不那么关键，因为 ClickHouse 可能会跟上合并。但是，用户应避免高速率插入小批次。

• 批处理也可以在 Sink 端进行配置。目前，ClickHouse Connect Sink 并不明确支持此功能，但可以通过 Kafka connect 框架进行配置 - 参见设置 consumer.override.max.poll.records。或者，用户可以配置 ClickHouse 异步插入，并允许 ClickHouse 进行批处理。在此模式下，插入可以作为小批次发送到 ClickHouse，ClickHouse 将在刷新之前对行进行批处理。请注意，在刷新时，行将无法搜索。因此，此方法**不会**有助于端到端的延迟。

• 在监视具有较少更改的表的数据库中，用户应注意 WAL 磁盘使用情况以及心跳间隔 heartbeat.interval.ms 的重要性。

• 以上方法当前不支持 Postgres 主键更改。要实现这一点，用户需要检测来自 Debezium 的 op=delete 消息，该消息没有 before 或 after 字段。然后，应使用 id 删除 ClickHouse 中的这些行 - 最好使用轻量级删除。这需要自定义代码，而不是使用 Kafka sink 将数据发送到 ClickHouse。

• 如果表的主键发生更改，用户可能需要创建一个新的 ClickHouse 表，其中新列作为 ORDER BY 子句的一部分。请注意，这也需要为 Debezium 连接器执行流程。

• Debezium 连接器所依赖的逻辑解码不支持 DDL 更改。这意味着连接器无法将 DDL 更改事件报告给消费者。

• 逻辑解码复制插槽仅受支持于主服务器上。当存在一组 PostgreSQL 服务器时，连接器只能在活动主服务器上运行。它无法在热备份或暖备份副本上运行。如果主服务器失败或被降级，连接器将停止。主服务器恢复后，可以重新启动连接器。如果另一个 PostgreSQL 服务器已被提升为主服务器，请在重新启动连接器之前调整连接器配置。

• 虽然 Kafka Sink 可以安全地扩展以使用更多的工作进程（假设相同的 Postgres 行的事件已被散列到相同的分区），但 Debezium 连接器只允许单个任务。上述解决方案每个表使用一个连接器，允许在表级别扩展解决方案。

• 文档中记录的方法假定每个表一个连接器实例。我们目前不支持一个连接器监视多个表 - 尽管可能通过主题路由实现，即将消息路由到特定表的主题。此配置尚未经过测试。

结论

在本博客文章中，我们探讨了如何构建 CDC 管道，以实现从 Postgres 到 ClickHouse 的变更的近实时复制。我们讨论了 ReplacingMergeTree 对此设计的基本作用，以及用户如何优化表设计并使用 FINAL 运算符进行查询时间去重。除了提供构建管道的说明，包括如何配置 Debezium，我们还讨论了用户想要构建生产解决方案时的其他考虑因素。尽管本博客文章中的细节是针对 Postgres 的，但由于 Debezium 具有与 DBMS 无关的消息格式，因此这些细节可能适用于 Debezium 支持的所有源数据库。我们将此留给读者作为一个探索其他数据库的练习，并让我们知道你的进展！

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com

​​联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求