О Компании Как Купить Скидки Продукты Услуги Тех.Поддержка Вопросы Карта Сайта

Проверка результатов миграции базы данных MS SQL в PostgreSQL

Начнем с того, что именно должно быть проверено в базе данных PostgreSQL после завершения миграции. Вот список объектов базы данных, которые необходимо проверить:

  • Структуры таблиц
  • Данные
  • Первичные ключи и индексы
  • Внешние ключи
  • Представления
  • Триггеры
  • Хранимые процедуры

Структуры таблиц

Microsoft SQL Server позволяет получить структуру таблицы следующим образом:

  • В T-SQL-клиент запустите оператор EXEC sp_columns @ table_name=(имя таблицы)
  • В Management Studio разверните базу данных на левой панели, закройте узел "Таблицы", щелкните правой кнопкой мыши имя таблицы и выберите элемент "Дизайн"

PostgreSQL позволяет получить определение таблицы, выполнив команду \d имя_таблицы

Структура таблицы MS SQL преобразуется правильно, когда каждый столбец имеет одинаковый тип, размер и значение по умолчанию в соответствующей таблице PostgreSQL. Ниже приведена таблица соответствующих преобразований для каждого типа данных Microsoft SQL:

SQL Server PostgreSQL
BIGINT BIGINT
BINARY(n) BYTEA
BIT BOOLEAN
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
DATE DATE
DATETIME TIMESTAMP(3)
DATETIME2(p) TIMESTAMP(p)
DATETIMEOFFSET(p) TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE PRECISION
FLOAT(p) DOUBLE PRECISION
INT, INTEGER INT, INTEGER
MONEY MONEY
NCHAR(n) CHAR(n)
NTEXT TEXT
NUMERIC(p,s) NUMERIC(p,s)
NVARCHAR(n) VARCHAR(n)
NVARCHAR(max) TEXT
REAL REAL
ROWVERSION BYTEA
SMALLDATETIME TIMESTAMP(0)
SMALLINT SMALLINT
SMALLMONEY MONEY
TEXT TEXT
TIME(p) TIME(p)
TIMESTAMP BYTEA
TINYINT SMALLINT
UNIQUEIDENTIFIER CHAR(16)
VARBINARY(n), VARBINARY(max) BYTEA
VARCHAR(n) VARCHAR(n)
VARCHAR(max) TEXT
XML XML

Данные

Преобразованные данные могут быть подтверждены визуальным сравнением определенных фрагментов из таблиц MS SQL и Postgres. Microsoft SQL позволяет исследовать фрагмент данных следующим образом:

  • В T-SQL клиенте запустите оператор
    SELECT TOP number_of_records * FROM table_name
    
  • В Microsoft Management Studio щелкните правой кнопкой мыши на таблице в левой панели и выберите пункт 'Select Top 1000 Rows' item

PostgreSQL позволяет извлечь фрагмент данных с помощью следующего запроса:

SELECT * FROM table_name LIMIT number_of_records

Кроме того, важно проверить, что таблицы Microsoft SQL и PostgreSQL имеют одинаковое количество записей. Обе СУБД позволяют получать количество записей в таблице следующим образом:

SELECT COUNT(*) FROM table_name

Первичные ключи и индексы

Microsoft SQL позволяет перечислять индексы следующим образом:

  • В консольной версии клиента T-SQL запустите инструкцию SQL
       
    SELECT o.name AS Table_Name, 
           i.name AS Index_Name, 
           i.type_desc AS Index_Type
    FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    WHERE i.name IS NOT NULL AND o.type = 'U'
    ORDER BY o.name, i.type
    
  • В Microsoft Management Studio откройте режим 'Дизайн' просмотра таблицы (см. подробнее в разделе 'Структуры таблиц') и нажмите кнопку 'Manage Indexes and Keys' на панеле инструментов (выделено красным на скриншоте вверху)

PostgreSQL displays information about indexes at the bottom of table definition generated by the command: \d table_name

Внешние ключи

MS SQL предоставляет информацию о внешних ключах следующим образом:

  • В консольной версии клиента T-SQL запустите инструкцию SQL
      
    SELECT obj.name AS fk_name,
           tab1.name AS table,
           col1.name AS column,
           tab2.name AS referenced_table,
           col2.name AS referenced_column
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.objects obj
           ON obj.object_id = fkc.constraint_object_id
    INNER JOIN sys.tables tab1
           ON tab1.object_id = fkc.parent_object_id
    INNER JOIN sys.columns col1
           ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
    INNER JOIN sys.tables tab2
           ON tab2.object_id = fkc.referenced_object_id
    INNER JOIN sys.columns col2
          ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
    
  • В Management Studio откройте представление "Дизайн" таблицы и нажмите кнопку "Отношения" на панели инструментов (слева от элемента управления, описанного в предыдущем разделе, см. Скриншот выше)

PostgreSQL позволяет извлекать информацию о внешних ключах из сервисной таблицы "information_schema" с помощью следующего запроса:

  
SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='table_name';

Представления

Единственный способ проверить правильность преобразования всех представлений - это сравнить код каждого представления в MS SQL и PostgreSQL с учетом различий между SQL-диалектами этих двух СУБД. Вот, как получить список всех представлений в Microsoft SQL:

  
SELECT * FROM sys.views 

PostgreSQL позволяет получить аналогичную информацию с помощью следующего запроса:

  
SELECT table_name FROM INFORMATION_SCHEMA.views; 

Триггеры

Так же, как и представления, код триггеров сравнивается с учетом различий в синтаксисе Microsoft SQL и PostgreSQL. Вот, как можно получить список всех триггеров в MS SQL:

SELECT 
    sysobjects.name AS trigger_name,
    USER_NAME(sysobjects.uid) AS trigger_owner,
    s.name AS table_schema,
    OBJECT_NAME(parent_obj) AS table_name,
    OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate,
    OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete,
    OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert,
    OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter,
    OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
    OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'

PostgreSQL позволяет получить аналогичную информацию с помощью следующего запроса:

select * from pg_trigger;

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

Хранимые процедуры и функции базы данных SQL Server переводятся в формат PostgreSQL путем конвертирования исходного кода с учетом различий в синтаксисе исходной и целевой СУБД. Вот, как можно получить список всех триггеров в MS SQL:

select * from information_schema.routines where routine_type = 'PROCEDURE'

PostgreSQL позволяет получить аналогичную информацию с помощью следующего запроса:

select proname, prosrc from pg_proc;