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


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


В этой статье я хотел бы описать настройку подключения 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
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
  2. Документация по Foreign Data Wrapper (en)
  3. Документация по Foreign Data Wrapper (ru)
  4. Список оберток сторонних данных
Поделиться с друзьями
-->

Комментарии (8)


  1. kxl
    08.09.2016 13:27

    Есть ли такая возможность для PostgreSQL, работающего под управлением Windows?


    1. iscsi
      08.09.2016 13:43

      Посмотрите здесь https://github.com/tds-fdw/tds_fdw/issues/53


      1. select_artur
        08.09.2016 14:03

        Хорошая ссылка. lorenati смог собрать tds_fdw, имея исходники PostgreSQL, и написал об этом пост: http://hadoopandstuff.blogspot.ru/2016/07/compile-tdsfdw-in-windows.html


      1. kxl
        08.09.2016 14:25

        Спасибо, нашел там ссылку Compile tds_fdw in Windows… Попробую…
        Нда, надо обновлять страницу…


    1. select_artur
      08.09.2016 13:44

      Теоретически такая возможность есть. Сам разработчик не предоставляет скомпилированные версии tds_fdw. И он не входит в наш PostgresPro. Теоретически можно собрать tds_fdw следующим образом:
      1 — необходимо скачать или собрать FreeTDS под Windows (http://www.freetds.org/userguide/osissues.htm#WINDOWS)
      2 — собрать tds_fdw под Windows. Есть полезные статьи https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules и http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/.
      Я сам еще не пробовал собирать tds_fdw под Windows. Возможно попробую и напишу об этом статью.


      1. kxl
        08.09.2016 13:49

        Спасибо, буду посмотреть :)


  1. iscsi
    08.09.2016 13:38

    Далее скачиваем и собираем tds_fdw

    Через pgxnclient это сделать проще, а в yum PGDG есть готовый пакет tds_fdw.


    1. vadv
      08.09.2016 13:54

      Я уже писал об этом, для стейджинга/прода вы можете использовать пакет вашего администратора/дистрибутива/сообщества. А у себя на ноутбуке — да, можно и нужно собрать из исходников, хотя бы для того, чтобы понять что это и как оно работает, если вы его собираетесь использовать в продуктиве.