Основные причины для миграции#

1. Снижение затрат. PostgreSQL — это полностью бесплатная и открытая СУБД, распространяемая под лицензией PostgreSQL. В отличие от MSSQL, который требует оплаты лицензий (как на использование, так и на масштабирование), PostgreSQL позволяет значительно сократить затраты на эксплуатацию.
2. Открытый исходный код. PostgreSQL — это open-source проект с большим сообществом разработчиков. Это даёт пользователям доступ к исходному коду, что открывает широкие возможности для кастомизации и интеграции с другими решениями.
3. Кроссплатформенность. PostgreSQL работает на различных операционных системах, включая Linux, Windows и macOS. Это делает её более гибкой для компаний, которые используют разнородные технологические стеки.
4. Широкий функционал. PostgreSQL предлагает мощные функции, такие как поддержка JSON/JSONB, сложные типы данных, расширяемость за счёт пользовательских функций и модулей, а также надёжные механизмы репликации и масштабирования.
5. Соответствие стандартам. PostgreSQL является одной из самых стандартизированных СУБД (SQL:2011). Это упрощает миграцию и делает базу данных более универсальной для работы с различными системами.

Плюсы PostgreSQL#

Минусы PostgreSQL#

Кому подойдёт миграция?#

Переход с MSSQL на PostgreSQL особенно актуален для:

• Стартапов и малых бизнесов, которые хотят минимизировать затраты на инфраструктуру.
• Компаний, стремящихся к независимости от крупных вендоров.
• Организаций, работающих с большими объёмами данных или сложными типами данных.
• IT-команд, готовых инвестировать время в настройку и оптимизацию новой СУБД.

Этап 1. Анализ текущей базы данных#

Перед началом миграции необходимо:

• Провести инвентаризацию всех объектов базы данных, включая таблицы, индексы, хранимые процедуры и триггеры.
• Оцените, сколько данных необходимо перенести, и подготовьтесь к возможным ограничениям производительности.
• Проверить совместимость типов данных и функций.

Сложные элементы

Хранимые процедуры, функции и специфичные для MSSQL конструкции требуют ручной доработки.

Этап 2. Подготовка к миграции#

• Установите PostgreSQL и настройте сервер в соответствии с требованиями проекта.
• Выберите инструмент для миграции данных (например, pgloader, ora2pg, или пользовательские скрипты).
• Разработайте план миграции, учитывающий зависимости между объектами базы данных.

Этап 3. Создание схемы базы данных в PostgreSQL#

• Переведите структуру таблиц из MSSQL в PostgreSQL, учитывая различия в синтаксисе (описаны ниже).
• Адаптируйте индексы, ограничения и связи.

Этап 4. Перенос данных#

• Экспортируйте данные из MSSQL в формат, совместимый с PostgreSQL (например, CSV).
• Загрузите данные в PostgreSQL, используя утилиты вроде COPY или инструменты миграции.
• Убедитесь, что данные корректно перенесены, проведя выборочную проверку.

Этап 5. Адаптация SQL-запросов и бизнес-логики#

• Измените хранимые процедуры, триггеры и представления для работы с PostgreSQL.
• Перепишите специфичные запросы с учетом различий в синтаксисе.

Этап 6. Тестирование и оптимизация#

• Проведите функциональное и нагрузочное тестирование на новой базе данных.
• Оптимизируйте запросы и настройте параметры PostgreSQL для достижения высокой производительности.

Этап 7. Переход на новую базу данных#

• Перенаправьте приложение на использование PostgreSQL.
• Оставьте резервную копию MSSQL на случай необходимости отката.

Типы данных#

CREATE TABLE transactions (
    amount MONEY
);

CREATE TABLE transactions (
    amount NUMERIC(15, 2)  -- 15 цифр, из которых 2 после запятой
);

CREATE TABLE example (
    is_active BIT
);
INSERT INTO example (is_active) VALUES (1), (0);

CREATE TABLE example (
    is_active BOOLEAN
);
INSERT INTO example (is_active) VALUES (TRUE), (FALSE);

Работа с идентификаторами#

В MSSQL идентификаторы оборачиваются в квадратные скобки [column_name]. Это полезно, если имя содержит пробелы или специальные символы. В PostgreSQL вместо этого используются двойные кавычки: "column_name". PostgreSQL чувствителен к регистру идентификаторов, если они заключены в кавычки.

MSSQL по умолчанию регистронезависим, даже если идентификаторы заключены в скобки. PostgreSQL чувствителен к регистру только тогда, когда идентификатор заключен в двойные кавычки. Например:

SELECT "Column_Name" FROM table_name;

В этом случае "Column_Name" и column_name будут считаться разными именами.

Практическое значение

При миграции из MSSQL нужно убедиться, что имена объектов корректно адаптированы к правилам PostgreSQL. Если в MSSQL использовались скобки, их необходимо заменить на кавычки.

Также важно унифицировать регистр имен (обычно используются строчные буквы в PostgreSQL) во избежание путаницы.

Автоинкремент#

Рассмотрим как в MSSQL и PostgreSQL реализуется автоматическая генерация значений для первичных ключей или других колонок, которые требуют последовательного увеличения (автоинкремент).

• В MSSQL для автоинкремента используется IDENTITY:

CREATE TABLE example (
    id INT IDENTITY(1,1), -- '1,1' означает, что автоинкремент начинается с 1 и увеличивается на 1
    name NVARCHAR(100)
);

Особенности

Значение автоматически генерируется при вставке новых записей.

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

• В PostgreSQL используется SERIAL или GENERATED:
  1. SERIAL — удобный синтаксический сахар для автоматической генерации последовательности.
  2. GENERATED ALWAYS AS IDENTITY — современный стандарт SQL.

Пример с SERIAL:

CREATE TABLE example (
    id SERIAL, -- создается последовательность, связанная с колонкой
    name VARCHAR(100)
);

Пример с GENERATED:

CREATE TABLE example (
    id INT GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100)
);

Различия:

• Гибкость:

  • В PostgreSQL последовательности, созданные с SERIAL, можно явно изменять или использовать в других контекстах.
  • IDENTITY в MSSQL менее гибкий.

• Совместимость:

  • SERIAL проще использовать для быстрого создания автоинкрементных колонок.
  • GENERATED рекомендуется для проектов, где важна поддержка современных стандартов SQL.

• Миграция:

  • При переносе таблиц из MSSQL в PostgreSQL, строки с IDENTITY обычно преобразуются в SERIAL или GENERATED.

LIMIT и OFFSET#

В MSSQL для этого есть два ключевых подхода:

1. TOP: Используется для ограничения числа строк.

SELECT TOP 10 * FROM table_name;

2. OFFSET FETCH: Этот метод позволяет пропустить определенное количество строк и взять фиксированное количество следующих.

SELECT * 
FROM table_name 
ORDER BY column_name 
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

В PostgreSQL для тех же целей используются LIMIT и OFFSET:

1. LIMIT: Указывает максимальное количество строк, которые нужно вернуть.

SELECT * FROM table_name LIMIT 10;

2. OFFSET: Указывает, сколько строк нужно пропустить перед возвратом результата.

SELECT * 
FROM table_name 
ORDER BY column_name 
OFFSET 5 LIMIT 10;

Различия:

• MSSQL требует обязательного использования ORDER BY с OFFSET FETCH, чтобы результат был предсказуемо упорядочен.
• PostgreSQL не требует ORDER BY для LIMIT и OFFSET, но его использование рекомендуется для получения детерминированного результата.
• PostgreSQL имеет более простой и интуитивно понятный синтаксис.

Объединение строк#

В MSSQL для объединения строк используется оператор +.

SELECT first_name + ' ' + last_name AS full_name FROM users;

Особенности

Если один из объединяемых элементов равен NULL, весь результат становится NULL. Чтобы избежать этого, можно использовать функцию ISNULL:

SELECT ISNULL(first_name, '') + ' ' + ISNULL(last_name, '') AS full_name FROM users;

В PostgreSQL для объединения строк используется оператор ||.

SELECT first_name || ' ' || last_name AS full_name FROM users;

Особенности

Если один из объединяемых элементов равен NULL, весь результат становится NULL. Чтобы избежать этого, можно использовать функцию COALESCE:

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;

Приведение типов

В PostgreSQL перед объединением может потребоваться явное приведение типов числовых данных к строковым с помощью функции CAST или ::TEXT.

Пустые строки и NULL#

В PostgreSQL пустая строка (”) и NULL различаются, тогда как в MSSQL они могут трактоваться одинаково в некоторых случаях. Это связано с особенностями настроек совместимости или функций, таких как ISNULL, которые позволяют заменить NULL значением по умолчанию.

В PostgreSQL пустая строка (”) и NULL считаются абсолютно разными понятиями:

• ” — это строка длиной 0, то есть пустая, но существующая.
• NULL — это отсутствие значения.

Это различие особенно важно при сравнениях. Например:

SELECT '' IS NULL; -- вернет FALSE
SELECT NULL IS NULL; -- вернет TRUE

UPDATE table_name
SET column_name = NULL
WHERE column_name = '';

SELECT COALESCE(column_name, '') AS result FROM table_name;

Работа с датами#

В MSSQL для получения текущего времени используется функция GETDATE(). Эта функция возвращает дату и время в формате DATETIME, включающем год, месяц, день, часы, минуты, секунды и доли секунды.

В PostgreSQL используется функция CURRENT_TIMESTAMP. Она возвращает аналогичный результат, включающий дату и время, но в формате TIMESTAMP WITH TIME ZONE (если не указанно иначе).

Различия в форматах и функциях:

• Тип данных:

  • В MSSQL типы DATETIME и SMALLDATETIME представляют дату и время, но они имеют разную точность и диапазон.
  • В PostgreSQL используется TIMESTAMP (без часового пояса) и TIMESTAMP WITH TIME ZONE, что позволяет явно учитывать часовые пояса.

• Функции преобразования:

  • В MSSQL для приведения даты к строке используется CONVERT:

  SELECT CONVERT(VARCHAR, GETDATE(), 101); -- MM/DD/YYYY
  

  • В PostgreSQL используется функция TO_CHAR:

  SELECT TO_CHAR(CURRENT_TIMESTAMP, 'MM/DD/YYYY');
  

Сравнение и операции с датами:

• Добавление или вычитание дат:

  • В MSSQL используется функция DATEADD:

  SELECT DATEADD(DAY, 7, GETDATE()); -- Добавить 7 дней
  

  • В PostgreSQL используется сложение с интервалами:

  SELECT CURRENT_TIMESTAMP + INTERVAL '7 days';
  

• Разница между датами:

  • В MSSQL используется DATEDIFF:

  SELECT DATEDIFF(DAY, '2023-12-01', '2023-12-31');
  

  • В PostgreSQL это выполняется простым вычитанием:

  SELECT '2023-12-31'::DATE - '2023-12-01'::DATE;
  

Миграция

Если в MSSQL используется DATETIME, его нужно преобразовать в TIMESTAMP PostgreSQL.

Нужно учитывать, поддерживает ли приложение работу с часовыми поясами, так как PostgreSQL различает TIMESTAMP и TIMESTAMP WITH TIME ZONE.

Хранимые процедуры и функции#

В MSSQL хранимые процедуры (stored procedures) создаются с помощью ключевого слова CREATE PROCEDURE. Они используются для выполнения набора SQL-запросов с возможностью передачи параметров. Результаты можно возвращать через параметры или использовать в SQL-запросах.

CREATE PROCEDURE example_procedure
    @param1 INT,
    @param2 NVARCHAR(50)
AS
BEGIN
    INSERT INTO table_name (column1, column2)
    VALUES (@param1, @param2);
END;

Вызов:

EXEC example_procedure 10, 'Sample Text';

В PostgreSQL вместо хранимых процедур чаще используются функции. Они создаются с помощью ключевого слова CREATE FUNCTION. Функции могут возвращать значения (таблицы, отдельные строки, примитивные типы данных и т.д.).

CREATE OR REPLACE FUNCTION example_function(param1 INT, param2 TEXT) RETURNS VOID AS $$
BEGIN
    INSERT INTO table_name (column1, column2)
    VALUES (param1, param2);
END;
$$ LANGUAGE plpgsql;

Вызов:

SELECT example_function(10, 'Sample Text');

Миграция

При переносе хранимых процедур из MSSQL в PostgreSQL их нужно преобразовывать в функции, переписывая синтаксис и учитывая использование PL/pgSQL.

Управление транзакциями#

Что такое транзакция?

Транзакция — это единица работы с базой данных, которая выполняется либо полностью (коммит), либо откатывается в исходное состояние (роллбэк), если происходит ошибка. Это обеспечивает целостность данных.

В MSSQL транзакции начинаются с BEGIN TRANSACTION. Далее выполняются SQL-операции, после которых:

• COMMIT TRANSACTION фиксирует изменения.
• ROLLBACK TRANSACTION отменяет все изменения, выполненные с момента начала транзакции.

BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id) VALUES (1, 100);
UPDATE customers SET balance = balance - 50 WHERE customer_id = 100;

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
END
ELSE
BEGIN
    COMMIT TRANSACTION;
END;

Здесь транзакция проверяет наличие ошибок (@@ERROR) перед фиксацией изменений.

PostgreSQL использует схожий подход, но синтаксис проще:

• BEGIN начинает транзакцию.
• COMMIT фиксирует изменения.
• ROLLBACK отменяет изменения.

BEGIN;
INSERT INTO orders (order_id, customer_id) VALUES (1, 100);
UPDATE customers SET balance = balance - 50 WHERE customer_id = 100;

IF (SELECT COUNT(*) FROM customers WHERE balance < 0) > 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

Логика проверки ошибок реализуется через SQL или PL/pgSQL конструкции.

Ключевые различия:

• Синтаксис:
  • MSSQL требует явного указания TRANSACTION, а PostgreSQL использует более короткие команды.
• Обработка ошибок:
  • MSSQL предоставляет встроенную переменную @@ERROR для проверки ошибок.
  • PostgreSQL предлагает более гибкий механизм управления через блоки EXCEPTION в PL/pgSQL.
• Автоматическое управление:
  • В PostgreSQL транзакции автоматически обрабатываются в блоках DO или FUNCTION, что делает код более компактным.

Миграция

При переносе логики транзакций с MSSQL на PostgreSQL нужно:

1. Переписать проверки ошибок в соответствии с механизмами PostgreSQL.
2. Убедиться, что все зависимости между операциями внутри транзакции правильно адаптированы.
3. Учесть особенности PostgreSQL, такие как автоматическая фиксация изменений вне явных транзакций.