MaterializedPostgreSQL и MaterializedMySQL

ClickHouse имеет встроенные движки баз данных для CDC (Change Data Capture) из PostgreSQL и MySQL. Вместо ручной синхронизации данных или внешних инструментов вроде Debezium, эти движки подписываются на журнал изменений источника и автоматически реплицируют все INSERT/UPDATE/DELETE в ClickHouse.

MaterializedPostgreSQL

MaterializedPostgreSQL создаёт базу данных ClickHouse, которая является зеркалом PostgreSQL-базы через логическую репликацию (WAL):

CREATE DATABASE pg_replica
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'mydb', 'replication_user', 'secret')
SETTINGS materialized_postgresql_schema = 'public';

После создания ClickHouse:

1. Выполняет начальный снапшот всех таблиц из PostgreSQL
2. Подписывается на WAL через logical replication slot
3. Получает и применяет все изменения в реальном времени

Таблицы в ClickHouse становятся доступны через стандартные SELECT-запросы:

-- Таблица pg_replica.users автоматически синхронизируется с PostgreSQL
SELECT count() FROM pg_replica.users;

-- В PostgreSQL (требует права суперпользователя)
ALTER SYSTEM SET wal_level = logical;
-- Требуется полный перезапуск PostgreSQL (не reload!)
-- sudo systemctl restart postgresql

Shadow tables: _sign и _version

MaterializedPostgreSQL хранит данные в shadow-таблицах ReplacingMergeTree с двумя служебными колонками:

• _sign (Int8): 1 = строка активна (INSERT), -1 = строка удалена (DELETE)
• _version (UInt64): монотонно возрастающий счётчик версий для отслеживания UPDATE

-- Прямой доступ к shadow-таблице (с _sign и _version)
SELECT *, _sign, _version
FROM pg_replica.orders
ORDER BY id, _version;

При SELECT через materialized view ClickHouse автоматически фильтрует строки с _sign = 1 и возвращает только актуальные версии.

MaterializedMySQL

MaterializedMySQL работает аналогично, но использует MySQL binlog вместо PostgreSQL WAL:

CREATE DATABASE mysql_replica
ENGINE = MaterializedMySQL('mysql-host:3306', 'mydb', 'replication_user', 'secret');

ClickHouse подключается к MySQL как реплика (slave) и читает binlog в реальном времени. Начальная загрузка выполняется через mysqldump-совместимый snapshot.

-- Проверка статуса репликации
SELECT *
FROM system.replication_queue
WHERE database = 'mysql_replica';

Ограничения и мониторинг

Ограничения MaterializedPostgreSQL:

• DDL-изменения в PostgreSQL (ALTER TABLE, DROP TABLE) могут прерывать репликацию — требуется ручное вмешательство
• Таблицы без primary key не могут реплицироваться корректно
• Поддерживаются не все типы данных PostgreSQL

Ограничения MaterializedMySQL:

• DDL-изменения в MySQL частично поддерживаются (добавление колонок работает; DROP TABLE прерывает репликацию)
• JSON-тип в MySQL может требовать явного маппинга

-- Мониторинг отставания репликации
SELECT
    database,
    last_exception,
    last_exception_time,
    total_rows_read,
    total_bytes_read
FROM system.materialized_postgresql_tables;

-- Детализация по таблицам
SELECT *
FROM system.replication_queue
WHERE database LIKE 'pg_%';

Рекомендуется: настроить алерты на last_exception и last_exception_time — они первыми сигнализируют о проблемах с WAL/binlog.

Выбор инструмента: ClickPipes vs MaterializedPostgreSQL vs Debezium

Когда что выбирать:

• ClickHouse Cloud — ClickPipes Postgres CDC (GA с мая 2025).
• Self-hosted с прямой сетью PG -> CH — MaterializedPostgreSQL (этот урок).
• Self-hosted с уже существующим Kafka-стеком или необходимостью SMT/multi-sink — Debezium.

Ключевые выводы

1. MaterializedPostgreSQL и MaterializedMySQL — встроенные CDC-движки ClickHouse: подписываются на WAL/binlog источника и автоматически реплицируют все изменения.
2. PostgreSQL обязательно требует wal_level=logical (изменение требует полного перезапуска сервера, не reload).
3. Shadow-таблицы используют ReplacingMergeTree с _sign (1=active, -1=deleted) и _version для отслеживания INSERT/UPDATE/DELETE-семантики.
4. DDL-изменения в источнике (особенно DROP TABLE) могут прерывать репликацию — необходим мониторинг last_exception.
5. Для production CDC из PostgreSQL/MySQL в self-hosted: MaterializedPostgreSQL/MaterializedMySQL проще Debezium, но требует прямого доступа к сети между ClickHouse и источником.
6. Для ClickHouse Cloud рекомендуемый путь — managed ClickPipes Postgres CDC (GA с мая 2025, на базе PeerDB).