如何把 PostgreSQL 数据库整体迁移到 ClickHouse：表结构转换与数据传送

如何把 PostgreSQL 数据库整体迁移到 ClickHouse表结构转换与数据传送【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse假设你的 PostgreSQL 里已经有一批业务表目标是把它们搬进 ClickHouse 做分析查询先确定 PostgreSQL 每张表的列和类型在 ClickHouse 里对应什么再把数据从 Postgres 拉到 ClickHouse 表里之后能定期把增量数据补进来。官方迁移指南PostgreSQL 迁移总览、Part 1Migrating data给出两条路径实时 CDC 复制以及手动批量装载 定期增量更新。本文以手动路径为主线因为表结构转换这一步在这条路径上由你显式完成CDC 路径作为可选替代放在最后。前提条件一台可访问的 PostgreSQL 实例地址host:port、数据库名、用户名和密码下文命令中需要替换这些值一台 ClickHouse 实例且能连通该 PostgreSQL 地址数据量在数百 GB 级别时官方的 table function 批量装载路径适用文档明确说该方式 relevant to bulk loads up to datasets of several hundred GB。先确定迁移策略官方文档的判断依据实时 CDC持续捕获 PostgreSQL 的 insert、update、delete 并近实时同步到 ClickHouse适合需要新鲜数据的场景但设置更复杂。使用 ClickPipesClickHouse Cloud 场景或 PeerDB自建 ClickHouse 场景。手动批量装载 定期更新一次性批量加载之后按调度周期同步变更。适合一次性迁移或对实时性不敏感的场景缺点是 Postgres 中的变更不会立即出现在 ClickHouse。如果你的需求只是把现有数据完整搬过去、之后按天/按小时补增量走手动路径即可下面的步骤全部围绕它展开。第一步用 DESCRIBE 导出 PostgreSQL 表的实际结构不需要在 PostgreSQL 侧手写元数据ClickHouse 的 postgresql 表函数可以直接描述远端表。对posts表执行host、port、username、password换成你的实际值DESCRIBE TABLE postgresql(host:port, postgres, posts, username, password) SETTINGS describe_compact_output 1逐张表执行得到每列在 ClickHouse 侧的推断类型。PostgreSQL 与 ClickHouse 的数据类型并不总是一一对应官方附录给出了映射表Appendix: Data type mappings常用的几行Postgres 类型ClickHouse 类型DATEDateTIMESTAMPDateTimeSMALLINTInt16INTEGERInt32BIGINTInt64SERIALUInt32BIGSERIALUInt64REAL/FLOAT4Float32DOUBLEFloat64DECIMAL/NUMERICDecimalTEXT/VARCHAR/CHAR/BPCHARStringBOOLEANBoolUUIDUUIDARRAY/ARRAYTArray/Array(T)JSONBString映射表之外低基数的字符串列可以进一步选LowCardinality(String)、枚举值固定的列可以选Enum来优化存储——官方 Part 1 的示例表就是这样优化后的结果文档示例Stack Overflow 数据集的posts表CREATE TABLE stackoverflow.posts ( Id Int32, PostTypeId Enum(Question 1, Answer 2, Wiki 3, TagWikiExcerpt 4, TagWiki 5, ModeratorNomination 6, WikiPlaceholder 7, PrivilegeWiki 8), AcceptedAnswerId UInt32, CreationDate DateTime, Score Int32, ViewCount UInt32, Body String, OwnerUserId Int32, OwnerDisplayName String, LastEditorUserId Int32, LastEditorDisplayName String, LastEditDate DateTime, LastActivityDate DateTime, Title String, Tags String, AnswerCount UInt16, CommentCount UInt8, FavoriteCount UInt8, ContentLicense LowCardinality(String), ParentId String, CommunityOwnedDate DateTime, ClosedDate DateTime ) ENGINE MergeTree ORDER BY tuple() COMMENT Optimized types建表时的关键决策是排序键ORDER BY。官方 Part 3 强调ClickHouse 的主键是稀疏索引与 PostgreSQL 的 B-tree 主键语义完全不同不能直接照搬 OLTP 主键排序键决定数据在磁盘上的顺序直接影响压缩率和查询性能见 Part 3: Data modeling techniques。上例中ORDER BY tuple()是官方优化指南为示例数据集给出的结果你的表应根据自己的查询过滤列来选择排序键。第二步用 INSERT INTO SELECT 把数据从 PostgreSQL 拉过来表建好后数据传送就是一条INSERT INTO SELECT直接读远端表INSERT INTO stackoverflow.posts SELECT * FROM postgresql(host:port, postgres, posts, username, password)执行后 ClickHouse 返回处理统计。官方文档展示的示例输出Stack Overflowposts表5982 万行 / 83.82 GB0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)这是文档示例你的行数、耗时和吞吐会随数据量不同但返回里会包含同样形式的 Processed 行数统计可以用来核对搬过去的量级。两点实现细节决定了这条命令的行为来自 postgresql 表函数参考PostgreSQL 侧实际执行的是只读事务中的COPY (SELECT ...) TO STDOUT简单的WHERE条件,!,,,,,IN会下推到 PostgreSQL 服务器执行而 join、聚合、排序和LIMIT都在 ClickHouse 侧完成。生产环境建议用 named collection 传连接参数避免把密码明文写在每条 SQL 里。文档明确说该方式 recommended for production environmentCREATE NAMED COLLECTION mypg AS host localhost, port 5432, database test, user postgresql_user, password password; INSERT INTO stackoverflow.posts SELECT * FROM postgresql(mypg, table posts)对每张表重复DESCRIBE → CREATE TABLE → INSERT INTO SELECT即可完成全库搬移。第三步增量装载与它的边界全量搬完后定期更新用同一个表函数加WHERE条件。官方示例以CreationDate为增量依据假设行被更新时该列会变化-- initial load INSERT INTO stackoverflow.posts SELECT * FROM postgresql(host, postgres, posts, postgres, password) -- incremental load INSERT INTO stackoverflow.posts SELECT * FROM postgresql(host, postgres, posts, postgres, password) WHERE CreationDate (SELECT (max(CreationDate) FROM stackoverflow.posts))增量条件列在 PostgreSQL 侧建有索引时效率更高因为WHERE会下推执行。这条路径的能力边界官方文档说得很明确纯 insert的表增量装载天然可用update可以支持前提是更新必然改动同一个水位列如上例的CreationDate。文档还提到一种用 PostgreSQLXMIN系统列transaction ID当水位检测 UPDATE 的方法但注意XMIN值会回绕且比较需要全表扫描delete无法增量处理需要全量重载complete reload表越大越难。如果你的业务有频繁的更新和删除手动路径的维护成本会明显上升这时应考虑下面的 CDC 替代路径。可选替代路径CDC 实时复制对于需要持续新鲜数据的场景官方推荐 CDCClickHouse Cloud 用 ClickPipes自建 ClickHouse 用 PeerDB。它们会自动完成初始加载并持续捕获 insert/update/delete。与手动路径的关键差异在于表结构由复制工具自动创建。ClickPipes 把 Postgres 表映射为 ReplacingMergeTree以_peerdb_version作为版本列并附加_peerdb_synced_at、_peerdb_is_deleted等列users表的自动建表示例见 Part 1。代价是CDC 复制 update/delete 时会在 ClickHouse 中产生重复行查询时需要配合FINAL等去重手段官方在 ClickPipes 去重文档中有说明。CDC 场景下排序键的定制有额外约束官方指向了专门的文档ordering keys with CDC。迁移完成后的验证与限制验证手段以文档实际给出的为准每次INSERT INTO SELECT的返回包含 Elapsed 和 Processed 行数统计可据此核对各表搬移量查询验证对 ClickHouse 新表执行与 Postgres 上相同的分析查询结果应一致。官方 Part 2: Rewriting PostgreSQL queries 用同一数据集演示了等价查询并提示Postgres 数据受外键引用完整性约束ClickHouse 侧没有这类约束因此两侧的行数和计数可能有细微差异如匿名用户行。使用限制汇总表函数批量装载适用于数百 GB 级别的数据集超大数据量应考虑 CDC 或导出 CSV/SQL 文件再经INSERT FROM INFILE、对象存储s3、gcs等函数加载官方列出的替代方式手动路径无法增量处理删除postgresql表函数支持 TLS 参数sslmode等转发给 libpq未设置时 libpq 默认值为prefer跨网络传输时按需配置查询改写不是必须的——大部分 PostgreSQL 分析查询在 ClickHouse 中无需修改即可运行但利用 ClickHouse 的聚合函数、数组函数等可以进一步简化查询。延伸阅读PostgreSQL 与 ClickHouse 概念对照Appendix分片与副本、最终一致性、ACID 支持范围、压缩对比迁移前值得通读Data Modeling: Schema design官方推荐配合本迁移使用用同一个 Stack Overflow 数据集讲解排序键选择、分区PARTITION BY注意避免超过约 100 个分区的高基数分区键和类型优化Part 2: Rewriting PostgreSQL queries 和 Part 3: Data modeling techniques数据到位后的查询改写与建模优化。【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考