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
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:
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:
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
