How to migrate a PostgreSQL database into a SQLServer one?

by

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 INTEGER field to a SEQUENCE using a DEFAULT. In SQL Server, this is an IDENTITY column, 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 FUNCTION statements 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_dump has 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 TEXT is simply a VARCHAR without a max length. In SQL Server, TEXT is… complicated (and deprecated). Each field in your original schema that are declared as TEXT will need to be reviewed for an appropriate SQL Server data type.
  • SQL Server has extra data types for UNICODE data. I’m not familiar enough with it to make suggestions. I’m just pointing out that it may be an issue.

More Related Contents:

  1. migrate data from MS SQL to PostgreSQL?
  2. Updating image in VB and SQL Server not working [closed]
  3. Query to list number of records in each table in a database
  4. Storing a file in a database as opposed to the file system?
  5. Multiple Indexes vs Multi-Column Indexes
  6. Preferred Method of Storing Passwords In Database
  7. Using a sort order column in a database table
  8. Create a view with ORDER BY clause
  9. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  10. How to do version control for SQL Server database?
  11. How to determine total number of open/active connections in ms sql server 2005
  12. How to check if a database exists in SQL Server?
  13. How to get difference of days/months/years (datediff) between two dates?
  14. Getting execute permission to xp_cmdshell
  15. TSQL: Create a view that accesses multiple databases
  16. What resources exist for Database performance-tuning? [closed]
  17. What is the point of “Initial Catalog” in a SQL Server connection string?
  18. How to create a yes/no boolean field in SQL server?
  19. Convert SQLITE SQL dump file to POSTGRESQL
  20. Best way to store time (hh:mm) in a database
  21. How can I run just the statement my cursor is on in SQL Server Management Studio?
  22. Executing set of SQL queries using batch file?
  23. Database design: Calculating the Account Balance
  24. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  25. Date range overlapping check constraint
  26. Difference between database and schema
  27. Generate table relationship diagram from existing schema (SQL Server) [closed]
  28. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  29. How do you deal with blank spaces in column names in SQL Server?
  30. Tool for Scripting Table Data

Leave a Comment