迁移数据
这是关于从 PostgreSQL 迁移到 ClickHouse 的指南的 第一部分。通过一个实用的示例,它演示了如何使用实时复制 (CDC) 方法高效地执行迁移。覆盖的许多概念同样适用于从 PostgreSQL 到 ClickHouse 的手动批量数据传输。
数据集
作为展示从 Postgres 到 ClickHouse 典型迁移的示例数据集,我们使用 这里 记录的 Stack Overflow 数据集。该数据集包含了从 2008 年到 2024 年 4 月在 Stack Overflow 上发生的每个 post
、vote
、user
、comment
和 badge
。以下是该数据的 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
值可以回绕,比较需要完全的表扫描,这使得跟踪变化更加复杂。