How to Control Results of SQL Server to PostgreSQL Migration

It is reasonable to begin with understanding what exactly must be validated in PostgreSQL database after migration is complete. Here is list of database objects that have to be verified:

• Table structures
• Data
• Primary keys and indexes
• Foreign keys
• Views

Table Structures

SQL Server exposes table structure as follows:

• In T-SQL client run the statement EXEC sp_columns @table_name=(table name)
• In Management Studio expand the database in the left pane, then expand 'Tables' node, right-click on the table name and select 'Design' item

PostgreSQL console client explores table definition by running the statement \d table_name

Table structure is converted from SQL Server to PostgreSQL properly when each column has equal type, size and default value in the corresponding PostgreSQL table. Here is the table of appropriate conversions for each SQL Server data type.

Data

SQL Server to PostgreSQL data migration can be validated by comparison of random data fragments from the source and destination tables. SQL Server allows to explore data fragment as follows:

• In T-SQL client run the statement SELECT TOP number_of_records * FROM table_name
• In Microsoft Management Studio right-click on the table in the left pane and select 'Select Top 1000 Rows' item

PostgreSQL accepts this kind of SELECT-query to extract fragment of data:

SELECT * FROM table_name LIMIT number_of_records

Also, it is important to check that SQL Server and PostgreSQL tables have the same number of records. Both DBMS allows to get number of table records as follows:

SELECT COUNT(*) FROM table_name

Primary Keys and Indexes

SQL Server allows to list indexes as follows:

• In T-SQL console client run SQL statement

  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
  

• In Management Studio open 'Design' view of the table (see details in 'Table Structures' section of this article) and click 'Manage Indexes and Keys' button on the toolbar (marked red on the screenshot above)

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

Foreign Keys

SQL Server exposes foreign keys information as follows:

• In T-SQL console client run SQL statement

  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
  

• In Management Studio open 'Design' view of the table and click 'Relationships' button on the toolbar (at the left of the conrtol described in previous section, see the screenshot above)

PostgreSQL can extract information about foreign keys from service table "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';

Views

The only way to check that all views have been converted from SQL Server to PostgreSQL properly is analyzing code of each view in the source and target databases taking into account differences between SQL dialects of these two DBMS. This is how to get list of all views in source and destination databases.

SQL Server exposes list of all views in the database using the query: SELECT * FROM sys.views

PostgreSQL can do the same via the query: SELECT table_name FROM INFORMATION_SCHEMA.views;

Basic differences between SQL Server and PostgreSQL queries are listed in this article.