Интеграция PostgreSQL с MS SQL Server

В предыдущей статье ^[1] мой коллега Дмитрий Васильев ^[2] описал настройку интеграции PostgreSQL с MySQL и описал, как более эффективно выполнять некоторые запросы.

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

Установка и настройка tds_fdw

Для интеграции PostgreSQL и MS SQL Server используется tds_fdw ^[3]. Этот модуль общается с базой данных через протокол TDS ^[4] (Tabular Data Stream). TDS используется такими СУБД, как MS SQL Server и Sybase SQL Server.

Прежде чем собирать и устанавливать tds_fdw, необходимо установить библиотеку FreeTDS ^[5]. Для Ubuntu это пакеты freetds-dev и freetds-common:

sudo apt-get install freetds-dev freetds-common

Далее скачиваем и собираем tds_fdw (для сборки нам также понадобится установленный PostgreSQL и pg_config в переменной окружения PATH):

git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make USE_PGXS=1 install

Перед использованием tds_fdw необходимо настроить FreeTDS. Настройка FreeTDS для подключения к MS SQL Server выполняется с помощью файла /etc/freetds/freetds.conf. Например, добавим такое содержимое:

[mssql01]
        host = 192.168.0.1
        port = 1433
        tds version = 7.1
        instance = MSSQL01

Теперь мы можем создать необходимые объекты в PostgreSQL:

-- Сам модуль
CREATE EXTENSION tds_fdw;
-- Сервер
CREATE SERVER sql01 FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername 'mssql01', database 'test_sql01', msg_handler 'notice');
-- Сопоставление пользователя
CREATE USER MAPPING FOR pguser SERVER sql01 OPTIONS (username 'msuser', password 'userpass');

Здесь, mssql01 — название сервера в freetds.conf, pguser — пользователь PostgreSQL, msuser — пользователь MS SQL Server.

После этого мы бы могли создать для каждой таблицы MS SQL Server внешнюю таблицу в PostgreSQL. Но вместо этого мы можем использовать команду IMPORT FOREIGN SCHEMA.

Импорт определения таблиц с MS SQL Server

Команда IMPORT FOREIGN SCHEMA была реализована в PostgreSQL 9.5. Его поддержку реализовали такие обертки сторонних данных, как oracle_fdw, mysql_fdw. Но не было поддержки у tds_fdw.

Для одного из наших проектов также необходима была поддержка этой команды со стороны tds_fdw. Нами команда была реализована и был создан pull request ^[6]. Разработчики радостно его приняли в тот же день. Перед этим они за пару часов исправили скрипты для тестирования, которые реализованы на Python, т.к. тесты выполняются для PostgreSQL 9.2, 9.3, 9.4 и 9.5. Но команда IMPORT FOREIGN SCHEMA была добавлена только в версии 9.5.

Теперь мы можем выполнить такую команду:

IMPORT FOREIGN SCHEMA msschema01 FROM SERVER sql01
    INTO pgschema01
    OPTIONS (import_default 'true');

Схемы msschema01 и pgschema01 уже должны существовать. Команда принимает следующие опции:

• import_default — добавлять или нет выражение DEFAULT при описании столбцов таблиц (по умолчанию, false).
• import_not_null — добавлять или нет ограничение NOT NULL при описании столбцов таблиц (по умолчанию, true).

При импорте описания столбцов таблиц используется следующее сопоставление типов:

bit
smallint
tinyint

smallint

int

integer

bigint

bigint

decimal(p[ ,s])

decimal(p[ ,s])

numeric(p[ ,s])

numeric(p[ ,s])

money
smallmoney

money

float
float(n), где 25<=n<=53

double precision

real
float(n), где 1<=n<=24

real

date

date

datetime
datetime2
smalldatetime
timestamp

timestamp without time zone

datetimeoffset

timestamp with time zone

time

time

char(n)
nchar(n)

char(n)

varchar(n)
nvarchar(n)

varchar(n)

varchar(MAX)
text
ntext

text

binary
varbinary
image

bytea

xml

xml

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

Спасибо за внимание!

Ссылки

1. Скачать tds_fdw ^[3]
2. Документация по Foreign Data Wrapper ^[7] (en)
3. Документация по Foreign Data Wrapper ^[8] (ru)
4. Список оберток сторонних данных ^[9]

Автор: Postgres Professional

Источник ^[10]