Асинхронная репликация данных из PostgreSQL в ClickHouse
Вы можете перенести базу данных из PostgreSQL в ClickHouse с помощью сервиса Yandex Data Transfer. Для этого:
- Подготовьте трансфер.
- Активируйте трансфер.
- Проверьте работу репликации.
- Выполните выборку данных в приемнике.
Если созданные ресурсы вам больше не нужны, удалите их.
Перед началом работы
Для примера все нужные ресурсы будут созданы в Yandex Cloud. Подготовьте инфраструктуру:
-
Создайте кластер-источник Managed Service for PostgreSQL любой подходящей конфигурации с хостами в публичном доступе и следующими настройками:
- Имя базы —
db1
. - Имя пользователя —
pg-user
. - Пароль —
<пароль источника>
.
- Имя базы —
-
Создайте кластер-приемник Managed Service for ClickHouse любой подходящей конфигурации с хостами в публичном доступе и следующими настройками:
- Количество хостов ClickHouse — не меньше 2 (для включения репликации в кластере).
- Имя базы —
db1
. - Имя пользователя —
ch-user
. - Пароль —
<пароль приемника>
.
-
Убедитесь, что группы безопасности кластеров настроены правильно и допускают подключение к ним:
-
Выдайте роль
mdb_replication
пользователюpg-user
в кластере Managed Service for PostgreSQL.
-
Если у вас еще нет Terraform, установите и настройте его.
-
Скачайте файл с настройками провайдера. Поместите его в отдельную рабочую директорию и укажите значения параметров.
-
Скачайте в ту же рабочую директорию файл конфигурации postgresql-to-clickhouse.tf.
В этом файле описаны:
- сети;
- подсети;
- группы безопасности, необходимые для подключения к кластерам;
- кластер-источник Managed Service for PostgreSQL;
- кластер-приемник Managed Service for ClickHouse;
- эндпоинт для источника;
- эндпоинт для приемника;
- трансфер.
-
Укажите в файле
postgresql-to-clickhouse.tf
пароли пользователя-администратора PostgreSQL и ClickHouse. -
Выполните команду
terraform init
в директории с конфигурационным файлом. Эта команда инициализирует провайдер, указанный в конфигурационных файлах, и позволяет работать с ресурсами и источниками данных провайдера. -
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Создайте необходимую инфраструктуру:
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
В указанном каталоге будут созданы все требуемые ресурсы. Проверить появление ресурсов и их настройки можно в консоли управления.
-
Подготовьте трансфер
-
Создайте в базе данных
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');
-
Создайте трансфер:
ВручнуюС помощью Terraform-
Создайте эндпоинт-источник типа
PostgreSQL
и укажите в нем параметры подключения к кластеру:- Тип подключения —
Кластер Managed Service for PostgreSQL
. - Кластер —
<имя кластера-источника PostgreSQL>
из выпадающего списка. - База данных —
db1
. - Пользователь —
pg-user
. - Пароль —
<пароль пользователя>
.
- Тип подключения —
-
Создайте эндпоинт-приемник типа
ClickHouse
и укажите в нем параметры подключения к кластеру:- Тип подключения —
Кластер Managed Service for ClickHouse
. - Кластер —
<имя кластера-приемника ClickHouse>
из выпадающего списка. - База данных —
db1
. - Пользователь —
ch-user
. - Пароль —
<пароль пользователя>
. - Политика очистки —
DROP
.
- Тип подключения —
-
Создайте трансфер типа Копирование и репликация, использующий созданные эндпоинты.
-
Укажите в файле
postgresql-to-clickhouse.tf
значение1
для переменнойtransfer_enabled
. -
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Создайте необходимую инфраструктуру:
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
-
-
Активируйте трансфер
-
Активируйте трансфер и дождитесь его перехода в статус Реплицируется.
-
Чтобы проверить, что трансфер перенес данные с учетом репликации в приемнике, подключитесь к кластеру-приемнику 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 │ └────┴────────┴────────────────────────────────┴───────────────────────────────┘
Проверьте работу репликации
-
Подключитесь к кластеру-источнику.
-
Удалите строку с идентификатором
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;
-
Убедитесь, что в таблице
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Если вы создали ресурсы с помощью Terraform:
-
В терминале перейдите в директорию с планом инфраструктуры.
-
Удалите конфигурационный файл
postgresql-to-clickhouse.tf
. -
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Подтвердите изменение ресурсов.
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
Все ресурсы, которые были описаны в конфигурационном файле
postgresql-to-clickhouse.tf
, будут удалены. -
-