[Перевод] Мигрируем с SQL Server на PostgreSQL двумя способами25.03.2024 23:30

15128838688a746412a47d8898212c34.png

Будучи одной из самых популярных баз данных, SQL Server славится простотой установки и настройки, функциями безопасности, среди которых есть шифрование, великолепными возможностями восстановления данных и множеством удобных инструментов.

Однако из-за ряда ограничений SQL Server постепенно теряет своих пользователей. SQL Server имеет достаточно сковывающую лицензию и стоимость обслуживания, растущую по мере увеличения размера базы данных или числа клиентов. Ее максимальный размер составляет 10 ГБ, а буферный кэш — 1 МБ. Она работает только под Windows.

Переманить же пользователей SQL Server может PostgreSQL — полностью бесплатная база данных с открытым исходным кодом. Эта база данных может похвастаться поддержкой международного сообщества и доступна под Windows, Mac, Linux, FreeBSD и Solaris. Кроме того, для нее существуют множество опенсорсных дополнений.

Я начну эту статью со знакомства с двумя бесплатными инструментами для миграции с SQL Server на PostgreSQL, затем поэтапно продемонстрирую, как выполнить миграцию между этими двумя базами данных, а в конце расскажу о полноценном решении для резервного копирования с защитой для управления сразу несколькими базами данных.

Инструменты миграции базы данных:

1.    Pgloader

Pgloader — это программа загрузчик данных, которая производит миграцию из MS SQL Server в PostgreSQL. Инструмент автоматически обнаруживает схему (первичный ключ, ограничения внешних ключей и структуру индексов) и предоставляет правила приведения для преобразования типа данных SQL Server в тип данных PostgreSQL.

2.    Sqlserver2pgsql

Sqlserver2pgsql — еще один инструмент миграции с открытым исходным кодом, написанный на Perl, который конвертирует схему SQL Server в PostgreSQL и переводит все данные SQL Server в PostgreSQL с помощью полученного Pentaho Data Integrator (Kettle). Однако он не переносит хранимые процедуры. Этот инструмент лучше использовать для миграции базы данных в автономном режиме. Найти его можно на Github.

Он должен работать на Linux, Windows и любой Unix-системе.

Способ 1: Миграция с SQL Server на PostgreSQL с помощью SSIS

  1. Установите PostgreSQL ODBC Driver для Windows x86/x64.

  2. В SQL Server Management Studio кликните правой кнопкой мыши по исходной базе данных и выберите Tasks> Export Data.

  3. Выберите SQL Server Native Clients в качестве источника данных и .Net Framework Data Provider for ODBC в качестве драйвера назначения (destination driver).

  4. Установите строку подключения к базе данных. Добавьте имя источника данных, которое соответствует значению, заданному для Driver= {имя-вашего-источника-данных} для драйвера PostgreSQL.

Для 32-битной системы

Driver={PostgreSQL UNICODE};Server=;Port=;Database=yourDBname;Uid=postgres;Pwd=admin;

Для 64-битной системы

Driver={PostgreSQL UNICODE (x64) };Server=;Port=;Database=yourDBname;Uid=postgres;Pwd=admin;

  1. Выберите исходные таблицы. Вы также можете отредактировать стандартное отображение SQL Server.

Способ 2: Миграция с SQL Server на PostgreSQL с помощью Sqlserver2pgsql

Что нам нужно:

  • Данные о подключении к SQL Server, включая IP, порт, имя пользователя, пароль, имена баз данных и инстансовов (если они не установлены по умолчанию).

  • Доступ к пустой целевой базе данных PostgreSQL.

Создаем образ Docker

Docker — это отличный инструмент для создания процедуры миграции баз данных, которая не будет зависеть от облака и может быть применена в большинстве IT-архитектур.

Создаем образ на OpenJDK 8, который позволяет создавать и запускать приложения Java 1.8.

FROM adoptopenjdk/openjdk8

Определяем ряд переменных окружения для обеих баз данных

ENV SRC_HOST=

ENV SRC_PORT=

ENV SRC_USER=

ENV SRC_PWD=

ENV SRC_DB=

Настраиваем рабочий каталог

ENV MIGRATIONDIR=/opt/data_migration

RUN mkdir -p $MIGRATIONDIR

Устанавливаем несколько инструментов для работы с клиентом

RUN apt-get update; apt-get install perl netcat -y; \

apt-get install wget unzip postgresql-client -y

Устанавливаем Kettle для запуска миграции

RUN wget --progress=dot:giga https://sourceforge.net/projects/pentaho/files/latest/download?source=files -O /tmp/kettle.zip; \

unzip /tmp/kettle.zip -d /tmp/kettle; \

mv /tmp/kettle/data-integration $MIGRATIONDIR; \

chmod -R +x $MIGRATIONDIR/data-integration/*.sh

Устанавливаем jTDS для подключения SQL Server в Kettle-джобе 

RUN wget https://sourceforge.net/projects/jtds/files/latest/download?source=files -O /tmp/jtds.zip; \

unzip /tmp/jtds.zip -d /tmp/jtds; \

cp /tmp/jtds/jtds-*.jar $MIGRATIONDIR/data-integration/lib/; \

rm -Rf /tmp/jtds;rm -f /tmp/jtds.zip

Настраиваем .sh-скрипт для запуска Kettle-джобы 

COPY ./scripts /scripts

RUN chmod +x /scripts/*.sh

WORKDIR $MIGRATIONDIR

Настраиваем скрипт миграции

Сначала запустим bash

#!/bin/bash

Настроим его на отказ, если его команды не выполняются

set -e

Преобразуем схему SQL Server в скрипты PostgreSQL

echo !!! Creating Kettle job && \

./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettlejobs -stringtype_unspecified -f conf/$SCHEMA_FILE \

  -sh $SRC_HOST -sp $SRC_PORT -su $SRC_USER -sw $SRC_PWD -sd $SRC_DB \

  -ph $DST_HOST -pp $DST_PORT -pu $DST_USER -pw $DST_PWD -pd $DST_DB

Файлы Kettle-джобы будут храниться в $MIGRATIONDIR/kettlejobs/migration.kjb.

Запускаем скрипт before.sql, содержащий SQL-команды Postgres, используемые для создания структуры таблицы

Используя для аутентификации PostgreSQL SSL-сертификаты (обратите внимание на параметры sslmode, sslrootcert, sslcert и sslkey, необходимые для этого)

echo !!! Executing before.sql && \

# restricting access to key file as per psql requirements:

chmod 0600 conf/client-key.pem && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -v sslmode=verify-ca -v sslrootcert=conf/server-ca.pem -v sslcert=conf/client-cert.pem -v sslkey=conf/client-key.pem -f before.sql

Используя для аутентификации PostgreSQL логин/пароль

echo !!! Executing before.sql && \

# restricting access to key file as per psql requirements:

chmod 0600 conf/client-key.pem && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -f before.sql

Выполняем Kettle-джобу

echo !!! Running Kettle job && \

data-integration/kitchen.sh -file=kettlejobs/migration.kjb -level=rowlevel

Запускаем скрипт after.sql

Используя для аутентификации PostgreSQL SSL-сертификаты (обратите внимание на параметры sslmode, sslrootcert, sslcert и sslkey, необходимые для этого)

echo !!! Executing after.sql && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -v sslmode=verify-ca -v sslrootcert=conf/server-ca.pem -v sslcert=conf/client-cert.pem -v sslkey=conf/client-key.pem -f after.sql

Используя для аутентификации PostgreSQL логин/пароль

echo !!! Executing after.sql && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -f after.sql

Создаем образ Docker-контейнера 

docker build -t sqlserver2psql .

Экспорт схемы с помощью SQL Server Management Studio

  1. В SQL Server Management Studio кликните правой кнопкой мыши по исходной базе данных. Затем выберите Tasks> Generate Scripts.

  2. Выберите таблицы для экспорта и нажмите кнопку Next.

  3. На панели Set Scripting Options выберите Save as a script file> Single script file, чтобы сохранить файл schema.sql в <корневой каталог проекта> / conf (при необходимости создайте такой файл). Установите флажок в поле Unicode text. Нажмите на Next.

  4. На странице обзора нажмите кнопку Next. После завершения процесса нажмите Finish.

  5. Поместите файл на сервер, где запущен sqlserver2pgsql.

Загружаем сертификаты для аутентификации PostgreSQL с помощью SSL

Загрузите сертификаты и скопируйте их в <корневой каталог проекта>/conf.

Файлы сертификатов должны иметь имена: server-ca.pem, client-cert.pem и client-key.pem.

Мигрируем с SQL Server на PostgreSQL

docker run --name sqlserver2psql --rm -e SRC_HOST= \

  -e SRC_USER= -e SRC_PWD="" \

  -e SRC_DB= -e DST_HOST= \

  -e DST_PORT=5432 -e DST_USER= \

  -e DST_PWD= -e DST_DB=""

  --mount type=bind,source="$(pwd)"/conf,target=/opt/data_migration/conf \

  sqlserver2psql /scripts/migrate.sh

Заключение

SQL Server и PostgreSQL — мощные и популярные среди пользователей по всему миру базы данных, и некоторым пользователям может потребоваться преобразовать свою базу данных SQL Server в PostgreSQL с целью получения большей свободы и экономии средств (см. как мигрировать с PostgreSQL на SQL Server).

В этой статье были продемонтрированы два способа миграции баз данных с помощью SSIS и инструмента с открытым исходным кодом sqlserver2pqsql.

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

В заключение приглашаем всех желающих посетить открытый урок 26 марта в 20:00, на котором проведем обзор автоматизированных средств миграции с MS SQL Server на PostgreSQL. Записаться можно по ссылке.

Habrahabr.ru прочитано 12139 раз