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

Узнайте, как перенести данные PostgreSQL в 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 как для исходной, так и для целевой базы данных. Peer — это подключение к базе данных. В интерфейсе PeerDB перейдите в раздел “Peers”, выбрав “Peers” на боковой панели. Чтобы создать новый peer, нажмите кнопку + New peer.

​

Создание peer для источника

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

​

Создание целевого peer

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

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

​

Получение дампа схемы исходной базы данных

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

Установка pg_dump

Ubuntu:

Обновите списки пакетов:

sudo apt update

Установите клиент PostgreSQL:

sudo apt install postgresql-client

macOS:

Способ 1: с помощью Homebrew (рекомендуется)

Установите Homebrew, если он у вас ещё не установлен:

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

Установите PostgreSQL:

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

Далее нужно создать 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”.

​

Дождитесь завершения Initial load

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

Когда 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.

Слоты репликации

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

​

Справочные материалы

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

​

Следующие шаги

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

• Краткое руководство по Managed Postgres