Convert SQL Server database to PostgreSQL

Introduction

This console application converts an SQL Server database to PostgreSQL.
The AdventureWorksLT2008R2 database was used for testing.

The application uses Npgsql (.NET data provider for PostgreSQL).
Npgsql will be installed automatically by the NuGet package manager.

Background

I first tried CSV export and import to convert an SQL Server database to PostgreSQL, but this failed to copy binary fields properly as PostgreSQL expects binary data in it's own proprietary format.

Command line usage

CopySqlServerToPostgresql [catalogname] [catalogname postgres]

If the second argument is omitted, the same name is used for the PostgreSQL database.

Example:

CopySqlServerToPostgresql AdventureWorksLT2008R2

Using the code

Both SQL Server (or SQL Server Express) and PostgreSQL need to be installed (PostgreSQL portable can also be used).

The application uses Windows Authentication and tries to connect to SQL Server Express first, if the connection fails it tries to connect to standard SQL Server. If you need SQL Server authentication, you will have to change the program code.

A new PostgreSQL database with tables and Primary Keys will be created.
Microsoft SQL Server datatypes will be translated to their PostgreSQL equivalents.
The data will be copied into the newly created database.

This takes about 10 seconds for the AdventureWorksLT2008R2 database (on a notebook with an I5 processor).

In debug mode change the Catalog variable in Main() to copy a database of your own choice:

#if DEBUG
            // In debug mode use AdventureWorksLT2008R2 catalog.
            Catalog = "AdventureWorksLT2008R2";
            Catalog2 = Catalog.ToLower();

An overview of SQL Server data types and their PostgreSQL equivalents:

char                char / text
nchar               char / text
varchar             varchar / text
nvarchar            varchar / text
xml                 text
bigint              bigint
bit                 boolean
uniqueidentifier    uuid
hierarchyid         bytea
geography           bytea
tinyint             smallint
float               float
real                real
double              double precision
numeric             numeric
decimal             numeric
money               numeric
smallmoney          numeric
binary              bytea
varbinary           bytea
datetime            timestamptz
datetime2           timestamptz

Note that for char and varchar conversion I kept the same data types, although it is also possible to use the more flexible PostgreSQL text data type which behaves like varchar(max).

If you feel limited by this, feel free to change the code at the end of the GetFieldInformation() method.

Points of Interest

Not supported: 

• Indexes and constraints
• Stored procedures
• User defined functions
• Views
• etc.

You will have to create these yourself, or change the program code, it's probably best to get this information directly using a query from the SQL Server database.

Another possible use of this application might be to "downgrade" an SQL Server database to a lower version, e.g. 2012 to 2008.
This would be even simpler to accomplish, as the PostgreSQL data type conversion is not needed.

Tested on Windows 7 with VS2013, SQL Server Express 2008 R2 and PostgreSQL 9 portable. 

More info:

http://www.npgsql.org/

http://sourceforge.net/projects/postgresqlportable/

http://msftdbprodsamples.codeplex.com/