Validating SQL Server to PostgreSQL Migration

Controlling results of SQL Server to PostgreSQL migration can be a daunting task for many. However, an unequivocal understanding of the various objects that must be validated in the PostgreSQL database after migration is an effectively practical way to begin. Here is a 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 console client run the query 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

The standard PostgreSQL console client tool psql explore table definition by running the command \d table_name

Table structure is converted properly when each column has an equal type, size, and default value in the resulting table during SQL Server migration. Here is the table of safe data types mapping:

Data

Converted data can be validated by visual comparison of certain fragment(s) from MS SQL and Postgres tables. SQL Server allows for exploration of data fragment as follows:

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

Any PostgreSQL client tool may run the following query to extract fragment of data:

SELECT * FROM table_name LIMIT number_of_records

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

SELECT COUNT(*) FROM table_name

If both of these validations have succeeded, the data is migrated from SQL Server to PostgreSQL properly.

Primary Keys and Indexes

Microsoft SQL allows to list indexes as follows:

• In a command line client (e.g. sqlcmd.exe) 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)

The standard PostgreSQL console client tool psql displays information about indexes at the bottom of table definition generated by the command: \d table_name. Indexes are correctly migrated from SQL Server to PostgreSQL if:

• Numbers of indexes are the same in every SQL Server and PostgreSQL table
• Each index has the same number of indexed fields
• Each index has the same properties in SQL Server and PostgreSQL

Foreign Keys

The information about foreign keys may be obtained in SQL Server by of the following options:

• In a command line client (e.g. sqlcmd.exe) 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 the Management Studio, open ‘Design’ view of the table and click ‘Relationships’ button on the toolbar

PostgreSQL stores information about foreign keys in the service table “information_schema”, it can be extracted as follows:

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’;

Criteria of correct migration from SQL Server to PostgreSQL for foreign keys is the same as for indexes.

Views

The only way to check that all views have been migrated from SQL Server to PostgreSQL properly is to compare code of each view with respect to differences between SQL dialects of these two DBMS. This is how to list all the views in both SQL Server and PostgreSQL databases.

SQL Server: SELECT * FROM sys.views

PostgreSQL: SELECT table_name FROM INFORMATION_SCHEMA.views;

Conclusion on SQL Server to PostgreSQL Migration

In conclusion, ensuring a successful SQL Server to PostgreSQL migration requires meticulous validation of various database objects. The process involves verifying table structures, validating data integrity through visual comparisons, confirming the equivalence of primary keys and indexes, inspecting foreign keys, and validating views by comparing the code for differences in SQL dialects. The article provides detailed steps for each validation process, emphasizing the importance of attention to detail for a seamless transition. By following these comprehensive validation procedures, users can confidently ensure that their data is migrated accurately and efficiently from SQL Server to PostgreSQL.