Convert SQL Server Database to PostgreSQL

• Download source - 13.1 KB
• Download CopySqlServerToPostgresql_1.4 - 183.2 KB

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 its 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
int                 integer
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
image               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.

Defaults

Getdate() will be converted to the PostgreSQL equivalent Now().

ROWGUIDCOL and newid() will be converted to the PostgreSQL equivalent uuid_generate_v1().

To enable support for generating GUIDs, in the OpenPostgreSql() method, the necessary extension is installed:

//-- Needed for: uuid PRIMARY KEY DEFAULT uuid_generate_v1()
ExecuteSqlScript(connectionStringPostgresql, "CREATE EXTENSION \"uuid-ossp\";");

IDENTITY columns will be converted to the PostgreSQL equivalent SERIAL.

Note that SERIAL is not a real data type, behind the scenes it is an integer with a sequence attached to it.

Points of Interest

Not supported:

• Stored procedures
• User defined functions

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.

Thanks to Henry for the Schema suggestion!

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

More information can be found at the following links:

• http://www.npgsql.org/
• http://sourceforge.net/projects/postgresqlportable/
• http://msftdbprodsamples.codeplex.com/

History

Version 1

• Initial release, source code only

Version 1.1

• Added command line arguments
• Added executable (uses Windows Authentication)
• Fixed order of primary key fields (for combined primary key)

Version 1.2

• Fixed a bug for fields with a data type in the name, e.g. datetime
• Added support for both standard SQL Server and SQL Server Express

Version 1.3

• Added legacy data type "image"
• Added support for schemas
• Added support for indexes
• Added support for constraints: foreign keys, defaults, ROWGUIDCOL

Version 1.4

• Added support for Views
• Added support for IDENTITY columns