Миграция базы данных из стороннего кластера PostgreSQL в Managed Service for PostgreSQL

Перенести данные из стороннего кластера-источника в кластер-приемник Managed Service for PostgreSQL можно тремя способами:

• Перенос данных с использованием сервиса Yandex Data Transfer.

  Этот способ позволяет:

  • обойтись без создания промежуточной виртуальной машины или разрешения доступа к вашему кластеру-приемнику Managed Service for PostgreSQL из интернета;
  • перенести базу целиком без остановки обслуживания пользователей;
  • мигрировать со старых версий PostgreSQL на более новые, в том числе обновить кластер с версии PostgreSQL 15 до версии 16.

  Чтобы использовать этот способ, разрешите подключение к кластеру-источнику из интернета.

  Подробнее см. в разделе Какие задачи решает сервис Yandex Data Transfer.

• Перенос данных с помощью логической репликации.

  Логическая репликация использует механизм подписки (subscriptions). Это позволяет перенести данные в кластер-приемник с минимальным временем простоя.

  Используйте этот способ только в том случае, если перенос данных с помощью Yandex Data Transfer по каким-либо причинам невозможен.

• Перенос данных через создание и восстановление логического дампа.

  Логический дамп — файл с набором команд, последовательное выполнение которых позволяет восстановить состояние базы данных. Он создается с помощью утилиты pg_dump. Чтобы обеспечить полноту логического дампа, перед его созданием кластер-источник следует перевести в режим только чтение.

  Используйте этот способ только в том случае, если перенос данных с помощью любого из предыдущих способов по каким-либо причинам невозможен.

Перенос данных с использованием сервиса Yandex Data TransferПеренос данных с использованием сервиса Yandex Data Transfer

1. Подготовьте кластер-источник.

2. Подготовьте инфраструктуру:

   Вручную

   Terraform

   1. Создайте кластер-приемник Managed Service for PostgreSQL любой подходящей конфигурации. При этом:

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

   2. Подготовьте кластер-приемник.

   3. Создайте эндпоинт для источника со следующими параметрами:

      • Тип базы данных — PostgreSQL.
      • Параметры эндпоинта → Настройки подключения — Пользовательская инсталляция.

      Укажите параметры подключения к кластеру-источнику.

   4. Создайте эндпоинт для приемника со следующими параметрами:

      • Тип базы данных — PostgreSQL.
      • Параметры эндпоинта → Настройки подключения — Кластер Managed Service for PostgreSQL.

      Укажите идентификатор кластера-приемника.

   5. Создайте трансфер типа Копирование и репликация, использующий созданные эндпоинты.

   6. Активируйте трансфер.

      Важно

      Избегайте любых изменений в схеме данных в кластере-источнике и кластере-приемнике во время работы трансфера. Подробнее см. в разделе Работа с базами данных во время трансфера.

   1. Если у вас еще нет Terraform, установите его.

   2. Получите данные для аутентификации. Вы можете добавить их в переменные окружения или указать далее в файле с настройками провайдера.

   3. Настройте и инициализируйте провайдер. Чтобы не создавать конфигурационный файл с настройками провайдера вручную, скачайте его.

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

   5. Скачайте в ту же рабочую директорию файл конфигурации data-transfer-pgsql-mpg.tf.

      В этом файле описаны:

      • сеть;
      • подсеть;
      • группа безопасности и правило, необходимое для подключения к кластеру;
      • кластер-приемник Managed Service for PostgreSQL;
      • эндпоинт для источника;
      • эндпоинт для приемника;
      • трансфер.

   6. Укажите в файле data-transfer-pgsql-mpg.tf:

      • параметры эндпоинта-источника;

      • pg-extensions – список расширений PostgreSQL в кластере-источнике;

      • параметры кластера-приемника, которые используются и как параметры эндпоинта-приемника:

        • target_pgsql_version — версия PostgreSQL, она должна быть не ниже, чем в кластере-источнике;
        • target_user и target_password — имя и пароль пользователя-владельца базы данных.

   7. Проверьте корректность файлов конфигурации Terraform с помощью команды:

      terraform validate
      

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

   8. Создайте необходимую инфраструктуру:

      1. Выполните команду для просмотра планируемых изменений:

         terraform plan
         

         Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

      2. Если вас устраивают планируемые изменения, внесите их:

         1. Выполните команду:

            terraform apply
            

         2. Подтвердите изменение ресурсов.

         3. Дождитесь завершения операции.

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

      Трансфер активируется автоматически после создания.

3. Дождитесь перехода трансфера в статус Реплицируется.

4. Переведите кластер-источник в режим только чтение.

5. На странице мониторинга трансфера дождитесь снижения до нуля характеристики Maximum data transfer delay. Это значит, что на кластер-приемник перенесены все изменения, произошедшие в кластере-источнике после завершения копирования данных.

6. Деактивируйте трансфер и дождитесь его перехода в статус Остановлен.

   Подробнее о статусах трансфера см. в разделе Жизненный цикл трансфера.

7. Переключите нагрузку на кластер-приемник.

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

   Ресурсы созданы вручную

   Ресурсы созданы с помощью Terraform

   • Удалите кластер Managed Service for PostgreSQL.
   • Удалите остановленный трансфер.
   • Удалите эндпоинты для источника и приемника.

   1. В терминале перейдите в директорию с планом инфраструктуры.

   2. Удалите конфигурационный файл data-transfer-pgsql-mpg.tf.

   3. Проверьте корректность файлов конфигурации Terraform с помощью команды:

      terraform validate
      

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

   4. Подтвердите изменение ресурсов.

      1. Выполните команду для просмотра планируемых изменений:

         terraform plan
         

         Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

      2. Если вас устраивают планируемые изменения, внесите их:

         1. Выполните команду:

            terraform apply
            

         2. Подтвердите изменение ресурсов.

         3. Дождитесь завершения операции.

      Все ресурсы, которые были описаны в конфигурационном файле data-transfer-pgsql-mpg.tf, будут удалены.

Перенос данных с помощью логической репликацииПеренос данных с помощью логической репликации

Логическая репликация поддерживается PostgreSQL с версии 10. Кроме миграции данных между одинаковыми версиями PostgreSQL, логическая репликация позволяет мигрировать на более новые версии PostgreSQL.

В кластерах Managed Service for PostgreSQL подписки может использовать владелец базы данных (пользователь, созданный одновременно с кластером) и пользователи с ролью mdb_admin для этого кластера.

Этапы миграции:

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

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

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

Создайте необходимые ресурсы:

Настройте кластер-источникНастройте кластер-источник

1. Укажите нужные настройки SSL и WAL в файле postgresql.conf. В ОС Debian и Ubuntu путь к этому файлу по умолчанию — /etc/postgresql/<версия_PostgreSQL>/main/postgresql.conf.

   1. Для миграции данных рекомендуется использовать SSL: это поможет не только шифровать данные, но и сжимать их. Подробнее в разделах документации PostgreSQL, SSL Support и Database Connection Control Functions.

      Чтобы включить использование SSL, задайте нужное значение в конфигурации:

      ssl = on                   # on, off
      

   2. Измените уровень логирования для Write Ahead Log (WAL), чтобы добавить в него информацию, необходимую для логической репликации. Для этого установите значение logical для настройки wal_level.

      Настройку можно изменить в файле postgresql.conf. Найдите строку с настройкой wal_level, раскомментируйте ее при необходимости и установите значение logical:

      wal_level = logical                    # minimal, replica, or logical
      

2. Настройте аутентификацию хостов в кластере-источнике. Для этого нужно внести хосты кластера в Yandex Cloud в файл pg_hba.conf (на дистрибутивах Debian и Ubuntu по умолчанию расположен по пути /etc/postgresql/<версия_PostgreSQL>/main/pg_hba.conf).

   Добавьте строки для разрешения подключения к базе данных с указанных хостов:

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

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

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

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

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

   sudo ufw allow from <адрес_хоста_кластера-приемника> to any port <порт>
   

4. Перезапустите сервис PostgreSQL, чтобы применить все сделанные настройки:

   sudo systemctl restart postgresql
   

5. Проверьте статус сервиса PostgreSQL после перезапуска:

   sudo systemctl status postgresql
   

Экспортируйте схему БД в кластере-источникеЭкспортируйте схему БД в кластере-источнике

С помощью утилиты pg_dump создайте файл со схемой БД, которую нужно будет применить в кластере-приемнике.

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

В этой команде при экспорте исключаются все данные, связанные с привилегиями и ролями, чтобы не возникало конфликтов с настройками БД в Yandex Cloud. Если вашей БД нужны дополнительные пользователи, создайте их.

Восстановите схему БД в кластере-приемникеВосстановите схему БД в кластере-приемнике

С помощью утилиты pg_restore восстановите схему БД в кластере-приемнике:

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

Создайте публикацию и подпискуСоздайте публикацию и подписку

Для работы логической репликации необходимо создать публикацию (группу логически реплицируемых таблиц) в кластере-источнике и подписку (описание соединения с другой базой) в кластере-приемнике.

1. В кластере-источнике создайте публикацию для всех таблиц базы данных. При переносе нескольких баз для каждой из них нужно создать отдельную публикацию.

   Примечание

   Для создания публикации на все таблицы потребуются права суперпользователя, а для переноса выбранных таблиц — нет. Более подробно о создании публикации см. в документации PostgreSQL.

   Запрос:

   CREATE PUBLICATION p_data_migration FOR ALL TABLES;
   

2. На хосте кластера Managed Service for PostgreSQL создайте подписку со строкой подключения к публикации. Более подробно о создании подписок см. в документации PostgreSQL.

   Запрос с включенным 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;
   

3. Чтобы получить статус репликации, обратитесь к каталогам pg_subscription_rel. Общий статус репликации в кластере-приемнике можно получить через pg_stat_subscription, в кластере-источнике — через pg_stat_replication.

   SELECT * FROM pg_subscription_rel;
   

   Прежде всего обратите внимание на поле srsubstate. Значение r в нем означает, что синхронизация завершилась, и базы готовы к репликации.

Перенесите PostgreSQL-sequences после репликацииПеренесите PostgreSQL-sequences после репликации

Чтобы завершить синхронизацию кластера-источника и кластера-приемника:

1. Переведите кластер-источник в режим только чтение.

2. Создайте дамп с PostgreSQL-sequences в кластере-источнике:

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

   Обратите внимание на используемый паттерн *.*_seq. Если в переносимой базе есть не соответствующие ему sequences, то для их выгрузки укажите другой паттерн.

   Подробнее о паттернах см. в документации PostgreSQL.

3. Восстановите дамп с sequences в кластере-приемнике:

   psql -h <IP-адрес_или_FQDN_хоста-мастера_кластера-приемника> \
        -U <имя_пользователя> \
        -p 6432 \
        -d <имя_БД> \
        < /tmp/seq-data.sql
   

Удалите подписку и перенесите нагрузкуУдалите подписку и перенесите нагрузку

1. Удалите подписку в кластере-приемнике:

   DROP SUBSCRIPTION s_data_migration;
   

2. Перенесите нагрузку на кластер-приемник.

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

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

Перенос данных через создание и восстановление логического дампаПеренос данных через создание и восстановление логического дампа

Создайте дамп нужной базы в кластере-источнике с помощью утилиты pg_dump. Для восстановления дампа в кластере-приемнике используйте утилиту pg_restore.

Этапы миграции:

1. Создайте дамп переносимой базы.
2. (Опционально) Создайте виртуальную машину в Yandex Cloud и загрузите дамп БД на нее.
3. Восстановите данные из дампа в кластер-приемник.

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

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

Создайте необходимые ресурсы:

Создайте дамп базы данныхСоздайте дамп базы данных

1. Переключите базу в режим только чтение.

2. Создайте дамп с помощью утилиты pg_dump. Для ускорения процесса запустите ее в многопоточном режиме, передав в аргументе --jobs количество доступных ядер процессора:

   pg_dump --host=<IP-адрес_или_FQDN_хоста-мастера_кластера-источника> \
           --port=<порт> \
           --username=<имя_пользователя> \
           --jobs=<количество_ядер_процессора> \
           --format=d \
           --dbname=<имя_БД> \
           --file=db_dump
   

(Опционально) Загрузите дамп на виртуальную машину в Yandex Cloud(Опционально) Загрузите дамп на виртуальную машину в Yandex Cloud

Переносить данные на промежуточную виртуальную машину в Compute Cloud нужно, если:

• К вашему кластеру Managed Service for PostgreSQL нет доступа из интернета.
• Ваше оборудование или соединение с кластером в Yandex Cloud недостаточно надежны.

Нужное количество оперативной памяти и ядер процессора зависит от объема переносимых данных и требуемой скорости переноса.

Чтобы подготовить виртуальную машину для восстановления дампа:

1. В консоли управления создайте новую виртуальную машину из образа Ubuntu 20.04 на Marketplace. Параметры виртуальной машины должны зависеть от размера базы, которую вы хотите перенести. Минимальной конфигурации (1 ядро, 2 ГБ RAM, 10 ГБ дискового пространства) должно хватить для переноса базы до 1 ГБ. Чем больше переносимая база, тем больше должно быть оперативной памяти, и тем больше должно быть дискового пространства (как минимум в два раза больше, чем размер базы).

   Виртуальная машина должна находиться в той же сети и зоне доступности, что и кластер PostgreSQL. Кроме того, виртуальной машине должен быть присвоен публичный IP-адрес, чтобы вы могли загрузить дамп извне Yandex Cloud.

2. Настройте apt-репозиторий PostgreSQL.

3. Установите клиент PostgreSQL и дополнительные утилиты для работы с СУБД:

   sudo apt install postgresql-client-common && \
   sudo apt install postgresql-client-<версия_PostgreSQL>
   

4. Упакуйте дамп в архив:

   tar -cvzf db_dump.tar.gz db_dump
   

5. Перенесите архив с дампом на виртуальную машину, например, используя утилиту scp:

   scp db_dump.tar.gz <имя_пользователя_ВМ>@<публичный_адрес_ВМ>:/db_dump.tar.gz
   

6. Подключитесь к виртуальной машине.

7. Распакуйте архив с дампом:

   tar -xzf db_dump.tar.gz
   

Восстановите данные из дампа в кластер-приемникВосстановите данные из дампа в кластер-приемник

Восстановите дамп базы данных с помощью утилиты pg_restore.

Версия pg_restore должна совпадать с версией pg_dump, а мажорная версия должна быть не меньше, чем у базы на которую дамп разворачивается.

Таким образом, для восстановления дампа PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13 и PostgreSQL 14 используйте pg_restore 10, pg_restore 11, pg_restore 12, pg_restore 13, pg_restore 14 соответственно.

pg_restore --host=<IP-адрес_или_FQDN_хоста-мастера_кластера-приемника> \
           --username=<имя_пользователя> \
           --dbname=<имя_БД> \
           --port=6432 \
           --format=d \
           --verbose \
           db_dump \
           --single-transaction \
           --no-privileges

Если нужно восстановить только одну схему, добавьте параметр --schema=<имя_схемы>. Без него команда сработает только от лица владельца базы данных.

Если восстановление прерывается из-за ошибок отсутствия необходимых прав для создания и изменения расширений, уберите из команды параметр --single-transaction. В этом случае ошибки будут проигнорированы:

pg_restore: warning: errors ignored on restore: 3

Убедитесь, что ошибки относятся только к расширениям, и проверьте целостность восстановленных данных.

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

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