从 PostgreSQL 加载数据到 ClickHouse
这是关于从 PostgreSQL 迁移到 ClickHouse 的指南的**第 1 部分**。此内容可被视为入门级，旨在帮助用户部署一个遵循 ClickHouse 最佳实践的初始功能系统。它避免了复杂主题，并且不会产生完全优化的模式；相反，它为用户构建生产系统和建立学习基础提供了坚实的基础。
数据集
作为一个示例数据集来展示从 Postgres 到 ClickHouse 的典型迁移，我们使用这里记录的 Stack Overflow 数据集。这包含从 2008 年到 2024 年 4 月在 Stack Overflow 上发生的每个post、vote、user、comment 和badge。此数据的 PostgreSQL 模式如下所示
在 PostgreSQL 中创建表的 DDL 命令可在此处找到。
此模式虽然不一定是最佳的，但利用了许多流行的 PostgreSQL 功能，包括主键、外键、分区和索引。
我们将把这些概念中的每一个迁移到它们的 ClickHouse 等效项。
对于希望将此数据集填充到 PostgreSQL 实例中以测试迁移步骤的用户，我们提供了 pg_dump 格式的数据供下载，以及随后的数据加载命令如下所示
# users
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/users.sql.gz
gzip -d users.sql.gz
psql < users.sql
# posts
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/posts.sql.gz
gzip -d posts.sql.gz
psql < posts.sql
# posthistory
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/posthistory.sql.gz
gzip -d posthistory.sql.gz
psql < posthistory.sql
# comments
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/comments.sql.gz
gzip -d comments.sql.gz
psql < comments.sql
# votes
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/votes.sql.gz
gzip -d votes.sql.gz
psql < votes.sql
# badges
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/badges.sql.gz
gzip -d badges.sql.gz
psql < badges.sql
# postlinks
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/postlinks.sql.gz
gzip -d postlinks.sql.gz
psql < postlinks.sql
虽然对于 ClickHouse 来说很小，但此数据集对于 Postgres 来说却很大。上面显示的是涵盖 2024 年前三个月的子集。
虽然我们的示例结果使用完整数据集来显示 Postgres 和 Clickhouse 之间的性能差异，但下面记录的所有步骤在功能上与较小的子集相同。想要将完整数据集加载到 Postgres 中的用户请参见此处。由于上述模式强加的外键约束，PostgreSQL 的完整数据集仅包含满足引用完整性的行。如果需要，可以使用Parquet 版本（没有此类约束）轻松直接加载到 ClickHouse 中。
迁移数据
在 ClickHouse 和 Postgres 之间迁移数据分为两种主要的工作负载类型
- 初始批量加载和定期更新 - 必须迁移初始数据集，并在设定的时间间隔（例如，每天）进行定期更新。此处的更新通过重新发送已更改的行来处理 - 通过可以用于比较的列（例如，日期）或 XMIN 值来识别。删除通过完整地定期重新加载数据集来处理。
- 实时复制或 CDC - 必须迁移初始数据集。此数据集的更改必须以接近实时的速度反映在 ClickHouse 中，只有几秒钟的延迟是可以接受的。这实际上是一个更改数据捕获 (CDC) 过程，其中 Postgres 中的表必须与 ClickHouse 同步，即 Postgres 表中的插入、更新和删除必须应用于 ClickHouse 中的等效表。
初始批量加载和定期更新
此工作负载代表上述工作负载中较简单的一种，因为更改可以定期应用。可以通过以下方式实现数据集的初始批量加载
- 表函数 - 在 ClickHouse 中使用Postgres 表函数从 Postgres 中
SELECT数据并将其INSERT到 ClickHouse 表中。这与高达数百 GB 数据集的批量加载相关。 - 导出 - 导出到中间格式，例如 CSV 或 SQL 脚本文件。然后，可以从客户端通过
INSERT FROM INFILE子句或使用对象存储及其关联函数（即 s3、gcs）将这些文件加载到 ClickHouse 中。
依次可以安排增量加载。如果 Postgres 表仅接收插入并且存在递增 ID 或时间戳，则用户可以使用上述表函数方法来加载增量，即可以将WHERE子句应用于SELECT。此方法也可用于支持更新，如果这些更新保证更新同一列。但是，支持删除将需要完全重新加载，这随着表的增长可能难以实现。
我们演示了使用CreationDate进行初始加载和增量加载（我们假设如果更新行，则此日期也会更新）。
-- initial load
INSERT INTO stackoverflow.posts SELECT * FROM postgresql('<host>', 'postgres', 'posts', 'postgres', '<password')
INSERT INTO stackoverflow.posts SELECT * FROM postgresql('<host>', 'postgres', 'posts', 'postgres', '<password') WHERE CreationDate > ( SELECT (max(CreationDate) FROM stackoverflow.posts)
ClickHouse 将把简单的
WHERE子句（例如 =、!=、>、>=、<、<= 和 IN）下推到 PostgreSQL 服务器。因此，通过确保在用于识别更改集的列上存在索引，可以使增量加载更有效率。
使用查询复制检测 UPDATE 操作的一种可能方法是使用XMIN 系统列（事务 ID）作为水印 - 此列的更改表示更改的发生，因此可以应用于目标表。使用此方法的用户应注意，XMIN 值可能会循环，并且比较需要完整表扫描，这使得跟踪更改更加复杂。有关此方法的更多详细信息，请参见“更改数据捕获 (CDC)”。
实时复制或 CDC
更改数据捕获 (CDC) 是在两个数据库之间保持表同步的过程。如果要以接近实时的速度处理更新和删除，这将变得更加复杂。目前存在多种解决方案
ClickHouse 的 PeerDB - PeerDB 提供了一个开源的专业 Postgres CDC 解决方案，用户可以自行管理或通过 SaaS 解决方案运行，该解决方案已被证明在 Postgres 和 ClickHouse 的大规模数据传输和可靠性保证方面表现良好。它支持在线和离线加载。
构建您自己的 - 这可以通过Debezium + Kafka实现 - Debezium 提供了捕获 Postgres 表上所有更改的能力，并将这些更改作为事件转发到 Kafka 队列。然后，这些事件可以由 ClickHouse Kafka 连接器或ClickHouse Cloud 中的 Clickpipes使用，以插入到 ClickHouse 中。这代表了更改数据捕获 (CDC)，因为 Debezium 不仅会执行表的初始复制，还会确保检测到 Postgres 上的所有后续更新、删除和插入，从而导致下游事件。这需要仔细配置 Postgres、Debezium 和 ClickHouse。示例可在此处找到。
在本指南中的示例中，我们假设仅进行初始批量加载，重点关注数据探索和轻松迭代，以实现可用于其他方法的生产模式。