Миграция данных

Это Часть 1 руководства по миграции из PostgreSQL в ClickHouse. Используя практический пример, оно демонстрирует, как эффективно выполнить миграцию с использованием подхода репликации в реальном времени (CDC). Многие из охваченных концепций также применимы к ручным массовым передачам данных из PostgreSQL в ClickHouse.

Набор данных

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

DDL команды для создания таблиц в PostgreSQL доступны здесь.

Эта схема, хотя и не обязательно является наиболее оптимальной, использует ряд популярных функций PostgreSQL, включая первичные ключи, внешние ключи, партиционирование и индексы.

Мы мигрируем каждую из этих концепций к соответствующим им в ClickHouse.

Для пользователей, которые хотят заполнить этот набор данных в экземпляр PostgreSQL, чтобы протестировать этапы миграции, мы предоставили данные в формате pg_dump для загрузки, а DDL и последующие команды загрузки данных представлены ниже:

Хотя этот набор данных невелик для ClickHouse, он значительно объемен для Postgres. Выше представлен поднабор, охватывающий первые три месяца 2024 года.

Хотя наши примеры результатов используют полный набор данных, чтобы показать различия в производительности между Postgres и ClickHouse, все шаги, документированные ниже, функционально идентичны для меньшего поднабора. Пользователи, желающие загрузить полный набор данных в Postgres, могут увидеть здесь. Из-за внешних ограничений, наложенных вышеуказанной схемой, полный набор данных для PostgreSQL содержит только строки, которые удовлетворяют ссылочной целостности. Версия Parquet без таких ограничений может быть легко загружена напрямую в ClickHouse, если это необходимо.

Миграция данных

Репликация в реальном времени (CDC)

Обратитесь к этому руководству, чтобы настроить ClickPipes для PostgreSQL. В руководстве охватываются многие разные типы исходных экземпляров Postgres.

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

Чтобы обрабатывать обновления и удаления в почти реальном времени, ClickPipes сопоставляет таблицы Postgres с ClickHouse с использованием движка ReplacingMergeTree, специально разработанного для обработки обновлений и удалений в ClickHouse. Вы можете найти дополнительную информацию о том, как данные реплицируются в ClickHouse с использованием ClickPipes здесь. Важно отметить, что репликация с использованием CDC создает дублированные строки в ClickHouse при репликации операций обновления или удаления. См. техники с использованием модификатора FINAL для обработки их в ClickHouse.

Давайте посмотрим, как таблица users создается в ClickHouse с использованием ClickPipes.

После настройки ClickPipes начинает миграцию всех данных из PostgreSQL в ClickHouse. В зависимости от сети и размера развертывания, это должно занять всего несколько минут для набора данных Stack Overflow.

Ручная массовая загрузка с периодическими обновлениями

Используя ручной подход, начальная массовая загрузка набора данных может быть выполнена через:

• Табличные функции - Используя табличную функцию Postgres в ClickHouse для SELECT данных из Postgres и INSERT их в таблицу ClickHouse. Это актуально для массовых загрузок до наборов данных нескольких сотен ГБ.
• Экспорт - Экспорт в промежуточные форматы, такие как CSV или SQL файл скрипта. Эти файлы затем могут быть загружены в ClickHouse либо с клиента с помощью команды INSERT FROM INFILE, либо с использованием объектного хранилища и их связанных функций, т.е. s3, gcs.

При ручной загрузке данных из PostgreSQL необходимо сначала создать таблицы в ClickHouse. Обратитесь к этой документации по моделированию данных, которая также использует набор данных Stack Overflow для оптимизации схемы таблиц в ClickHouse.

Типы данных между PostgreSQL и ClickHouse могут отличаться. Чтобы установить эквивалентные типы для каждого из столбцов таблицы, мы можем использовать команду DESCRIBE с табличной функцией Postgres. Следующая команда описывает таблицу posts в PostgreSQL, измените её в соответствии с вашей средой:

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

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

Мы можем заполнить это с помощью простого INSERT INTO SELECT, считывая данные из PostgreSQL и вставляя в ClickHouse:

Инкрементные загрузки могут, в свою очередь, быть запланированы. Если таблица Postgres принимает только вставки и существует увеличивающийся id или временной штамп, пользователи могут использовать вышеуказанный подход с табличной функцией для загрузки приростов, т.е. к SELECT можно применить клаузу WHERE. Этот подход также может быть использован для поддержки обновлений, если эти обновления гарантированно будут вноситься в один и тот же столбец. Поддержка удалений, однако, потребует полной перезагрузки, что может быть сложно осуществить по мере роста таблицы.

Мы демонстрируем начальную загрузку и инкрементную загрузку с использованием CreationDate (мы предполагаем, что это обновляется, если строки обновляются).

ClickHouse будет передавать простые клаузулы WHERE, такие как =, !=, >, >=, <, <= и IN серверу PostgreSQL. Инкрементные загрузки могут стать более эффективными при условии, что индекс существует на столбцах, используемых для идентификации набора изменений.

Возможный метод обнаружения обновлений при использовании репликации запросов - использование системного столбца XMIN (идентификаторы транзакций) в качестве водяного знака - изменение в этом столбце указывает на изменение, и, следовательно, может быть применено к целевой таблице. Пользователи, использующие этот подход, должны помнить, что значения XMIN могут перезаписываться, и сравнения требуют полной проверки таблицы, что усложняет отслеживание изменений.

Щелкните здесь, чтобы перейти к Часть 2