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

    В предыдущей статье мой коллега Дмитрий Васильев описал настройку интеграции 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 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. Список оберток сторонних данных
    Postgres Professional
    307,00
    Разработчик СУБД Postgres Pro
    Поделиться публикацией

    Похожие публикации

    Комментарии 11

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

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

        0

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

        +1
        А почему
        datetime
        datetime2
        smalldatetime
        timestamp сопоставляются timestamp without time zone?
        timestamp в MSSQLSERVER — ни разу не дата/время!

        timestamp
        Тип данных timestamp является синонимом типа данных rowversion и подчиняется правилам поведения синонимов типов данных. В инструкциях на языке описания данных DDL по возможности используйте rowversion вместо timestamp. Дополнительные сведения см. в статье Синонимы типов данных (Transact-SQL).
        Тип данных Transact-SQL timestamp отличается от типа данных timestamp, определенного в стандарте ISO.
        Примечание

        Синтаксис timestamp является нерекомендуемым. Этот компонент находится в режиме обслуживания и может быть удален в будущей версии Microsoft SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
          0

          Спасибо. Вы правы! Это было уже изменено. Сейчас rowversion и timestamp соответствуют типу bytea. Я обновил таблицу.

          0

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

          Самое читаемое