Easiest way to migrate a PostgreSQL database into an SQL Server one
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?
One Solution collect form web for “Easiest way to migrate a PostgreSQL database into an SQL Server one”
I believe that you may have gotten down-votes due to amazing ease of generating a simple SQL script from PostgreSQL that can (theoretically) be run again just about any DBMS. If one is a regular PostgreSQL user, it sounds like a dumb question.
That’s not fair since it turns out this is actually a moderately hard problem (although more due to SQL Server’s odd syntax and interface than any failing of PostgreSQL).
You should be able to find some useful information in the accepted answer in this Serverfault page: https://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.
If you can get the schema converted without the data, you may be able to shorten the steps for the data by using this command:
pg_dump --data-only --column-inserts your_db_name > data_load_script.sql
This load will be quite slow, but the
--column-inserts option generates the most generic INSERT statements possible for each row of data and should be compatible.
EDIT: Suggestions on converting the schema follows:
I would start by dumping the schema, but removing anything that has to do with ownership or permissions. This should be enough:
pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql
Edit this file to add the line
BEGIN TRANSACTION; to the beginning and
ROLLBACK TRANSACTION; to the end. Now you can load it and run it in a query window in SQL Server. If you get any errors, make sure you go to the bottom of the file, highlight the ROLLBACK statement and run it (by hitting F5 while the statement is highlighted).
Basically, you have to resolve each error until the script runs through cleanly. Then you can change the
ROLLBACK TRANSACTION to
COMMIT TRANSACTION and run one final time.
Unfortunately, I cannot help with which errors you may see as I have never gone from PostgreSQL to SQL Server, only the other way around. Some things that I would expect to be an issue, however (obviously, NOT an exhaustive list):
- PostgreSQL does auto-increment fields by linking a
NOT NULL INTEGERfield to a
DEFAULT. In SQL Server, this is an
IDENTITYcolumn, but they’re not exactly the same thing. I’m not sure if they are equivalent, but if your original schema is full of “id” fields, you may be in for some trouble. I don’t know if SQL Server has
CREATE SEQUENCE, so you may have to remove those.
- Database functions / Stored Procedures do not translate between RDBMS platforms. You’ll need to remove any
CREATE FUNCTIONstatements and translate the algorithms manually.
- Be careful about encoding of the data file. I’m a Linux person, so I have no idea how to verify encoding in Windows, but you need to make sure that what SQL Server expects is the same as the file you are importing from PostgreSQL.
pg_dumphas an option
--encoding=that will let you set a specific encoding. I seem to recall that Windows tends to use two-byte, UTF-16 encoding for Unicode where PostgreSQL uses UTF-8. I had some issue going from SQL Server to PostgreSQL due to UTF-16 output so it would be worth researching.
- The PostgreSQL datatype
TEXTis simply a
VARCHARwithout a max length. In SQL Server,
TEXTis… complicated (and deprecated). Each field in your original schema that are declared as
TEXTwill need to be reviewed for an appropriate SQL Server data type.
- SQL Server has extra data types for
UNICODEdata. I’m not familiar enough with it to make suggestions. I’m just pointing out that it may be an issue.