Подключиться
Практические руководства

Асинхронная репликация данных из PostgreSQL в ClickHouse

Статья создана

Вы можете перенести базу данных из PostgreSQL в ClickHouse с помощью сервиса Yandex Data Transfer. Для этого:

  1. Подготовьте трансфер.
  2. Активируйте трансфер.
  3. Проверьте работу репликации.
  4. Выполните выборку данных в приемнике.

Если созданные ресурсы вам больше не нужны, удалите их.

Перед началом работы

Для примера все нужные ресурсы будут созданы в Yandex Cloud. Подготовьте инфраструктуру:

  1. Создайте кластер-источник Managed Service for PostgreSQL любой подходящей конфигурации с хостами в публичном доступе и следующими настройками:

    • Имя базы — db1.
    • Имя пользователя — pg-user.
    • Пароль — <пароль источника>.

  2. Создайте кластер-приемник Managed Service for ClickHouse любой подходящей конфигурации с хостами в публичном доступе и следующими настройками:

    • Количество хостов ClickHouse — не меньше 2 (для включения репликации в кластере).
    • Имя базы — db1.
    • Имя пользователя — ch-user.
    • Пароль — <пароль приемника>.

  3. Убедитесь, что группы безопасности кластеров настроены правильно и допускают подключение к ним:

  4. Выдайте роль mdb_replication пользователю pg-user в кластере Managed Service for PostgreSQL.

  1. Если у вас еще нет Terraform, установите и настройте его.

  2. Скачайте файл с настройками провайдера. Поместите его в отдельную рабочую директорию и укажите значения параметров.

  3. Скачайте в ту же рабочую директорию файл конфигурации postgresql-to-clickhouse.tf.

    В этом файле описаны:

    • сети;
    • подсети;
    • группы безопасности, необходимые для подключения к кластерам;
    • кластер-источник Managed Service for PostgreSQL;
    • кластер-приемник Managed Service for ClickHouse;
    • эндпоинт для источника;
    • эндпоинт для приемника;
    • трансфер.

  4. Укажите в файле postgresql-to-clickhouse.tf пароли пользователя-администратора PostgreSQL и ClickHouse.

  5. Выполните команду terraform init в директории с конфигурационным файлом. Эта команда инициализирует провайдер, указанный в конфигурационных файлах, и позволяет работать с ресурсами и источниками данных провайдера.

  6. Проверьте корректность файлов конфигурации Terraform с помощью команды:

    terraform validate

    Если в файлах конфигурации есть ошибки, Terraform на них укажет.

  7. Создайте необходимую инфраструктуру:

    1. Выполните команду для просмотра планируемых изменений:

      terraform plan

      Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

    2. Если вас устраивают планируемые изменения, внесите их:

      1. Выполните команду:

        terraform apply

      2. Подтвердите изменение ресурсов.

      3. Дождитесь завершения операции.

    В указанном каталоге будут созданы все требуемые ресурсы. Проверить появление ресурсов и их настройки можно в консоли управления.

Подготовьте трансфер

  1. Подключитесь к кластеру Managed Service for PostgreSQL.

  2. Создайте в базе данных db1 таблицу x_tab и заполните ее данными:

    CREATE TABLE x_tab
(
    id NUMERIC PRIMARY KEY,
    name CHAR(5)
);
CREATE INDEX ON x_tab (id);
INSERT INTO x_tab (id, name) VALUES
  (40, 'User1'),
  (41, 'User2'),
  (42, 'User3'),
  (43, 'User4'),
  (44, 'User5');

  3. Создайте трансфер:

    1. Создайте эндпоинт-источник типа PostgreSQL и укажите в нем параметры подключения к кластеру:

      • Тип подключения — Кластер Managed Service for PostgreSQL.
      • Кластер — <имя кластера-источника PostgreSQL> из выпадающего списка.
      • База данных — db1.
      • Пользователь — pg-user.
      • Пароль — <пароль пользователя>.

    2. Создайте эндпоинт-приемник типа ClickHouse и укажите в нем параметры подключения к кластеру:

      • Тип подключения — Кластер Managed Service for ClickHouse.
      • Кластер — <имя кластера-приемника ClickHouse> из выпадающего списка.
      • База данных — db1.
      • Пользователь — ch-user.
      • Пароль — <пароль пользователя>.
      • Политика очистки — DROP.

    3. Создайте трансфер типа Копирование и репликация, использующий созданные эндпоинты.

    1. Укажите в файле postgresql-to-clickhouse.tf значение 1 для переменной transfer_enabled.

    2. Проверьте корректность файлов конфигурации Terraform с помощью команды:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

    3. Создайте необходимую инфраструктуру:

      1. Выполните команду для просмотра планируемых изменений:

        terraform plan

        Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

      2. Если вас устраивают планируемые изменения, внесите их:

        1. Выполните команду:

          terraform apply

        2. Подтвердите изменение ресурсов.

        3. Дождитесь завершения операции.

Активируйте трансфер

  1. Активируйте трансфер и дождитесь его перехода в статус Реплицируется.

  2. Чтобы проверить, что трансфер перенес данные с учетом репликации в приемнике, подключитесь к кластеру-приемнику Yandex Managed Service for ClickHouse и посмотрите, что таблица x_tab в базе db1 содержит те же колонки, что таблица x_tab в базе-источнике, а также колонки с временными метками __data_transfer_commit_time и __data_transfer_delete_time:

    SELECT * FROM db1.x_tab WHERE id = 41;

    ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
│ 41 │  User2 │   1633417594957267000          │ 0                             │
└────┴────────┴────────────────────────────────┴───────────────────────────────┘

Проверьте работу репликации

  1. Подключитесь к кластеру-источнику.

  2. Удалите строку с идентификатором 41 и измените строку с идентификатором 42 в таблице x_tab базы-источника PostgreSQL:

    DELETE FROM db1.public.x_tab WHERE id = 41;
UPDATE db1.public.x_tab SET name = 'Key3' WHERE id = 42;

  3. Убедитесь, что в таблице x_tab на приемнике ClickHouse отобразились изменения:

    SELECT * FROM db1.x_tab WHERE (id >= 41) AND (id <= 42);

    ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
│ 41 │  User2 │   1633417594957267000          │ 1675417594957267000           │
│ 42 │  Key3  │   1675417594957267000          │ 0                             │
│ 42 │  User3 │   1633417594957268000          │ 1675417594957267000           │
└────┴────────┴────────────────────────────────┴───────────────────────────────┘

Выполните выборку данных в ClickHouse

На приемнике ClickHouse с включенной репликацией для воссоздания таблиц используются движки ReplicatedReplacingMergeTree и ReplacingMergeTree. В каждую таблицу автоматически добавляются столбцы:

  • __data_transfer_commit_time — время изменения строки на это значение, в формате TIMESTAMP;

  • __data_transfer_delete_time — время удаления строки в формате TIMESTAMP, если строка удалена в источнике. Если строка не удалялась, то устанавливается значение 0.

    Столбец __data_transfer_commit_time необходим для работы движка ReplicatedReplacedMergeTree. Если запись удаляется или изменяется, в таблицу вставляется новая строка со значением в этом столбце. Запрос по одному первичному ключу возвращает несколько записей с разными значениями в столбце __data_transfer_commit_time.

В статусе трансфера Реплицируется данные в источнике могут добавляться или удаляться. Чтобы обеспечить стандартное поведение команд SQL, когда первичный ключ указывает на единственную запись, дополните запросы к перенесенным таблицам в ClickHouse конструкцией с фильтром по столбцу __data_transfer_delete_time. Например, для таблицы x_tab:

SELECT * FROM x_tab FINAL
WHERE __data_transfer_delete_time = 0;

Для упрощения запросов SELECT создайте представление с фильтром по столбцу __data_transfer_delete_time и обращайтесь к нему. Например, для таблицы x_tab:

CREATE VIEW x_tab_view AS SELECT * FROM x_tab FINAL
WHERE __data_transfer_delete_time == 0;

Примечание

Использование ключевого слова FINAL сильно снижает эффективность запросов. По возможности избегайте его применения при работе с большими таблицами.

Удалите созданные ресурсы

Если созданные ресурсы вам больше не нужны, удалите их:

  • Убедитесь, что трансфер находится в статусе Завершен и удалите его.

  • Удалите эндпоинты и кластеры:

    Если вы создали ресурсы с помощью Terraform:

    1. В терминале перейдите в директорию с планом инфраструктуры.

    2. Удалите конфигурационный файл postgresql-to-clickhouse.tf.

    3. Проверьте корректность файлов конфигурации Terraform с помощью команды:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

    4. Подтвердите изменение ресурсов.

      1. Выполните команду для просмотра планируемых изменений:

        terraform plan

        Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

      2. Если вас устраивают планируемые изменения, внесите их:

        1. Выполните команду:

          terraform apply

        2. Подтвердите изменение ресурсов.

        3. Дождитесь завершения операции.

      Все ресурсы, которые были описаны в конфигурационном файле postgresql-to-clickhouse.tf, будут удалены.

В этой статье: