Data Migration from SQL Server to PostgreSQL

Question

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.

Answer 1

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!