MaxRokatansky 25 мар в 23:17

Мигрируем с SQL Server на PostgreSQL двумя способами

6 мин

15K

Блог компании OTUSPostgreSQL*Microsoft SQL Server*

Перевод

Будучи одной из самых популярных баз данных, 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

Установите PostgreSQL ODBC Driver для Windows x86/x64.
В SQL Server Management Studio кликните правой кнопкой мыши по исходной базе данных и выберите Tasks → Export Data.
Выберите SQL Server Native Clients в качестве источника данных и .Net Framework Data Provider for ODBC в качестве драйвера назначения (destination driver).
Установите строку подключения к базе данных. Добавьте имя источника данных, которое соответствует значению, заданному для 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;

Выберите исходные таблицы. Вы также можете отредактировать стандартное отображение 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

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

В SQL Server Management Studio кликните правой кнопкой мыши по исходной базе данных. Затем выберите Tasks → Generate Scripts.
Выберите таблицы для экспорта и нажмите кнопку Next.
На панели Set Scripting Options выберите Save as a script file → Single script file, чтобы сохранить файл schema.sql в <корневой каталог проекта> / conf (при необходимости создайте такой файл). Установите флажок в поле Unicode text. Нажмите на Next.
На странице обзора нажмите кнопку Next. После завершения процесса нажмите Finish.
Поместите файл на сервер, где запущен sqlserver2pgsql.

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

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

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

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

docker run --name sqlserver2psql --rm -e SRC_HOST=<SQL Server host> \

  -e SRC_USER=<SQL Server username> -e SRC_PWD="<SQL Server password>" \

  -e SRC_DB=<SQL Server db name> -e DST_HOST=<PostgreSQL host> \

  -e DST_PORT=5432 -e DST_USER=<PostgreSQL username> \

  -e DST_PWD=<PostgreSQL password> -e DST_DB="<PostgreSQL db name>"

  --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. Записаться можно по ссылке.

Теги:

Хабы: