Движок таблицы MaterializedPostgreSQL

Пользователям ClickHouse Cloud рекомендуется использовать ClickPipes для репликации PostgreSQL в ClickHouse. ClickPipes изначально поддерживает высокопроизводительный CDC (фиксация изменений данных) для PostgreSQL.

Создает таблицу ClickHouse с начальным дампом данных из таблицы PostgreSQL и запускает процесс репликации, то есть выполняет фоновую задачу, применяющую новые изменения по мере их появления в таблице PostgreSQL в удаленной базе данных PostgreSQL.

Этот движок таблицы является экспериментальным. Чтобы использовать его, установите allow_experimental_materialized_postgresql_table в значение 1 в файлах конфигурации или с помощью команды SET:

SET allow_experimental_materialized_postgresql_table=1

Если требуется реплицировать несколько таблиц, настоятельно рекомендуется использовать движок базы данных MaterializedPostgreSQL вместо движка таблицы и параметр materialized_postgresql_tables_list, который задает таблицы для репликации (также появится возможность добавить schema базы данных). Это значительно эффективнее с точки зрения нагрузки на CPU, количества соединений и числа слотов репликации в удаленной базе данных PostgreSQL.

Создание таблицы

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

Параметры движка

host:port — адрес сервера PostgreSQL.
database — имя удалённой базы данных.
table — имя удалённой таблицы.
user — пользователь PostgreSQL.
password — пароль пользователя.

TLS/SSL

Параметры TLS/SSL передаются в libpq и могут быть заданы через именованную коллекцию или в качестве завершающих аргументов движка в формате ключ-значение: sslmode (disable, allow, prefer, require, verify-ca или verify-full; если не задан, используется значение prefer по умолчанию в libpq), а также сертификаты и ключ в одном из двух вариантов. sslrootcert (CA‑сертификат), sslcert (клиентский сертификат) и sslkey (закрытый ключ клиента) — это пути к локальным файлам сервера; они принимаются только из именованной коллекции, определённой в файле конфигурации сервера. Вместо них sslrootcert_pem, sslcert_pem и sslkey_pem принимают буквальное содержимое соответствующего файла, могут быть указаны в SQL и маскируются в журналах и запросах SHOW так же, как пароль.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----')
PRIMARY KEY key;

Параметры TLS/SSL входят в параметры подключения к PostgreSQL и задаются при создании таблицы.

Требования

Параметр wal_level должен иметь значение logical, а параметр max_replication_slots — значение не менее 2 в конфигурационном файле PostgreSQL.
Таблица с движком MaterializedPostgreSQL должна иметь первичный ключ — тот же, что и индекс replica identity (по умолчанию это первичный ключ) таблицы PostgreSQL (см. подробнее об индексе replica identity).
Допускается только база данных Atomic.
Движок таблицы MaterializedPostgreSQL работает только с PostgreSQL версии >= 11, поскольку для его реализации требуется функция PostgreSQL pg_replication_slot_advance.

Виртуальные столбцы

_version — Счётчик транзакций. Тип: UInt64.
_sign — Метка удаления. Тип: Int8. Возможные значения:
- 1 — строка не удалена,
- -1 — строка удалена.

Эти столбцы не нужно указывать при создании таблицы. Они всегда доступны в запросе SELECT. Столбец _version соответствует позиции LSN в WAL, поэтому его можно использовать, чтобы проверить, насколько актуальна репликация.

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;

Значения TOAST реплицируются. Когда PostgreSQL при обновлении отправляет неизменённую ссылку TOAST, существующее значение сохраняется. Если столбец replica identity TOAST не изменился, PostgreSQL должен отправить старый кортеж ключа, иначе строку невозможно идентифицировать.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Движок таблицы MaterializedPostgreSQL

Создание таблицы

TLS/SSL

Требования

Виртуальные столбцы

​Создание таблицы

​TLS/SSL

​Требования

​Виртуальные столбцы

Создание таблицы

TLS/SSL

Требования

Виртуальные столбцы