Мигрируем DWH с MS SQL Server 2008 на PostgreSQL 9.5: fdw_postgres и UPSERT как инструменты поэтапного перехода

У одного из госклиентов сошлись сразу два мотива для смены СУБД: реестр Минкомсвязи с требованием обосновывать закупку иностранного ПО и MS SQL Server 2008, который Microsoft снял с основной поддержки ещё в 2014 году. Аналитическое хранилище на SQL Server 2008 R2, несколько ETL-процессов на SSIS, пара десятков хранимых процедур на T-SQL - вот с чем мы работаем последние пару месяцев.

PostgreSQL 9.5 как основной кандидат для замены мы рассматривали без особых колебаний: для DWH и аналитических задач это сейчас самый зрелый вариант в реестре, тем более что с января у нас уже есть опыт работы с UPSERT в 9.5. Вопрос был не «что», а «как» переходить, не роняя при этом продуктивную работу аналитиков.

Подход: fdw_postgres как мост, а не разовый дамп

Перенести данные через dump/restore - несложно. Сложно перенести работающий пайплайн так, чтобы старый источник и новый приёмник работали параллельно, а переключение произошло в управляемый момент. Для этого мы используем postgres_fdw - расширение, которое позволяет обращаться к внешней PostgreSQL-базе как к локальным таблицам.

Схема выглядит так: разворачиваем PostgreSQL 9.5, создаём foreign server, указывающий обратно на MS SQL через tds_fdw (расширение для подключения к Sybase/MS SQL по протоколу TDS). Загружаем данные в PostgreSQL, ETL-процессы постепенно переключаем один за другим.

-- Подключение к MS SQL через tds_fdw
CREATE EXTENSION tds_fdw;

CREATE SERVER mssql_source
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername '10.0.1.15', port '1433', database 'DWH_PROD');

CREATE USER MAPPING FOR dwh_user
  SERVER mssql_source
  OPTIONS (username 'etl_reader', password '...');

-- Пример foreign table для исторических данных
CREATE FOREIGN TABLE ft_fact_sales (
  sale_id     INT,
  sale_date   DATE,
  amount      NUMERIC(18,2),
  customer_id INT
)
SERVER mssql_source
OPTIONS (query 'SELECT sale_id, sale_date, amount, customer_id FROM dbo.fact_sales');

После этого первоначальная загрузка - это просто INSERT INTO fact_sales SELECT * FROM ft_fact_sales. Инкрементальные обновления дальше идут через UPSERT: при каждом цикле ETL берём дельту из источника и применяем ON CONFLICT DO UPDATE. Логика конфликта прямо в запросе, без промежуточных DELETE.

tds_fdw не идеален - производительность при больших выборках через него заметно ниже, чем прямой bulk export. Для первоначальной загрузки крупных таблиц (у клиента несколько таблиц фактов от 50 млн строк) мы всё равно использовали BCP на стороне SQL Server плюс COPY в PostgreSQL. fdw удобен для инкрементального режима и для небольших справочников.

Матрица совместимости T-SQL / PL-pgSQL

Самая трудоёмкая часть - хранимые процедуры. Процедур не очень много, но в них накоплена бизнес-логика, которую нельзя потерять при переписывании. Вот что мы встретили чаще всего:

TOP N в SELECT. В T-SQL SELECT TOP 100 * FROM t - в PL-pgSQL это SELECT * FROM t LIMIT 100. Механика та же, но синтаксис другой. Особенность T-SQL: TOP без ORDER BY возвращает произвольные строки - и в процедурах на это иногда неявно рассчитывают.

ISNULL() vs COALESCE(). В T-SQL ISNULL(col, 0) - прямой аналог в PostgreSQL - COALESCE(col, 0). COALESCE есть в обоих диалектах, так что проще заменять сразу на COALESCE.

GETDATE() vs NOW(). В T-SQL текущая дата/время - GETDATE(), в PostgreSQL - NOW() или CURRENT_TIMESTAMP. Оба возвращают timestamp с временной зоной, поведение идентичное.

Строковые функции. CHARINDEX(substr, str) в T-SQL - это POSITION(substr IN str) или STRPOS(str, substr) в PostgreSQL. LEN(str) - это LENGTH(str). SUBSTRING(str, start, len) - одинаково.

CONVERT() и CAST(). CAST стандартный, работает в обоих. CONVERT(VARCHAR, col, 104) с форматными кодами - специфика T-SQL, в PostgreSQL заменяется на TO_CHAR(col, 'DD.MM.YYYY').

Временные таблицы. #temp_table в T-SQL - в PostgreSQL это обычные TEMP TABLE, синтаксис CREATE TEMP TABLE t AS SELECT ... работает. Из неочевидного: в PostgreSQL temp-таблицы живут в течение сессии, в T-SQL - в течение процедуры, если не указано иное. Это влияет на то, как процедуры передают данные между собой.

SET NOCOUNT ON. В T-SQL это стандартное начало процедуры - подавляет вывод «N rows affected». В PostgreSQL этого нет, функции по умолчанию не выводят такие сообщения.

Из серьёзного, что нас задержало: несколько процедур использовали MERGE - операцию, которой в PostgreSQL нет. В 9.5 именно для таких случаев UPSERT (INSERT ON CONFLICT) закрывает большинство паттернов, но MERGE с несколькими источниками или сложными условиями совпадения приходится разбирать вручную и переписывать логику.

Где реально сложно

SSIS-пакеты. Это отдельная боль. SSIS - проприетарный ETL-инструмент, его «просто перенести» нельзя. Мы смотрим на Pentaho Data Integration (Kettle) как на замену - он умеет работать с PostgreSQL из коробки, и концептуально близок к SSIS по модели трансформаций. Но это отдельный проект, не один спринт.

Отчёты через SSRS. Клиент использует SQL Server Reporting Services для нескольких регулярных отчётов. SSRS умеет подключаться к PostgreSQL через ODBC, так что отчёты технически остаются на месте - меняется только строка подключения. Проверили на двух отчётах - работает, но производительность нужно смотреть под реальной нагрузкой.

Агентские задания (SQL Server Agent). Расписания запуска ETL сейчас в SQL Server Agent. Переносим на pg_cron - расширение для PostgreSQL, которое позволяет запускать SQL по расписанию прямо в базе.

Где мы сейчас

Данные перенесены, часть ETL-процессов переключена на PostgreSQL. Хранимые процедуры - в процессе переписывания, примерно половина готова. Параллельный запуск показывает расхождения только в одном месте - там была специфическая логика с MERGE, разбираем.

Клиент смотрит на это спокойно - регуляторное требование закрывается, функциональность не деградирует. Для нас это первый полноценный кейс перехода DWH с MS SQL на PostgreSQL под импортозамещение, и он подтверждает: техническая часть решаема, если подходить поэтапно, а не одним прыжком.