Microsoft SQL Server and PostgreSQL are both widely spread database management systems having rich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policy and high total cost of ownership while PostgreSQL goes with less restrictive license and it is open source. Due to those reasons a lot of companies and organizations plan migration of their SQL Server databases to Postgres on-premises or cloud platforms.
Here is the sequence of steps to migrate database from SQL Server to Postgres:
- extract CREATE-statements for tables, indexes and constraints from the source SQL Server database
- convert those statements into Postgres format with respect to default values, date/time and binary string representations
- load the converted statements into the target Postgres database
- export SQL Server data into comma separated values files (one file per table) as an intermediate storage
- transform the data to comply with Postgres format and load into the destination database
Now let’s consider these steps more closely. To export SQL Server table definitions, open the Microsoft SQL Management Studio, right-click on database then go to Tasks > Generate Scripts. On the appeared dialog navigate to “Set scripting options” tab, click on Advanced and select “data only”, or “data and schema” for “Types of data to script” (in the General section).
The SQL statements must be modified before loading into Postgres database as follows:
- remove MS SQL specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
- replace square brackets that are used as a delimiter for database object names by double quotes
- remove square brackets around the data types
- replace “dbo” that is the default SQL Server schema by PostgreSQL “public” in all related statements
- remove all non-supported optional keywords (i.e. “WITH NOCHECK”, “CLUSTERED”)
- remove all reference to filegroup (i.e. “ON PRIMARY”)
- replace types “INT IDENTITY(…)” by “SERIAL”
- update all non-supported data types (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
- replace the SQL Server query terminator “GO” with “;” according to Postgres syntax
Now it is time to migrate the data from SQL Server to Postgres. It also can be done via Microsoft SQL Management Studio: right-click on database then go to Tasks > Export Data. In the appeared wizard-style dialog window select data source “Microsoft OLE DB Provider for SQL Server” and the destination “Flat File Destination”. After all steps of the wizard are completed, the data will be exported into the specified file of comma-separated values (CSV) format.
If some the source database contains binary data, it is required to apply the following workaround while migrate from SQL Server to Postgres. On “Specify Table Copy or Query” wizard screen of Microsoft SQL Management Studio select the option “Write a query to specify the data to transfer”. Then compose the SELECT-query on the next wizard page called “Provide a Source Query” as follows:
select <non-binary field #1>, <non-binary field #2>, cast(master.sys.fn_varbintohexstr(
cast(<binary field name> as varbinary(max))) as varchar(max)) as <binary field name>
from <table name>;
Unfortunately, this approach is not applied for large binary data (1MB+) since the query goes into infinite hang. The resulting comma separated values (CSV) files can be imported into PostgreSQL tables via “COPY” command:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
If you see “Permission denied” error after running this statement, try to use “\COPY” command instead.
Another bottleneck of SQL Server to Postgres migration is spatial data. Both DBMS support those geometry and geography data types, however the data cannot be extracted properly via the straight forward approach specified above. It is necessary to use special text representation of spatial data called well-known text (WKT). For example, SQL Server table “spatial_table” is defined as follows:
CREATE TABLE spatial_table(col1 geography, col2 geometry)
The, the spatial columns ‘col1’ and ‘col2’ could be extracted in WKT form using built-in function STAsText:
SELECT col1.STAsText(), col2.STAsText() FROM spatial_table
The brief guide exposed above may illustrate that database migration from SQL Server to Postgres is a tedious procedure requiring much time and efforts, especially for large and complicated data warehouses. It would be reasonable to use special software to simplify and automate the overall migration procedure via intuitive user interface withing just a few clicks of mouse buttons.
One of such tools has been developed by Intelligent Converters, a software company specializing in database migration and synchronization between all popular relational DBMS such as MySQL, PostgreSQL, SQL Server, Oracle, MS Access, IBM DB2, Firebird, FoxPro and SQLite since 2001.
Their SQL Server to Postgres converter offer all necessary features to make the database migration smooth and safe:
- all modern versions of on-premises and cloud SQL Server and Postgres are supported (Azure SQL, Heroku, Amazon RDS, etc)
- the tool migrates schemas, tables, indexes, constraints, sequences, data and views
- existing Postgres databases can be merged or synchronized with SQL Server data (equal structures and primary keys are required for source and target tables)
- the migration can be scripted or scheduled via command line version of the tool included in the installation pack
- extremely powerful option to filter data through SELECT-queries allows to preprocess the source tables before migration
Lean more about SQL Server to Postgres database migration and the related software on the official site of Intelligent Converters.
