Перенос в ClickHouse Managed Postgres с помощью PeerDB

В этом руководстве приведены пошаговые инструкции по переносу базы данных PostgreSQL в ClickHouse Managed Postgres с помощью PeerDB.

• Доступ к исходной базе данных PostgreSQL.
• Экземпляр ClickHouse Managed Postgres, в который вы хотите перенести данные.
• PeerDB, установленный на компьютере. Вы можете воспользоваться инструкциями по установке в репозитории PeerDB на GitHub. Достаточно клонировать репозиторий и выполнить docker-compose up. В этом руководстве мы будем использовать интерфейс PeerDB, который после запуска PeerDB будет доступен по адресу http://localhost:3000.

Прежде чем начинать миграцию, имейте в виду следующее:

• Объекты базы данных: PeerDB автоматически создаст таблицы в целевой базе данных на основе исходной схемы. Однако некоторые объекты базы данных, такие как индексы, ограничения и триггеры, не переносятся автоматически. После миграции вам потребуется вручную заново создать эти объекты в целевой базе данных.
• Изменения DDL: Если вы включите непрерывную репликацию, PeerDB будет поддерживать синхронизацию целевой базы данных с исходной для операций DML (INSERT, UPDATE, DELETE) и передавать операции ADD COLUMN. Однако другие изменения DDL (например, DROP COLUMN, ALTER COLUMN) автоматически не передаются. Подробнее о поддержке изменений схемы здесь
• Сетевое подключение: Убедитесь, что и исходная, и целевая базы данных доступны с машины, на которой запущен PeerDB. Возможно, потребуется настроить правила межсетевого экрана или параметры Security Group, чтобы разрешить подключение.

Сначала нужно создать peer как для исходной, так и для целевой базы данных. Peer — это подключение к базе данных. В интерфейсе PeerDB перейдите в раздел "Peers", выбрав "Peers" на боковой панели. Чтобы создать новый peer, нажмите кнопку + New peer.

Создайте peer для исходной базы данных PostgreSQL, указав сведения о подключении: хост, порт, имя базы данных, имя пользователя и пароль. После этого нажмите кнопку Create peer, чтобы сохранить peer.

Аналогичным образом создайте peer для своего экземпляра ClickHouse Managed Postgres, указав необходимые сведения о подключении. Сведения о подключении для вашего экземпляра можно получить в консоли ClickHouse Cloud. После заполнения данных нажмите кнопку Create peer, чтобы сохранить целевой peer.

Теперь в разделе "Peers" должны отображаться оба peer — исходный и целевой.

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

sudo apt update

sudo apt install postgresql-client

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

brew install postgresql

pg_dump --version

pg_dump -d 'postgresql://<user>:<password>@<host>:<port>/<database>'  -s > source_schema.sql

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

# Предварительный просмотр
grep -n "CONSTRAINT.*UNIQUE" <dump_file_path>
grep -n "CREATE UNIQUE INDEX" <dump_file_path>
grep -n -E "(CONSTRAINT.*UNIQUE|CREATE UNIQUE INDEX)" <dump_file_path>

# Удаление
sed -i.bak -E '/CREATE UNIQUE INDEX/,/;/d; /(CONSTRAINT.*UNIQUE|ADD CONSTRAINT.*UNIQUE)/d' <dump_file_path>

После очистки файла дампа схемы его можно применить к целевой базе данных ClickHouse Managed Postgres, подключившись через psql и выполнив файл дампа схемы:

psql -h <target_host> -p <target_port> -U <target_username> -d <target_database> -f source_schema.sql

Здесь, на стороне целевой базы, важно, чтобы ингестия через PeerDB не блокировалась ограничениями по внешним ключам. Для этого можно изменить целевую роль (используемую выше в target peer), задав для session_replication_role значение replica:

ALTER ROLE <target_role> SET session_replication_role = replica;

Далее нужно создать mirror, чтобы определить процесс переноса данных между исходным и целевым peer. В интерфейсе PeerDB перейдите в раздел "Mirrors", щелкнув "Mirrors" на боковой панели. Чтобы создать новый mirror, нажмите кнопку + New mirror.

1. Задайте mirror имя, описывающее перенос.
2. Выберите исходный и целевой peer, которые вы создали ранее, в раскрывающихся списках.
3. Убедитесь, что:

• Soft delete выключен.
• Разверните Advanced settings. Убедитесь, что система типов Postgres включена, а столбцы PeerDB отключены.

4. Выберите таблицы, которые хотите перенести. Можно выбрать конкретные таблицы или все таблицы из исходной базы данных.

5. После настройки параметров mirror нажмите кнопку Create mirror.

После этого новый mirror должен появиться в разделе "Mirrors".

После создания mirror PeerDB начнет Initial load данных из исходной базы данных в целевую. Чтобы отслеживать ход Initial load данных, нажмите mirror и перейдите на вкладку Initial load.

Когда Initial load завершится, вы увидите статус, указывающий, что миграция завершена.

Если щёлкнуть по исходному peer, можно увидеть список выполняемых команд PeerDB. Например:

1. Сначала выполняется запрос COUNT, чтобы оценить количество строк в каждой таблице.
2. Затем выполняется запрос на партиционирование с использованием NTILE, чтобы разбить большие таблицы на более мелкие фрагменты для эффективной передачи данных.
3. После этого выполняются команды FETCH, чтобы получить данные из исходной базы данных, а затем PeerDB синхронизирует их с целевой базой данных.

После завершения миграции:

• Выполните проверки перед переключением

Перед переключением трафика сравните ключевые таблицы в исходной и целевой системах:

-- Сравнение количества строк в критически важных таблицах
SELECT 'public.orders' AS table_name, COUNT(*) AS row_count FROM public.orders;
SELECT 'public.customers' AS table_name, COUNT(*) AS row_count FROM public.customers;

-- Выборочная проверка последних записей в таблицах с высокой активностью
SELECT MAX(updated_at) FROM public.orders;
SELECT MAX(id) FROM public.orders;

• Остановите запись в исходной системе

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

ALTER DATABASE <source_db> SET default_transaction_read_only = on;

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

ALTER DATABASE <source_db> SET default_transaction_read_only = off;

• Убедитесь, что репликация полностью синхронизирована

Проверьте, что последняя строка в одной или нескольких таблицах с высокой интенсивностью записи совпадает в исходной и целевой системах:

-- Выполните на источнике и целевой базе данных и сравните результаты
SELECT MAX(id) AS latest_id, MAX(updated_at) AS latest_ts FROM public.orders;

• Заново создайте и включите ограничения, индексы и триггеры

Если вы удалили ограничения/индексы для ингестии или отложили их создание, примените их снова. Также сбросьте роль репликации на целевом экземпляре, если ранее вы установили для неё значение replica:

ALTER ROLE <target_role> SET session_replication_role = origin;

# Пример: применение SQL-файла, содержащего ограничения/индексы/триггеры
psql -h <target_host> -p <target_port> -U <target_user> -d <target_db> -f post_migration_objects.sql

• Сброс последовательностей в целевых таблицах

После загрузки данных приведите последовательности в соответствие с текущими значениями в таблицах:

-- Универсальный сброс последовательностей для всех столбцов с serial/identity в несистемных схемах
DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT
            n.nspname AS schema_name,
            c.relname AS table_name,
            a.attname AS column_name,
            pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        JOIN pg_attribute a ON a.attrelid = c.oid
        WHERE c.relkind = 'r'
            AND a.attnum > 0
            AND NOT a.attisdropped
            AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        IF r.seq_name IS NOT NULL THEN
            EXECUTE format(
                'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false)',
                r.seq_name, r.column_name, r.schema_name, r.table_name
            );
        END IF;
    END LOOP;
END $$;

• Переключите трафик приложения

После успешной проверки и настройки последовательностей и ограничений:

1. Переведите трафик чтения на ClickHouse Managed Postgres.
2. Переведите трафик записи на ClickHouse Managed Postgres.
3. Отслеживайте ошибки приложения, нарушения ограничений и состояние базы данных.

• Очистите ресурсы

Когда вы убедитесь, что миграция прошла успешно, и переключите приложение на использование ClickHouse Managed Postgres, можно удалить mirror и peers в PeerDB.

• Документация по ClickHouse Managed Postgres
• Руководство PeerDB по созданию CDC
• FAQ по Postgres ClickPipe (также актуально для PeerDB)

Поздравляем! Вы успешно перенесли свою базу данных PostgreSQL в ClickHouse Managed Postgres с помощью pg_dump и pg_restore. Теперь вы можете изучить возможности ClickHouse Managed Postgres и его интеграцию с ClickHouse. Вот 10-минутное краткое руководство, которое поможет вам начать:

• Краткое руководство по быстрому старту с ClickHouse Managed Postgres