Close Menu
    Technology

    Database Migration Journey: Transitioning From Sql Server To Postgres

    Benjamin WhiteBy

    Being a powerful and popular relational database management system,  SQL Server has high cost of licensing, especially for large databases and number of workplaces. This is the primary reason of database migration from SQL Server to Postgres for many organizations. They choose PostgreSQL as an advanced open source DBMS fully compatible with SQL92 standard that has even more capabilities and advanced features than SQL Server.

    It is very important to recognize all differences between MS SQL and PostgreSQL before migration.

    Types Conversion

    Although SQL Server and PostgreSQL support similar sets of data types, some of those types don’t match and consequently must be converted from the source to the target system properly. Here is the table of safe type mappings:

    Microsoft SQL PostgreSQL Comments
    BINARY(n) BYTEA Binary data
    BIT BOOLEAN 1, 0 or NULL
    DATETIME TIMESTAMP(3) Date and Time with fraction
    FLOAT(p) DOUBLE PRECISION FLOAT(p)
    IMAGE BYTEA Variable length binary data
    INT IDENTITY SERIAL Auto-increment column
    NVARCHAR(max) TEXT Variable length Unicode data
    TINYINT SMALLINT 8 bit unsigned integer, 0 to 255
    UNIQUEIDENTIFIER UUID Unique identifier
    VARBINARY(n) BYTEA Binary data of variable length
    VARCHAR(max) TEXT Text data of variable length

    Built-in Functions

    SQL Server and PostgreSQL provide rich collections of built-in functions for manipulations over the data, conversion between different data formats and other common tasks. These functions are used in views, stored procedures and functions and so must be properly converted into PostgreSQL equivalents to preserve database logic. See below equivalents for SQL Server functions missing in PostgreSQL :

    Microsoft SQL PostgreSQL Comments
    CHARINDEX POSITION Locates substring in string
    DATEADD operator ‘+’ Adds interval to date
    DATEPART DATE_PART Extracts part of the date
    GETDATE NOW Returns current system date
    ISNULL COALESCE Replaces NULL values by the specified expression
    REPLICATE REPEAT Returns replication of the specified symbol
    SPACE(n) REPEAT(‘ ‘, n) Returns replication of space symbol

    Also, there is difference in string concatenation operator in SQL Server and PostgreSQL: SQL Server uses ‘+’ for String Concatenation while PostgreSQL uses ‘||’ for the same purpose.

    Besides the challenges specified above, these two DBMS have different restrictions on object names (tables, columns, indxes, etc), as well as default schemas and case sensitivity rules. There are workarounds for every such issue:

    • Just like SQL Server encloses object names in square brackets for space symbols or keyword, PostgreSQL uses double quotes for the same purposes
    • Unlike SQL Server, Postgres object names are case sensitive. It is recommended to convert all names to lower case to avoid collisions.
    • In SQL Server default schema is “dbo”, in PostgreSQL it is “public”

    The transformations and workarounds listed above make SQL Server to Postgres database migration a tedious and complicated procedure. To avoid risk of data loss or corruption experienced database administrators use special software to automate migration process. Some of these tools are listed below.

    Pgloader

    This is free tool for SQL Server to Postgres database migration providing automatic conversion of schemas, building indexes, primary key and foreign keys constraints. Also, pgloader allows to customize casting rules to make conversion from SQL Server to PostgreSQL even more flexible.

    Sqlserver2pgsql

    Another open source solution for SQL Server to Postgres database migration is  Perl script called Sqlserver2pgsql. This tool provides the following capabilities:

    • convert SQL Server schema into PostgreSQL schema
    • create Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to Postgres

    Besides free converters that usually provide just a partial automation of the database migration, there are commercial software capable of automating the entire SQL Server to Postgres database migration. Visit https://www.convert-in.com/mss2pgs.htm for learn more about one of such database migration tools.