Verifying MS SQL to PostgreSQL Migration

Verifying MS SQL to PostgreSQL Migration

The most important phase of database migration is validating results at the end of the process. This whitepaper explains how to verify the primary database entries:

  • Schemas
  • Data
  • Indexes
  • Views

Schemas

SQL Server allows to explore schemas in two ways.

  • In T-SQL console client use the query EXEC sp_columns @table_name=’table_name’
  • In Management Studio click on database name in the left pane, expand ‘Tables’ item, right-click on the appropriate name and select ‘Design’ item

PostgreSQL displays table structure through the query:\d table_name

MS SQL schema is considered as properly converted when each column has the same type, size and default value in the resulting PostgreSQL table. This is the list of safe type 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

To verify conversion of data it is required to do visual comparison of random fragment(s) in MS SQL and Postgres tables. Microsoft SQL provides two options to extract data fragment:

  • In T-SQL console client use the query SELECT TOP number_of_records * FROM table_name

or

  • Click on the appropriate table name in the left pane of Microsoft Management Studio and select ‘Select Top 1000 Rows’ menu item

PostgreSQL extracts fragment of data through the query:

SELECT * FROM table_name LIMIT number_of_records

It is also reasonable to check that source and destination tables have the same number of records. Both MS SQL and PostgreSQL display number of rows as follows:

SELECT COUNT(*) FROM table_name

Indexes

Microsoft SQL provides two options to explore indexes:

  • In T-SQL console client run the query

SELECT o.name AS Tbl_Name,

i.name AS Idx_Name,

i.type_desc AS Idx_Type

FROM sys.indexesi

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 ‘Schemas’ section above) and click ‘Manage Indexes and Keys’ icon on the toolbar (it is marked red on the screenshot)

PostgreSQL describes indexes at the bottom of table definition generated by ‘\d table_name’ statement.

Views

In order to check that the view is converted properly,it is required to compare SQL code of this view in MS SQL and PostgreSQL with respect to differences between SQL dialects of these two database management systems.

MS SQL allows explore views list using the query:

select TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where TABLE_NAME not in (‘sysalternates’,’sysconstraints’,’syssegments’)

PostgreSQL uses this query for the same purpose:

select viewname, definition from pg_catalog.pg_views where schema name NOT IN (‘pg_catalog’, ‘information_schema’)

Find more details on database migration from MS SQL to PostgreSQL at https://www.convert-in.com/mss2pgs.htm

Casey Huel

Leave a Reply

Your email address will not be published. Required fields are marked *