技術文章 » Clickhouse 實現 MaterializedPostgreSQL

Clickhouse 實現 MaterializedPostgreSQL

2022-07-27 18:00:45

開發環境： 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 不中斷複製（例如，重新命名列），表仍將接收更新（插入按位元置完成）。

開啟遷移引擎

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 ...]

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 資料庫引擎進行復制。

預設值：空列表 — 表示將複製整個 PostgreSQL 資料庫。

materialized_postgresql_schema#

預設值：空字串。 （使用預設模式）

materialized_postgresql_schema_list#

預設值：空列表。 （使用預設模式）

materialized_postgresql_allow_automatic_update#

不要在 22.1 版本之前使用此設定。

當檢測到架構更改時，允許在後臺重新載入表。 PostgreSQL 端的 DDL 查詢不會通過 ClickHouse MaterializedPostgreSQL 引擎進行復制，因為 PostgreSQL 邏輯複製協定不允許這樣做，但 DDL 更改的事實是 交易檢測。 在這種情況下，預設行為是在檢測到 DDL 後停止複製這些表。 但是，如果啟用此設定，則不會停止這些表的複製，而是通過資料庫快照在後臺重新載入它們，而不會丟失資料，並且將為它們繼續複製。

可能的值：

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

預設值：「0」。

materialized_postgresql_max_block_size#

設定在將資料重新整理到 PostgreSQL 資料庫表之前在記憶體中收集的行數。

可能的值：

  • 正整數。

預設值：65536。

materialized_postgresql_replication_slot#

使用者建立的複製槽。 必須與 `materialized_postgresql_snapshot` 一起使用。

materialized_postgresql_snapshot#

標識快照的文字字串，將從中執行 PostgreSQL 表的初始轉儲。 必須與 materialized_postgresql_replication_slot 一起使用。

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 查詢。

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 表的副本標識索引（預設：主鍵）相同（參見 副本標識索引的詳細資訊）。
  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;