Clickhouse 實現 MaterializedPostgreSQL

Clickhouse 實現 MaterializedPostgreSQL

開發環境：macOS 12.4 + docker

一、環境搭建

docker-compose.yml

services:
  postgis:
    image: postgis/postgis:13-3.1
    restart: always
    expose:
      - "5432"
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: {POSTGRES_USER}
      POSTGRES_PASSWORD: {POSTGRES_PASSWORD}
      POSTGRES_DB: mydb
      POSTGRES_HOST: localhost
      POSTGRES_HOST_AUTH_METHOD: trust
  clickhouse:
    image: clickhouse/clickhouse-server:22.6.4
    restart: always
    expose:
      - 9009
      - 8123
    ports:
      - "9009:9009"
      - "8123:8123"
    environment:
      platform: linux/amd64
      ulimit nofile: 262144:262144

二、PgSQL 環境設置

要求

  1. wal_level 設置必須有一個值 logical 和 max_replication_slots 參數必須有一個值至少 2 在 PostgreSQL 配置文件。
  2. 每個複製的表必須具有以下 副本標識之一：
  • primary key (by default)
  • index

wal_level

wal_level決定多少資訊寫入到 WAL 中。默認值是replica，它會寫入足夠的數據以支援WAL歸檔和複製，包括在後備伺服器上運行只讀查詢。minimal會去掉除從崩潰或者立即關機中進行恢復所需的資訊之外的所有記錄。最後，logical會增加支援邏輯解碼所需的資訊。每個層次包括所有更低層次記錄的資訊。這個參數只能在伺服器啟動時設置。

在minimal級別中，某些批量操作的 WAL 日誌可以被安全地跳過，這可以使那些操作更快（見populate-pitr）。這種優化可以應用的操作包括：

  • CREATE TABLE AS
  • CREATE INDEX
  • CLUSTER
  • COPY到在同一個事務中被創建或截斷的表中

但最少的 WAL 不會包括足夠的資訊來從基礎備份和 WAL 日誌中重建數據，因此，要啟用 WAL 歸檔（archive_mode）和流複製，必須使用replica或更高級別。

在logical層，與replica相同的資訊會被記錄，外加上 允許從 WAL 抽取邏輯修改集所需的資訊。使用級別 logical將增加 WAL 容量，特別是如果為了REPLICA IDENTITY FULL配置了很多表並且執行了很多UPDATE和DELETE 語句時。

在 9.6 之前的版本中，這個參數也允許值archive和hot_standby。現在仍然接受這些值，但是它們會被映射到replica。

ALTER SYSTEM SET wal_level = logical;

max_replication_slots

指定伺服器可以支援的複製槽最大數量。默認值為10。這個參數只能在伺服器啟動時設置。將它設置為一個比當前已有複製槽要少的值會阻礙伺服器啟動。此外，要允許使用複製槽， wal_level必須被設置為replica或 更高。

ALTER SYSTEM SET max_replication_slots = 2;

三、Clickhouse 遷移

資料庫引擎#

使用 PostgreSQL 資料庫中的表創建 ClickHouse 資料庫。首先，帶有引擎 MaterializedPostgreSQL 的資料庫創建 PostgreSQL 資料庫的快照並載入所需的表。所需表可以包括來自指定資料庫的任何模式子集的任何錶子集。隨著快照資料庫引擎獲取 LSN，一旦執行了表的初始轉儲 – 它就開始從 WAL 中提取更新。創建資料庫後，PostgreSQL 資料庫中新添加的表不會自動添加到複製中。它們必須通過 ATTACH TABLE db.table 查詢手動添加。

複製是使用 PostgreSQL 邏輯複製協議實現的，該協議不允許複製 DDL，但允許知道是否發生了複製中斷更改（列類型更改、添加/刪除列）。檢測到此類更改並根據表格停止接收更新。如果打開所需的設置，此類表可以在後台自動重新載入（可以從 22.1 開始使用）。目前最安全的方法是使用 ATTACH/DETACH 查詢來完全重新載入表。如果 DDL 不中斷複製（例如，重命名列），表仍將接收更新（插入按位置完成）。

1、開啟遷移引擎

SET allow_experimental_database_materialized_postgresql=1

2、創建資料庫

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

Engine 參數

  • host:port — PostgreSQL 伺服器地址
  • database — PostgreSQL 資料庫名稱
  • user — PostgreSQL 用戶名
  • password — PostgreSQL 用戶密碼

使用示例

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('localhost:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list='table1,table2,table3',materialized_postgresql_allow_automatic_update = 1;

SHOW TABLES FROM postgres_db;

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

SELECT * FROM postgres_db.table1;

設置

materialized_postgresql_tables_list#
設置 PostgreSQL 資料庫表的逗號分隔列表，將通過 [MaterializedPostgreSQL](/docs/en/engines/database-engines/materialized-postgresql) 資料庫引擎進行複製。

默認值：空列表 — 表示將複製整個 PostgreSQL 資料庫。
materialized_postgresql_schema#
默認值：空字元串。 （使用默認模式）
materialized_postgresql_schema_list#
默認值：空列表。 （使用默認模式）
materialized_postgresql_allow_automatic_update#
不要在 22.1 版本之前使用此設置。

當檢測到架構更改時，允許在後台重新載入表。 PostgreSQL 端的 DDL 查詢不會通過 ClickHouse [MaterializedPostgreSQL](/docs/en/engines/database-engines/materialized-postgresql) 引擎進行複製，因為 PostgreSQL 邏輯複製協議不允許這樣做，但 DDL 更改的事實是 交易檢測。 在這種情況下，默認行為是在檢測到 DDL 後停止複製這些表。 但是，如果啟用此設置，則不會停止這些表的複製，而是通過資料庫快照在後台重新載入它們，而不會丟失數據，並且將為它們繼續複製。

可能的值：

- 0 — 當檢測到架構更改時，表不會在後台自動更新。
- 1 — 當檢測到架構更改時，表會在後台自動更新。

默認值：「0」。
materialized_postgresql_max_block_size#
設置在將數據刷新到 PostgreSQL 資料庫表之前在記憶體中收集的行數。

可能的值：

- 正整數。

默認值：`65536`。
materialized_postgresql_replication_slot#
用戶創建的複製槽。 必須與 `materialized_postgresql_snapshot` 一起使用。
materialized_postgresql_snapshot#
標識快照的文本字元串，將從中執行 [PostgreSQL 表的初始轉儲](/docs/en/engines/database-engines/materialized-postgresql)。 必須與 `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;
```

如有必要，可以使用 DDL 查詢更改設置。 但是不可能更改設置 `materialized_postgresql_tables_list`。 要更新此設置中的表列表，請使用 `ATTACH TABLE` 查詢。

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

動態添加新表到複製

MaterializedPostgreSQL 資料庫創建後，它不會自動檢測相應 PostgreSQL 資料庫中的新表。 可以手動添加此類表：

ATTACH TABLE postgres_db.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_db.table_to_remove;

表引擎#

使用 PostgreSQL 表的初始數據轉儲創建 ClickHouse 表並啟動複製過程，即執行後台作業以應用在遠程 PostgreSQL 資料庫中的 PostgreSQL 表上發生的新更改。

如果需要多張表，強烈建議使用 MaterializedPostgreSQL 資料庫引擎代替表引擎並使用materialized_postgresql_tables_list 設置，它指定要複製的表（也可以添加資料庫 schema）。在遠程 PostgreSQL 資料庫中的 CPU、更少的連接和更少的複製槽方面會好得多。

創建表

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('localhost:5432', 'postgres_database', 'postgresql_replica', 'postgres_user', 'postgres_password')
PRIMARY KEY key;
Engine 參數
  • host:port — PostgreSQL 伺服器地址
  • database — PostgreSQL 資料庫名稱
  • table – PostgreSQL 表名
  • user — PostgreSQL 用戶名
  • password — PostgreSQL 用戶密碼
要求
  1. wal_level 設置必須有一個值 logical 和 max_replication_slots 參數必須至少有一個值 2 在 PostgreSQL 配置文件中。
  2. 使用 MaterializedPostgreSQL 引擎的表必須有一個主鍵——與 PostgreSQL 表的副本標識索引（默認：主鍵）相同（參見 [副本標識索引的詳細資訊](//clickhouse.com） /docs/en/engines/database-engines/materialized-postgresql#requirements)）。
  3. 只允許使用資料庫 Atomic。
虛擬列
  • _version — 事務計數器。類型： UInt64.
  • _sign — 刪除標記。類型：Int8。可能的值：
    • 1 — 未刪除行，
    • -1 — 行被刪除。

創建表時不需要添加這些列。它們始終可以在 SELECT 查詢中訪問。 _version 列等於 WAL 中的 LSN 位置，因此它可能用於檢查複製的最新程度。

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_replica', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

SELECT key, value, _version FROM postgresql_db.postgresql_replica;
Tags:

VirMach 便宜 VPS

QNews

QNews