跳至主要内容

[实验性]MaterializedPostgreSQL

创建一个包含 PostgreSQL 数据库中表的 ClickHouse 数据库。首先，使用引擎 MaterializedPostgreSQL 的数据库会创建 PostgreSQL 数据库的快照并加载所需的表。所需的表可以包含来自指定数据库的任何子集架构中的任何表子集。除了快照之外，数据库引擎还会获取 LSN，并且一旦表进行初始转储，它就会开始从 WAL 中提取更新。数据库创建后，不会自动将新添加到 PostgreSQL 数据库中的表添加到复制中。它们必须使用 ATTACH TABLE db.table 查询手动添加。

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

注意

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

SET allow_experimental_database_materialized_postgresql=1

创建数据库​

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]

引擎参数

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

使用示例​

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');

SHOW TABLES FROM postgres_db;

┌─name───┐
│ table1 │
└────────┘

SELECT * FROM postgresql_db.postgres_table;

动态将新表添加到复制​

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

ATTACH TABLE postgres_database.new_table;
危险

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

动态从复制中删除表​

可以从复制中删除特定表。

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

PostgreSQL 架构​

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

  1. 一个架构对应一个 MaterializedPostgreSQL 数据库引擎。需要使用设置 materialized_postgresql_schema。可以通过表名访问表。
CREATE DATABASE postgres_database
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema = 'postgres_schema';

SELECT * FROM postgres_database.table1;
  1. 对于一个 MaterializedPostgreSQL 数据库引擎，可以指定任意数量的架构和表集。需要使用设置 materialized_postgresql_tables_list。每个表都与其架构一起写入。可以通过架构名称和表名同时访问表。
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'schema1.table1,schema2.table2,schema1.table3',
         materialized_postgresql_tables_list_with_schema = 1;

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema2.table2`;

但在这种情况下，materialized_postgresql_tables_list 中的所有表都必须与其架构名称一起写入。需要 materialized_postgresql_tables_list_with_schema = 1。

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

  1. 对于一个 MaterializedPostgreSQL 数据库引擎，可以指定任意数量的架构和所有表。需要使用设置 materialized_postgresql_schema_list。
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema_list = 'schema1,schema2,schema3';

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema1.table2`;
SELECT * FROM database1.`schema2.table2`;

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

要求​

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

  2. 每个复制的表必须具有以下 副本标识 之一

  • 主键（默认）

  • 索引

postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;

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

postgres# SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;
注意

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

设置​

materialized_postgresql_tables_list​

Sets a comma-separated list of PostgreSQL database tables, which will be replicated via [MaterializedPostgreSQL](/docs/en/engines/database-engines/materialized-postgresql) database engine.

Each table can have subset of replicated columns in brackets. If subset of columns is omitted, then all columns for table will be replicated.

``` sql
materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)
```

Default value: empty list — means whole PostgreSQL database will be replicated.

materialized_postgresql_schema​

Default value: empty string. (Default schema is used)

materialized_postgresql_schema_list​

Default value: empty list. (Default schema is used)

materialized_postgresql_max_block_size​

Sets the number of rows collected in memory before flushing data into PostgreSQL database table.

Possible values:

- Positive integer.

Default value: `65536`.

materialized_postgresql_replication_slot​

A user-created replication slot. Must be used together with `materialized_postgresql_snapshot`.

materialized_postgresql_snapshot​

A text string identifying a snapshot, from which [initial dump of PostgreSQL tables](/docs/en/engines/database-engines/materialized-postgresql) will be performed. Must be used together with `materialized_postgresql_replication_slot`.

``` sql
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'table1,table2,table3';

SELECT * FROM database1.table1;
```

The settings can be changed, if necessary, using a DDL query. But it is impossible to change the setting `materialized_postgresql_tables_list`. To update the list of tables in this setting use the `ATTACH TABLE` query.

``` sql
ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;
```

materialized_postgresql_use_unique_replication_consumer_identifier​

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

注释​

逻辑复制槽位故障转移​

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

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

示例（来自 @bchrobot）

  1. 在 PostgreSQL 中配置复制槽位。

    apiVersion: "acid.zalan.do/v1"
    kind: postgresql
    metadata:
      name: acid-demo-cluster
    spec:
      numberOfInstances: 2
      postgresql:
        parameters:
          wal_level: logical
      patroni:
        slots:
          clickhouse_sync:
            type: logical
            database: demodb
            plugin: pgoutput

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

    BEGIN;
    SELECT pg_export_snapshot();

  3. 在 ClickHouse 中创建数据库

    CREATE DATABASE demodb
    ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
    SETTINGS
      materialized_postgresql_replication_slot = 'clickhouse_sync',
      materialized_postgresql_snapshot = '0000000A-0000023F-3',
      materialized_postgresql_tables_list = 'table1,table2,table3';

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

    kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'

所需权限​

  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