Можно ли реплицировать данные из нескольких экземпляров Postgres в один или несколько сервисов ClickHouse?
- Первичный ключ: Самый простой вариант — задать для таблицы первичный ключ. Он обеспечивает уникальный идентификатор для каждой строки, что крайне важно для отслеживания обновлений и удалений. В этом случае для REPLICA IDENTITY можно оставить значение
DEFAULT(поведение по умолчанию). - Replica Identity: Если у таблицы нет первичного ключа, можно задать replica identity. Для replica identity можно установить значение
FULL, и тогда для идентификации изменений будет использоваться вся строка целиком. Либо, если у таблицы есть уникальный индекс, можно использовать его и затем задать для REPLICA IDENTITY значениеUSING INDEX index_name. Чтобы установить для replica identity значение FULL, используйте следующую SQL-команду:
REPLICA IDENTITY FULL может влиять на производительность, а также ускорять рост WAL, особенно для таблиц без первичного ключа и с частыми обновлениями или удалениями, поскольку в этом случае для каждого изменения требуется записывать больше данных. Если у вас есть вопросы или вам нужна помощь с настройкой первичных ключей или replica identity для ваших таблиц, обратитесь в нашу службу поддержки.
Важно отметить, что если не задан ни первичный ключ, ни replica identity, ClickPipes не сможет реплицировать изменения для этой таблицы, и в процессе репликации вы можете столкнуться с ошибками. Поэтому перед настройкой ClickPipe рекомендуется проверить схемы таблиц и убедиться, что они соответствуют этим требованиям.
Да, партиционированные таблицы поддерживаются из коробки, если для них определены PRIMARY KEY или REPLICA IDENTITY. PRIMARY KEY и REPLICA IDENTITY должны быть заданы как для родительской таблицы, так и для её партиций. Подробнее об этом можно прочитать здесь.
Да. О способах, которыми ClickPipes может подключаться к источнику данных, недоступному из публичного интернета, см. в разделе Сетевое взаимодействие ClickPipes.
ClickPipes для Postgres фиксирует и INSERT, и UPDATE из Postgres как новые строки с разными версиями в ClickHouse (с использованием столбца версий _peerdb_). Движок таблицы ReplacingMergeTree периодически выполняет дедупликацию в фоновом режиме на основе ключа сортировки (столбцов ORDER BY), оставляя только строку с последней версией _peerdb_.
DELETE из Postgres передаются как новые строки с пометкой об удалении (с использованием столбца _peerdb_is_deleted). Поскольку дедупликация выполняется асинхронно, временно вы можете видеть дубликаты. Чтобы это исправить, дедупликацию нужно учитывать на уровне запроса.
Также обратите внимание: по умолчанию Postgres не отправляет значения столбцов, которые не входят в первичный ключ или replica identity, при операциях DELETE. Если вы хотите фиксировать полные данные строки при DELETE, можно установить REPLICA IDENTITY в значение FULL.
Подробнее см.:
- Рекомендации по использованию движка таблицы ReplacingMergeTree
- Блог о внутренних механизмах CDC (фиксация изменений данных) из Postgres в ClickHouse
-
Внезапные всплески активности в базе данных
- Крупные пакетные обновления, массовые вставки или существенные изменения схемы могут быстро сгенерировать большой объем WAL-данных.
- Слот репликации будет удерживать эти записи WAL, пока они не будут потреблены, что приведет к временному увеличению его размера.
-
Длительные транзакции
- Открытая транзакция заставляет Postgres сохранять все сегменты WAL, созданные с момента ее начала, что может значительно увеличить размер слота.
- Установите
statement_timeoutиidle_in_transaction_session_timeoutв разумные значения, чтобы транзакции не оставались открытыми бесконечно долго:Используйте этот запрос, чтобы выявить необычно долгие транзакции.
-
Операции обслуживания или служебные утилиты (например,
pg_repack)- Такие инструменты, как
pg_repack, могут полностью переписывать таблицы, создавая большой объем WAL-данных за короткое время. - Планируйте такие операции на периоды низкой нагрузки или внимательно отслеживайте использование WAL во время их выполнения.
- Такие инструменты, как
-
VACUUM и VACUUM ANALYZE
- Хотя эти операции необходимы для нормальной работы базы данных, они могут создавать дополнительный WAL-трафик, особенно при сканировании больших таблиц.
- Рассмотрите возможность настройки параметров autovacuum или планируйте ручные операции VACUUM на часы минимальной нагрузки.
-
Потребитель репликации не читает слот
- Если ваш CDC-конвейер (например, ClickPipes) или другой потребитель репликации останавливается, ставится на паузу или аварийно завершается, WAL-данные будут накапливаться в слоте.
- Убедитесь, что ваш конвейер работает непрерывно, и проверьте журналы на наличие ошибок подключения или аутентификации.
Могу ли я задать собственное сопоставление типов данных при репликации данных из Postgres в ClickHouse?
json и jsonb реплицируются в ClickHouse как тип String из-за несовместимости с нативным типом JSON. Например:
- PostgreSQL допускает на верхнем уровне любое корректное значение JSON (строки, числа, массивы), тогда как тип JSON в ClickHouse поддерживает только объекты.
- Ключи, содержащие точки (например, “app.kubernetes.io/name”), тип JSON в ClickHouse также интерпретирует как вложенные пути, что может изменить структуру данных.
- Для sync: если процесс отменяется в середине, значение confirmed_flush_lsn в Postgres не продвигается, поэтому следующий sync начнется с той же позиции, что и прерванный, обеспечивая согласованность данных.
- Для normalize: порядок вставки в ReplacingMergeTree обеспечивает дедупликацию.
snapshot number of tables in parallel или указать собственный индексированный столбец партиционирования для больших таблиц.
Вы можете поручить ClickPipes управление публикациями (для этого потребуются дополнительные разрешения) или создать их самостоятельно. Если публикациями управляет ClickPipes, мы автоматически добавляем и удаляем таблицы по мере редактирования пайпа. Если вы управляете ими самостоятельно, внимательно определяйте состав публикаций, чтобы включать только те таблицы, которые нужно реплицировать, — лишние таблицы замедляют декодирование WAL в Postgres.
Если вы включаете в публикацию какую-либо таблицу, убедитесь, что у неё есть либо первичный ключ, либо REPLICA IDENTITY FULL. Если в базе данных есть таблицы без первичного ключа, создание публикации для всех таблиц приведёт к сбоям операций DELETE и UPDATE для таких таблиц.
Чтобы найти таблицы без первичного ключа в вашей базе данных, можно использовать следующий запрос:
-
Исключить таблицы без первичного ключа из ClickPipes:
Создайте публикацию только для таблиц, у которых есть первичный ключ:
-
Включить таблицы без первичного ключа в ClickPipes:
Если вы хотите включить таблицы без первичного ключа, для них нужно установить replica identity в
FULL. Это обеспечит корректную работу операций UPDATE и DELETE:
- Минимум: установите
max_slot_wal_keep_sizeтак, чтобы сохранялся как минимум двухдневный объём WAL. - Для крупных баз данных (высокий объём транзакций): сохраняйте как минимум в 2–3 раза больше, чем пиковый суточный объём генерации WAL.
- Для сред с ограниченным объёмом хранилища: настраивайте этот параметр осторожно, чтобы избежать переполнения диска и при этом обеспечить стабильность репликации.
Для PostgreSQL 10+
Для PostgreSQL 9.6 и более ранних версий:
- Выполните приведённый выше запрос в разное время суток, особенно в периоды высокой транзакционной нагрузки.
- Рассчитайте, какой объём WAL генерируется за 24 часа.
- Умножьте это число на 2 или 3, чтобы обеспечить достаточный запас хранения.
- Установите для
max_slot_wal_keep_sizeполученное значение в МБ или ГБ.
Пример
ReceiveMessage — это функция в протоколе logical decoding Postgres, которая читает сообщения из потока репликации. Ошибка EOF (End of File) означает, что соединение с сервером Postgres было неожиданно закрыто при попытке чтения из потока репликации.
Это устранимая и совершенно нефатальная ошибка. ClickPipes автоматически попытается переподключиться и возобновить процесс репликации.
Это может происходить по нескольким причинам:
- Проблемы с сетью: Временные сбои в сети могут привести к разрыву соединения.
- Перезапуск сервера Postgres: Если сервер Postgres перезапускается или аварийно завершает работу, соединение будет потеряно.
max_slot_wal_keep_size в вашей базе данных PostgreSQL (например, всего несколько гигабайт). Мы рекомендуем увеличить это значение. Подробнее о настройке max_slot_wal_keep_size см. в этом разделе. В идеале следует установить значение не менее 200 ГБ, чтобы слот репликации не становился недействительным.
В редких случаях мы наблюдали эту проблему даже тогда, когда max_slot_wal_keep_size не был настроен. Это может быть связано со сложной и редкой ошибкой в PostgreSQL, хотя точная причина по-прежнему неясна.
В ClickHouse возникают ошибки нехватки памяти (OOM) во время ингестии данных через мой ClickPipe. Можете помочь?
-
Распространённый способ оптимизации JOIN — если у вас есть
LEFT JOIN, в котором таблица справа очень велика. В этом случае перепишите запрос, используяRIGHT JOIN, и переместите более крупную таблицу в левую часть. Это позволит планировщику запросов эффективнее использовать память. -
Ещё один способ оптимизации JOIN — явно отфильтровать таблицы с помощью
subqueriesилиCTEs, а затем выполнитьJOINмежду этими подзапросами. Это даёт планировщику подсказки о том, как эффективнее фильтровать строки и выполнятьJOIN.
invalid snapshot identifier возникает, когда между ClickPipes и вашей базой данных Postgres происходит разрыв соединения. Это может случиться из-за тайм-аутов шлюза, перезапусков базы данных или других временных проблем.
Рекомендуется не выполнять никаких операций, способных нарушить работу, таких как обновления или перезапуски базы данных Postgres, пока идет начальная загрузка, а также убедиться, что сетевое соединение с базой данных стабильно.
Чтобы устранить эту проблему, вы можете запустить повторную синхронизацию из интерфейса ClickPipes. Это перезапустит процесс начальной загрузки с самого начала.
Удаление публикации в Postgres нарушит работу подключения ClickPipe, поскольку публикация нужна ClickPipe, чтобы получать изменения из источника. В этом случае вы обычно получите оповещение об ошибке о том, что публикация больше не существует.
Чтобы восстановить ClickPipe после удаления публикации:
- Создайте в Postgres новую публикацию с тем же именем и нужными таблицами
- Нажмите кнопку ‘Resync tables’ на вкладке Settings в вашем ClickPipe
Мне нужно хранить в ClickHouse полную историю данных, даже если они удаляются из исходной базы данных Postgres. Могу ли я полностью игнорировать операции DELETE и TRUNCATE из Postgres в ClickPipes?
Начальная загрузка завершилась, но в ClickHouse нет данных или отсутствует часть данных. В чем может быть проблема?
- Есть ли у пользователя достаточные разрешения на чтение исходных таблиц.
- Нет ли на стороне ClickHouse политик на уровне строк, из-за которых часть строк может отфильтровываться.
Advanced Settings при создании ClickPipe. Обратите внимание: для использования этой возможности требуется Postgres версии 17 или выше.

ReorderBufferPreserveLastSpilledSnapshot, это означает, что logical decoding не может прочитать снимок, выгруженный на диск. Возможно, стоит увеличить logical_decoding_work_mem до большего значения.
Я вижу ошибки вроде error converting new tuple to map или error parsing logical message во время CDC-репликации
Я вижу, что мой ClickPipe перешел в состояние Snapshot, но данные не поступают — в чем может быть причина?
CREATE_REPLICATION_SLOT завис в состоянии Lock. Это может происходить из-за того, что другая транзакция удерживает блокировки на объектах, которые Postgres использует при создании слотов репликации.
Чтобы увидеть блокирующие запросы, выполните приведённый ниже запрос на исходном экземпляре Postgres: