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

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 можно настроить тремя способами (начиная с версии 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