跳到主要内容
跳到主要内容

迁移数据

这是关于从 PostgreSQL 迁移到 ClickHouse 的指南的 第一部分。通过一个实用的示例,它演示了如何使用实时复制 (CDC) 方法高效地执行迁移。覆盖的许多概念同样适用于从 PostgreSQL 到 ClickHouse 的手动批量数据传输。

数据集

作为展示从 Postgres 到 ClickHouse 典型迁移的示例数据集,我们使用 这里 记录的 Stack Overflow 数据集。该数据集包含了从 2008 年到 2024 年 4 月在 Stack Overflow 上发生的每个 postvoteusercommentbadge。以下是该数据的 PostgreSQL 模式:

在 PostgreSQL 中创建表的 DDL 命令可以在 这里 找到。

这个模式虽然不一定是最优的,但利用了许多流行的 PostgreSQL 特性,包括主键、外键、分区和索引。

我们将把这些概念迁移到它们的 ClickHouse 等价物。

对于希望将此数据集填充到 PostgreSQL 实例以测试迁移步骤的用户,我们提供了带有 DDL 的 pg_dump 格式的数据以供下载,后续的数据加载命令如下所示:

虽然对于 ClickHouse 来说数据集较小,但对于 Postgres 来说,这个数据集是相当庞大的。上述表示的是覆盖 2024 年前三个月的一个子集。

虽然我们的示例结果使用完整数据集来展示 Postgres 和 ClickHouse 之间的性能差异,但下面文档记录的所有步骤在功能上与较小的子集是相同的。希望将完整数据集加载到 Postgres 中的用户可以查看 这里。由于上述模式施加的外键约束,PostgreSQL 的完整数据集仅包含符合引用完整性的行。可以直接将 Parquet 版本 轻松加载到 ClickHouse 中,且没有此类约束。

数据迁移

实时复制 (CDC)

请参考此 指南 来设置 PostgreSQL 的 ClickPipes。该指南涵盖了许多不同类型的源 PostgreSQL 实例。

使用 ClickPipes 或 PeerDB 的 CDC 方法,PostgreSQL 数据库中的每个表都会自动复制到 ClickHouse 中。

为了在近实时中处理更新和删除,ClickPipes 使用 ReplacingMergeTree 引擎将 Postgres 表映射到 ClickHouse,该引擎专门设计用于处理 ClickHouse 中的更新和删除。有关如何使用 ClickPipes 复制数据到 ClickHouse 的更多信息,请查看 这里。需要注意的是,使用 CDC 进行复制时,ClickHouse 中会在复制更新或删除操作时产生重复的行。查看技术,使用 FINAL 修饰符来处理 ClickHouse 中的这些行。

让我们看看如何使用 ClickPipes 在 ClickHouse 中创建 users 表。

设置完成后,ClickPipes 开始将所有数据从 PostgreSQL 迁移到 ClickHouse。根据网络和部署的大小,这应该只需要几分钟时间来完成对 Stack Overflow 数据集的迁移。

手动批量加载与定期更新

通过手动方式,数据集的初始批量加载可以通过以下方式实现:

  • 表函数 - 使用 ClickHouse 中的 Postgres 表函数 SELECT 从 Postgres 中提取数据并 INSERT 到 ClickHouse 表中。适用于数百 GB 的批量加载数据集。
  • 导出 - 导出到中间格式,如 CSV 或 SQL 脚本文件。然后可以通过 INSERT FROM INFILE 子句从客户端加载这些文件到 ClickHouse,或使用对象存储及其相关功能,如 s3、gcs。

在从 PostgreSQL 手动加载数据时,您需要首先在 ClickHouse 中创建表。请参考此 数据建模文档,也使用 Stack Overflow 数据集来优化 ClickHouse 中的表模式。

PostgreSQL 和 ClickHouse 之间的数据类型可能有所不同。为了确立每个表列的等效类型,我们可以使用带有 Postgres 表函数DESCRIBE 命令。以下命令描述了 PostgreSQL 中的 posts 表,请根据您的环境进行修改:

有关 PostgreSQL 和 ClickHouse 之间数据类型映射的概述,请参阅 附录文档

优化此模式类型的步骤与从其他来源加载数据时是相同的,例如 S3 上的 Parquet。应用此 替代指南使用 Parquet 中所描述的过程会得到以下模式:

我们可以使用简单的 INSERT INTO SELECT 填充这个表,从 PostgresSQL 读取数据并插入到 ClickHouse 中:

增量加载可以依次安排。如果 Postgres 表只接收插入,并且存在递增的 id 或时间戳,用户可以使用上述表函数的方法加载增量,即可以在 SELECT 中应用 WHERE 子句。此方法也可以支持更新,如果这些更新保证会更新相同的列。然而,支持删除将需要完全重新加载,当表增长时,这可能会变得困难。

我们演示了使用 CreationDate 的初始加载和增量加载(我们假设如果行被更新,则此字段将被更新)。

ClickHouse 将推动简单的 WHERE 子句,如 =!=>>=<<= 和 IN 到 PostgreSQL 服务器。因此,通过确保在用于识别更改集的列上存在索引,可以使增量加载更有效。

使用查询复制时检测 UPDATE 操作的一种可能方法是使用 XMIN 系统列(事务 ID)作为水印 - 此列的变化指示变化,因此可以应用到目标表。使用此方法的用户应注意,XMIN 值可以回绕,比较需要完全的表扫描,这使得跟踪变化更加复杂。

点击这里查看第二部分