Это часть 1 руководства по миграции с PostgreSQL на ClickHouse. На практическом примере показано, как эффективно выполнить миграцию с помощью репликации в реальном времени (CDC). Многие из рассмотренных здесь концепций также применимы к ручному массовому переносу данных из PostgreSQL в ClickHouse.В качестве примера, демонстрирующего типичную миграцию из Postgres в ClickHouse, мы используем набор данных Stack Overflow, описанный здесь. Он содержит все
post, vote, user, comment и badge, появившиеся на Stack Overflow с 2008 года по апрель 2024 года. Схема PostgreSQL для этих данных показана ниже:

pg_dump для скачивания вместе с DDL, а ниже приведены последующие команды загрузки данных:
Хотя в нашем примере для демонстрации разницы в производительности между Postgres и ClickHouse используются результаты на полном наборе данных, все описанные ниже шаги работают точно так же и для меньшего подмножества. Если вы хотите загрузить в Postgres полный набор данных, см. здесь. Из-за ограничений внешних ключей, задаваемых приведённой выше схемой, полный набор данных для PostgreSQL включает только строки, удовлетворяющие требованиям ссылочной целостности. При необходимости версию в формате Parquet без таких ограничений можно легко загрузить напрямую в ClickHouse.Обратитесь к этому руководству, чтобы настроить ClickPipes для PostgreSQL. В нем рассматриваются различные типы исходных экземпляров Postgres. При использовании CDC с ClickPipes или PeerDB каждая таблица в базе данных PostgreSQL автоматически реплицируется в ClickHouse. Чтобы обрабатывать обновления и удаления почти в реальном времени, ClickPipes сопоставляет таблицы Postgres с ClickHouse, используя движок ReplacingMergeTree, специально предназначенный для обработки обновлений и удалений в ClickHouse. Подробнее о том, как данные реплицируются в ClickHouse с помощью ClickPipes, можно узнать здесь. Важно отметить, что репликация с использованием CDC создает в ClickHouse дублирующиеся строки при репликации операций обновления и удаления. См. методы использования модификатора FINAL для работы с такими случаями в ClickHouse. Давайте посмотрим, как таблица
users создается в ClickHouse с помощью ClickPipes.
- Табличные функции - использовать табличную функцию Postgres в ClickHouse, чтобы
SELECT-ом получить данные из Postgres и выполнить ихINSERTв таблицу ClickHouse. Подходит для массовой загрузки наборов данных объёмом до нескольких сотен ГБ. - Экспорт - экспортировать данные в промежуточные форматы, такие как CSV или SQL-скрипт. Затем эти файлы можно загрузить в ClickHouse либо через клиент с помощью конструкции
INSERT FROM INFILE, либо с использованием объектного хранилища и связанных с ним функций, то есть s3, gcs.
DESCRIBE с табличной функцией Postgres. Следующая команда описывает таблицу posts в PostgreSQL; измените её в соответствии с вашим окружением:
Query
Query
INSERT INTO SELECT, считывая данные из PostgresSQL и вставляя их в ClickHouse:
Query
SELECT можно применить условие WHERE. Этот подход также можно использовать для поддержки обновлений, если гарантируется, что они изменяют один и тот же столбец. Однако для поддержки удалений потребуется полная перезагрузка, что по мере роста таблицы может быть сложно реализовать.
Мы покажем начальную и инкрементальную загрузку с использованием CreationDate (предполагается, что это значение обновляется при обновлении строк)..
ClickHouse будет проталкивать в PostgreSQL server простые предложенияWHERE, такие как=,!=,>,>=,<,<=и IN. Поэтому инкрементальные загрузки можно сделать эффективнее, если убедиться, что на столбцах, используемых для определения набора изменений, есть индекс.
Один из возможных способов обнаружения операций UPDATE при использовании репликации запросов — использовать системный столбецНажмите здесь, чтобы перейти к Part 2XMIN(идентификаторы транзакций) в качестве водяной метки: изменение этого столбца указывает на наличие изменения и, следовательно, может быть применено к целевой таблице. Пользователям, использующим этот подход, следует учитывать, что значенияXMINмогут переполняться по кругу, а сравнения требуют полного сканирования таблицы, из-за чего отслеживание изменений усложняется.