Миграция данных в Облако репликацией

Предположим, вы решили перенести БД в сервис управляемых БД PostgreSQL. Для этого придётся выполнить логическую репликацию:

1. Настроить сервер с источником данных.
2. Экспортировать схему БД из источника.
3. Создать кластер Managed Service for PostgreSQL и восстановить схему БД.
4. Создать публикации и подписки.
5. Перенести PostgreSQL-sequences после репликации.
6. Отключить репликацию и перенести нагрузки.

Как видите, процесс довольно трудоёмкий. Разберём его по шагам.1. Настройка сервера с источником данныхЧтобы перенести данные с помощью репликации, настройте PostgreSQL на сервере-источнике. Измените настройки SSL и WAL (Write Ahead Log) в файле postgresql.conf (в Ubuntu путь к нему по умолчанию — /etc/postgresql/10/main/postgresql.conf).Для переноса данных используйте SSL: это поможет не только зашифровать данные, но и сжать их. Подробнее читайте в документации PostgreSQL: разделы SSL Support и Database Connection Control Functions.Включите использование SSL:

SSL=ON 

Измените уровень логирования для WAL, чтобы добавить в него информацию для логической репликации. В файле postgresql.conf найдите строку с настройкой wal_level, раскомментируйте её при необходимости и установите значение logical:

wal_level=logical 

Теперь настройте аутентификацию хостов в источнике: внесите хосты кластера в облаке в файл pg_hba.conf (в Debian и Ubuntu путь к нему по умолчанию — /etc/postgresql/10/main/pg_hba.conf). Добавьте в файл строки, которые разрешат входящие соединения к БД с указанных хостов.Если вы используете SSL:

hostssl    all            all             <адрес хоста>      md5
hostssl    replication    all             <адрес хоста>      md5          
  

Если вы не используете SSL:

host    all            all             <адрес хоста>      md5
host    replication    all             <адрес хоста>      md5 

Если на сервере-источнике работает фаервол, разрешите входящие соединения с хостов кластера Managed Service for PostgreSQL. Например, для Ubuntu 18:

sudo ufw allow from <адрес хоста> to any port 5432 

Перезагрузите сервер БД, чтобы применить настройки:

sudo systemctl restart postgresql 

После перезапуска проверьте статус PostgreSQL с помощью команды:

sudo systemctl status postgresql 

2. Экспорт схемы БД из источникаС помощью утилиты pg_dump создайте файл со схемой БД, которую нужно применить в кластере Managed Service for PostgreSQL.

 
pg_dump -h <адрес сервера СУБД> \
    -U <имя пользователя> \
    -p <порт> \
    --schema-only \
    --no-privileges \
    --no-subscriptions \
    -d <имя базы данных> -Fd -f /tmp/db_dump
 

В этой команде при экспорте исключаются все данные, которые связаны с привилегиями и ролями. Это необходимо, чтобы не возникало конфликтов с настройками БД в Yandex.Cloud. Если базе нужны дополнительные пользователи, создайте их.3. Создание кластера Managed Service for PostgreSQL и восстановление схемы БДЕсли у вас ещё нет PostgreSQL-кластера в Yandex.Cloud, создайте его. При создании кластера укажите то же имя БД, что и на сервере-источнике.Восстановите схему в созданном кластере:

 
pg_restore -Fd -v --single-transaction -s --no-privileges \
          -h <адрес приемника> \
          -U <имя пользователя> \
          -p 6432 \
          -d <имя базы данных> /tmp/db_dump
 
 

4. Создание публикаций и подпискиОпределите публикацию (группу логически реплицируемых таблиц) на сервере-источнике и подписку (описание соединения с другой базой) на сервере-приёмнике.На сервере-источнике создайте публикацию для всех таблиц БД. Если переносите несколько баз — для каждой сделайте свою публикацию.Чтобы создать публикации для всех таблиц, потребуются права суперпользователя. Чтобы перенести выбранные таблицы, права не нужны. Воспользуйтесь запросом:

CREATE PUBLICATION p_data_migration FOR ALL TABLES; 

На хосте кластера Managed Service for PostgreSQL создайте подписку со строкой подключения к публикации. Подробности о создании подписок смотрите в документации PostgreSQL (раздел Create subscription).Запрос с включённым SSL:

 
CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<адрес сервера-источника> port=<порт> user=<имя пользователя> sslmode=verify-full dbname=<имя базы данных>' PUBLICATION p_data_migration;
  

Без SSL:

 
CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<адрес сервера-источника> port=<порт> user=<имя пользователя> sslmode=disable dbname=<имя базы данных>' PUBLICATION p_data_migration;
  

Следите за статусом репликации через каталоги pg_subscription_rel. Общий статус репликации на приёмнике можно получить через pg_stat_subscription, на источнике — через pg_stat_replication.

select * from pg_subscription_rel; 

Важно следить за статусом репликации на приёмнике по полю srsubstate. Значение r в поле srsubstate говорит о том, что синхронизация завершилась и базы готовы к репликации.5. Перенос PostgreSQL-sequences после репликацииЧтобы завершить синхронизацию источника и приёмника, запретите запись данных на сервере-источнике и перенесите PostgreSQL-sequences в кластер Managed Service for PostgreSQL.Экспортируйте PostgreSQL-sequences из источника:

 
pg_dump -h <адрес сервера СУБД> \
    -U <имя пользователя> \
    -p <порт> -d <имя базы данных> \
    --data-only -t '*.*_seq' > /tmp/seq-data.sql
  

Обратите внимание на паттерн: если в переносимой БД есть sequences, которые не соответствуют паттерну *.*_seq, то для их выгрузки укажите другой паттерн. Подробная информация о паттернах приводится в документации PostgreSQL.Восстановите sequences на хосте Managed Service for PostgreSQL:

 
psql -h <адрес сервера СУБД> \
    -U <имя пользователя> -p 6432 \
    -d <имя базы данных> \
    < /tmp/seq
 

6. Отключение репликации и перенос нагрузкиПосле того как репликация завершилась и вы перенесли sequences, удалите подписку на сервере-приёмнике (в кластере Managed Service for PostgreSQL):

DROP SUBSCRIPTION s_data_migration; 

После этого можно переносить нагрузку на сервер-приёмник.