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

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

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

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

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

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

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

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. Создайте трансфер:

   Вручную

   Terraform

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

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

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

      • Тип подключения — Managed кластер.
      • Managed кластер — <имя_кластера-приемника_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®

На приемнике 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;

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

Некоторые ресурсы платные. Чтобы за них не списывалась плата, удалите ресурсы, которые вы больше не будете использовать:

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

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

  Вручную

  Terraform

  • Эндпоинт-источник и эндпоинт-приемник.
  • Managed Service for PostgreSQL.
  • Managed Service for ClickHouse®.

  Если вы создали ресурсы с помощью 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, будут удалены.

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.