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

в 9:13, , рубрики: fdw, Microsoft SQL Server, open source, postgresql, sql server, Блог компании Postgres Professional

В предыдущей статье мой коллега Дмитрий Васильев описал настройку интеграции 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. Список оберток сторонних данных

Автор: Postgres Professional

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js