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

物化PostgreSQL

Experimental feature. Learn more.
Not supported in ClickHouse Cloud
备注

建议ClickHouse Cloud用户使用ClickPipes进行PostgreSQL到ClickHouse的复制。这原生支持PostgreSQL的高性能变更数据捕获（CDC）。

创建一个ClickHouse数据库，包含来自PostgreSQL数据库的表。首先，使用引擎MaterializedPostgreSQL的数据库创建PostgreSQL数据库的快照并加载所需的表。所需的表可以包含指定数据库的任何模式中的任何表的子集。通过快照，数据库引擎获取LSN，一旦初始表的转储完成，就开始从WAL中提取更新。创建数据库后，新添加到PostgreSQL数据库的表不会自动添加到复制中。必须使用ATTACH TABLE db.table查询手动添加。

复制使用PostgreSQL逻辑复制协议实现，该协议不允许复制DDL，但允许检测是否发生了破坏复制的更改（列类型更改、添加/删除列）。此类更改被检测到，相应的表停止接收更新。在这种情况下，您应该使用ATTACH/DETACH PERMANENTLY查询来完全重新加载表。如果DDL没有破坏复制（例如，重命名列），表仍将接收更新（插入是按位置进行的）。

备注

该数据库引擎是实验性的。要使用它，请在配置文件中或通过使用SET命令将allow_experimental_database_materialized_postgresql设置为1：

创建数据库​

引擎参数

  • host:port — PostgreSQL服务器端点。
  • database — PostgreSQL数据库名称。
  • user — PostgreSQL用户。
  • password — 用户密码。

使用示例​

动态添加新表到复制​

在创建MaterializedPostgreSQL数据库后，它不会自动检测到相应PostgreSQL数据库中的新表。这些表可以手动添加：

危险

在22.1版本之前，将表添加到复制中会留下未删除的临时复制槽（命名为{db_name}_ch_replication_slot_tmp）。如果在ClickHouse 22.1之前的版本中附加表，请确保手动删除它（SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')）。否则，磁盘使用量将增加。此问题已在22.1中修复。

动态从复制中移除表​

可以从复制中移除特定表：

PostgreSQL模式​

PostgreSQL 模式可以通过三种方式进行配置（从21.12版本开始）。

  1. 一个模式对应一个MaterializedPostgreSQL数据库引擎。需要使用设置materialized_postgresql_schema。 通过仅使用表名访问表：
  1. 任何数量的模式和指定表的集合对应一个MaterializedPostgreSQL数据库引擎。需要使用设置materialized_postgresql_tables_list。每个表都应与其模式一起书写。 通过模式名和表名同时访问表：

但在这种情况下，materialized_postgresql_tables_list中的所有表必须写为其模式名。 需要materialized_postgresql_tables_list_with_schema = 1。

警告：在这种情况下，不允许在表名中使用点。

  1. 任何数量的模式和完整的表集对应一个MaterializedPostgreSQL数据库引擎。需要使用设置materialized_postgresql_schema_list。

警告：在这种情况下，不允许在表名中使用点。

要求​

  1. PostgreSQL配置文件中的wal_level设置必须为logical，并且max_replication_slots参数的值至少为2。

  2. 每个被复制的表必须有以下之一的副本身份：

  • 主键（默认）

  • 索引

主键始终首先进行检查。如果不存在，则检查定义为副本身份索引的索引。 如果索引用作副本身份，则表中只能有一个这样的索引。 您可以使用以下命令检查特定表使用的是哪种类型：

备注

不支持TOAST值的复制。将使用数据类型的默认值。

设置​

materialized_postgresql_tables_list​

设置要通过MaterializedPostgreSQL数据库引擎复制的PostgreSQL数据库表的以逗号分隔的列表。

每个表可以在括号中具有复制列的子集。如果省略列的子集，则将复制该表的所有列。

默认值：空列表——意味着将复制整个PostgreSQL数据库。

materialized_postgresql_schema​

默认值：空字符串。（使用默认模式）

materialized_postgresql_schema_list​

默认值：空列表。（使用默认模式）

materialized_postgresql_max_block_size​

设置在将数据刷新到PostgreSQL数据库表之前，在内存中收集的行数。

可能的值：

  • 正整数。

默认值：65536。

materialized_postgresql_replication_slot​

用户创建的复制槽。必须与materialized_postgresql_snapshot一起使用。

materialized_postgresql_snapshot​

标识快照的文本字符串，从该快照将执行初始PostgreSQL表的转储。必须与materialized_postgresql_replication_slot一起使用。

如果需要，可以使用DDL查询更改设置。但是不可能更改设置materialized_postgresql_tables_list。要更新该设置中的表列表，请使用ATTACH TABLE查询。

materialized_postgresql_use_unique_replication_consumer_identifier​

为复制使用唯一的复制消费者标识符。默认值：0。 如果设置为1，则允许设置多个指向相同PostgreSQL表的MaterializedPostgreSQL表。

注意事项​

逻辑复制槽的故障转移​

存在于主服务器上的逻辑复制槽在备用副本上不可用。 因此，如果发生故障转移，新主（旧的物理备用）将不知道旧主所存在的任何槽。这将导致从PostgreSQL到ClickHouse的复制中断。 解决此问题的方法是自己管理复制槽并定义一个永久复制槽（一些信息可以在这里找到）。您需要通过materialized_postgresql_replication_slot设置传递槽名称，并且必须使用EXPORT SNAPSHOT选项导出。快照标识符需要通过materialized_postgresql_snapshot设置传递。

请注意，这仅在实际需要时使用。如果没有真正的需求或完全理解原因，则最好允许表引擎创建和管理自己的复制槽。

示例（来自 @bchrobot）

  1. 在PostgreSQL中配置复制槽。

  2. 等待复制槽准备就绪，然后开始一个事务并导出事务快照标识符：

  3. 在ClickHouse创建数据库：

  4. 一旦确认到ClickHouse数据库的复制后，结束PostgreSQL事务。验证复制在故障转移后继续：

所需权限​

  1. CREATE PUBLICATION — 创建查询权限。

  2. CREATE_REPLICATION_SLOT — 复制权限。

  3. pg_drop_replication_slot — 复制权限或超级用户。

  4. DROP PUBLICATION — 该发布的所有者（在MaterializedPostgreSQL引擎中为username）。

可以避免执行2和3命令并拥有那些权限。使用设置materialized_postgresql_replication_slot和materialized_postgresql_snapshot。但是需谨慎。

访问表：

  1. pg_publication

  2. pg_replication_slots

  3. pg_publication_tables