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

MaterializedPostgreSQL

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

Пользователям ClickHouse Cloud рекомендуется использовать ClickPipes для репликации PostgreSQL в ClickHouse. Это нативно поддерживает высокопроизводительный Change Data Capture (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 может быть настроена тремя способами (начиная с версии 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