Перейти к основному содержимому
Перейти к основному содержимому

MaterializedPostgreSQL

Experimental feature. Learn more.
Not supported in ClickHouse Cloud
примечание

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

Создает базу данных ClickHouse с таблицами из базы данных PostgreSQL. Сначала база данных с движком MaterializedPostgreSQL создает снимок базы данных PostgreSQL и загружает требуемые таблицы. Требуемые таблицы могут включать любой подмножество таблиц из любого подмножества схем из указанной базы данных. Вместе с базой данных снимок получает LSN, и после завершения начального дампа таблиц начинает извлекать обновления из WAL. После создания базы данных новые добавленные таблицы в базу данных PostgreSQL автоматически не добавляются в репликацию. Их необходимо добавлять вручную с помощью запроса ATTACH TABLE db.table.

Репликация реализована с использованием протокола логической репликации PostgreSQL, который не позволяет реплицировать DDL, но позволяет знать, произошли ли изменения, нарушающие репликацию (изменения типа столбца, добавление/удаление столбцов). Такие изменения обнаруживаются, и соответствующие таблицы прекращают получать обновления. В этом случае вам следует использовать запросы ATTACH/DETACH PERMANENTLY, чтобы полностью перезагрузить таблицу. Если DDL не нарушает репликацию (например, переименование столбца), таблица будет по-прежнему получать обновления (вставка выполняется по позиции).

примечание

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

Создание базы данных​

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

  • host:port — конечная точка сервера PostgreSQL.
  • database — имя базы данных PostgreSQL.
  • user — пользователь PostgreSQL.
  • password — пароль пользователя.

Пример использования​

Динамическое добавление новых таблиц в репликацию​

После создания базы данных MaterializedPostgreSQL она не автоматически обнаруживает новые таблицы в соответствующей базе данных PostgreSQL. Такие таблицы можно добавлять вручную:

осторожно

Перед версией 22.1 добавление таблицы в репликацию оставляло неремонтируемый временный слот репликации (названный {db_name}_ch_replication_slot_tmp). Если вы добавляете таблицы в ClickHouse до версии 22.1, убедитесь, что вы удалили его вручную (SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). В противном случае использование диска будет расти. Эта проблема исправлена в 22.1.

Динамическое удаление таблиц из репликации​

Можно удалить конкретные таблицы из репликации:

Схема PostgreSQL​

Схема PostgreSQL schema может быть настроена 3 способами (начиная с версии 21.12).

  1. Одна схема для одного движка базы данных MaterializedPostgreSQL. Требуется использовать настройку materialized_postgresql_schema. Таблицы доступа через имя таблицы:
  1. Любое количество схем с указанным набором таблиц для одного движка базы данных MaterializedPostgreSQL. Требуется использовать настройку materialized_postgresql_tables_list. Каждая таблица записывается вместе с ее схемой. Таблицы доступа через имя схемы и имя таблицы одновременно:

Но в этом случае все таблицы в materialized_postgresql_tables_list должны быть записаны с именем схемы. Требуется materialized_postgresql_tables_list_with_schema = 1.

Предупреждение: для этого случая точки в имени таблицы не допускаются.

  1. Любое количество схем с полным набором таблиц для одного движка базы данных MaterializedPostgreSQL. Требуется использовать настройку materialized_postgresql_schema_list.

Предупреждение: для этого случая точки в имени таблицы не допускаются.

Требования​

  1. Настройка wal_level должна иметь значение logical, а параметр max_replication_slots должен иметь значение не менее 2 в файле конфигурации PostgreSQL.

  2. Каждая реплицируемая таблица должна иметь один из следующих replica identity:

  • первичный ключ (по умолчанию)

  • индекс

Первичный ключ всегда проверяется первым. Если он отсутствует, то проверяется индекс, определенный как индекс идентичности реплики. Если индекс используется как идентичность реплики, в таблице должен быть только один такой индекс. Вы можете проверить, какой тип используется для конкретной таблицы с помощью следующей команды:

примечание

Репликация значений TOAST не поддерживается. Будет использоваться значение по умолчанию для типа данных.

Настройки​

materialized_postgresql_tables_list​

Устанавливает список таблиц базы данных PostgreSQL, разделенный запятыми, которые будут реплицироваться через движок базы данных MaterializedPostgreSQL.

Каждая таблица может иметь подмножество столбцов для репликации в скобках. Если подмножество столбцов опущено, то все столбцы таблицы будут реплицироваться.

Значение по умолчанию: пустой список — означает, что вся база данных PostgreSQL будет реплицирована.

materialized_postgresql_schema​

Значение по умолчанию: пустая строка. (Используется схема по умолчанию)

materialized_postgresql_schema_list​

Значение по умолчанию: пустой список. (Используется схема по умолчанию)

materialized_postgresql_max_block_size​

Устанавливает количество строк, собираемых в памяти перед сбросом данных в таблицу базы данных PostgreSQL.

Возможные значения:

  • Положительное целое число.

Значение по умолчанию: 65536.

materialized_postgresql_replication_slot​

Созданный пользователем слот репликации. Должен использоваться вместе с materialized_postgresql_snapshot.

materialized_postgresql_snapshot​

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

Настройки могут быть изменены, если это необходимо, с помощью DDL-запроса. Но невозможно изменить настройку materialized_postgresql_tables_list. Чтобы обновить список таблиц в этой настройке, используйте запрос ATTACH TABLE.

materialized_postgresql_use_unique_replication_consumer_identifier​

Используйте уникальный идентификатор потребителя репликации для репликации. Значение по умолчанию: 0. Если установлено в 1, позволяет настроить несколько таблиц MaterializedPostgreSQL, указывающих на одну и ту же таблицу PostgreSQL.

Заметки​

Переключение слота логической репликации​

Слоты логической репликации, которые существуют на первичном, недоступны на резервных репликах. Поэтому если произойдет переключение, новый первичный (старый физический резервный) не будет знать о любых слотах, которые существовали со старым первичным. Это приведет к нарушению репликации из PostgreSQL. Решение этой проблемы заключается в том, чтобы управлять слотами репликации самостоятельно и определить постоянный слот репликации (некоторую информацию можно найти здесь). Вам нужно будет передать имя слота через настройку materialized_postgresql_replication_slot, и он должен быть экспортирован с опцией EXPORT SNAPSHOT. Идентификатор снимка необходимо передать через настройку materialized_postgresql_snapshot.

Обратите внимание, что вы должны использовать это только в том случае, если это действительно необходимо. Если в этом нет реальной необходимости или полного понимания причин, лучше позволить движку таблицы создавать и управлять своим собственным слотом репликации.

Пример (от @bchrobot)

  1. Настройте слот репликации в PostgreSQL.

  2. Дождитесь готовности слота репликации, затем начните транзакцию и экспортируйте идентификатор снимка транзакции:

  3. В ClickHouse создайте базу данных:

  4. Завершите транзакцию PostgreSQL после подтверждения репликации в базе данных ClickHouse. Убедитесь, что репликация продолжается после переключения:

Необходимые разрешения​

  1. CREATE PUBLICATION — привилегия создавать запрос.

  2. CREATE_REPLICATION_SLOT — привилегия репликации.

  3. pg_drop_replication_slot — привилегия репликации или суперпользователя.

  4. DROP PUBLICATION — владелец публикации (username в движке MaterializedPostgreSQL).

Можно избежать выполнения команд 2 и 3 и получения этих разрешений. Используйте настройки materialized_postgresql_replication_slot и materialized_postgresql_snapshot. Но с большой осторожностью.

Доступ к таблицам:

  1. pg_publication

  2. pg_replication_slots

  3. pg_publication_tables