How to migrate a PostgreSQL database into a SQLServer one?

Question

I have a PostgreSQL database that I want to move to SQL Server -- both schema and data. I am poor so I don't want to pay any money. I am also lazy, so I don't want to do very much work. Currently I'm doing this table by table, and there are about 100 tables to do. This is extremely tedious.

Is there some sort of trick that does what I want?

user_129696 · Answer

I have found a faster and easier way to accomplish this.

First copy your table (or query) to a tab delimited file like so:

COPY (SELECT siteid, searchdist, listtype, list, sitename, county, street, 
   city, state, zip, georesult, elevation, lat, lng, wkt, unlocated_bool, 
   id, status, standard_status, date_opened_or_reported, date_closed, 
   notes, list_type_description FROM mlocal) TO 'c:\SQLAzureImportFiles\data_script_mlocal.tsv' NULL E''

Next you need to create your table in SQL, this will not handle any schema for you. The schema must match your exported tsv file in field order and data types.

Finally you run SQL's bcp utility to bring in the tsv file like so:

bcp MyDb.dbo.mlocal in "\NEWDBSERVER\SQLAzureImportFiles\data_script_mlocal.tsv" -S tcp:YourDBServer.database.windows.net -U YourUserName -P YourPassword -c

A couple of things of note that I encountered. Postgres and SQL Server handle boolean fields differently. Your SQL Server schema need to have your boolean fields set to varchar(1) and the resulting data will be 'f', 't' or null. You will then have to convert this field to a bit. doing something like:

ALTER TABLE mlocal ADD unlocated bit;
UPDATE mlocal SET unlocated=1 WHERE unlocated_bool='t';
UPDATE mlocal SET unlocated=0 WHERE unlocated_bool='f';
ALTER TABLE mlocal DROP COLUMN unlocated_bool;

Another thing is the geography/geometry fields are very different between the two platforms. Export the geometry fields as WKT using ST_AsText(geo) and convert appropriately on the SQL Server end.

There may be more incompatibilities needing tweaks like this.

EDIT. So whereas this technique does technically work, I am trying to transfer several million records from 100+ tables to SQL Azure and bcp to SQL Azure is pretty flaky it turns out. I keep getting intermittent Unable to open BCP host data-file errors, the server is intermittently timing out and for some reason some records are not getting transferred with no indications of errors or problems. So this technique is not stable for transferring large amounts of data to Azure SQL.

user_2536283 · Answer

You can use Navicate a powerful GUI tool for working with various databases including Postgres and SQL Server. You can transfer both schema and data easily as follows:

Create two connection for source and target database

Go to Tools -> Data Transfer

Select source database and target database with its IP, database name and schema

as you can see in the option, if target table is not exist, it would create

Tada, it takes 10 mins to transfer whole my 63 tables and its data from Postgres to SQL Server.

Enjoy it!

How to migrate a PostgreSQL database into a SQLServer one?

3 Answers