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

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

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

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

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

Прежде чем собирать и устанавливать tds_fdw, необходимо установить библиотеку FreeTDS. Для 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. Разработчики радостно его приняли в тот же день. Перед этим они за пару часов исправили скрипты для тестирования, которые реализованы на 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).

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

Таблица сопоставления типов

Тип MS SQL Server	Тип PostgreSQL
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 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 rowversion timestamp	bytea
xml	xml

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

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

UPDATE Обновилась таблица сопоставления типов. Теперь типы rowversion и timestamp MS SQL Server соответствуют типу bytea PostgreSQL (см. коммит).

Ссылки

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