SQL Server to PostgreSQL – Data Migration Guide

migrationpostgresqlsql server

I need to migrate database from SQL Server to PostgreSQL. Both databases are on different servers. I have about 20 tables and millions of rows.

What are the ways to accomplish this? I there any open source tools? Can I do this using some script?

I have access to the server that runs PostgreSQL database and I have connection parameters to the SQL Server. The OS on the server that runs PostgreSQL is ubuntu.

The SQL Server is still live, and will continue to be. Time is not a problem. I didn't understood you, sorry. Neither database is in a production so I guess they can be unavailable as long as needed.

Best Answer

There is no "silver bullet" to complete this migration. You will need to set up your database schema in the destination, then perform an ETL process to pull the data out of SQL Server and insert it into Postgres. There will be changes required in the schema (and transformations of the data) because there isn't a 1:1 mapping of data types between the two platforms.

But that just moves the data.

Then you have to migrate your stored procedures, triggers, and other code embedded in the database. Again, there's no "here, run this script" to magically convert everything. These will require more effort to convert than the raw data.

If there is development ongoing with this system, you'll need to port each of those changes from SQL Server to Postgres. If you want a "live" migration and data is still being modified (insert, update, delete, anything that changes the state of the data), you'll need to replicate those changes to Postgres. When your cutover date arrives, you'll have to quiesce SQL Server, ensure that the last of the data and other updates have been migrated, then shut down SQL Server and update your apps to point at Postgres.

And don't forget to test before you make the final switch!

Related Solutions

Sql-server – Migrate Data and/or Schema from DBv1 to DBv2

It sounds like you've got this entirely back to front and upside down. Typically changes are tracked using scripts, which you would then apply to the production database. I've never encountered a case where it made sense to import production data to the next version created from development.

If your database isn't currently in source control, the articles linked to from Jeff Atwood's article Get Your Database Under Version Control are a good introduction. Also, the free Redgate ebook SQL Server Team Based Development includes a chapter on source control for databases.

If you've been making changes via GUI tools and don't have any record of what's changed your best bet is a database comparison tool, like Redgate SQLCompare or Apex SQLDiff. These tools will generate the scripts to upgrade your production database to match the schema of development.

If the boss won't part with the cash for a comparison tool, you could reconcile the changes manually using a diff tool like WinMerge or DiffMerge. Use SSMS Generate Script tools to script objects to individual files, then use the diff tool to identify differences. Finally, hand craft the necessary ALTER statements for any changes to tables.

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

Best Answer

Related Solutions

Sql-server – Migrate Data and/or Schema from DBv1 to DBv2

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

Related Question