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:
SQL Server | PostgreSQL |
BIGINT | BIGINT |
BINARY(n) | BYTEA |
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 | ROWVERSION |
SMALLDATETIME | TIMESTAMP(0) |
SMALLINT | SMALLINT |
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 |
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.
