Microsoft SQL Server -> Postgres Data Migration

Question

I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is in the cloud.

What is the best way to manage this ETL process? My current approach is to just transfer over the tables we need (about 30) and then re-initialize the materialized views. I am willing to pay for a migration tool if it can fully automate the process, otherwise I'll go with the simplest solution our bandwidth can support.

Thanks!

Answer 1

There's no need for a commercial product. You could make use of for example Pentaho Data Integration to do the task.

Back to the main question, here are the steps you could do to feed your data without recreating the tables every time:

1. Create a capture data change mechanism (it works as a table), to log changes on tables (which table has been inserted with which record (id) etc)
2. Update your destination data with changes logged in CDC mechanism.

This kind of process is broadly implemented in Data Warehousing to migrate changes.

If this seems a bit like an overkill for you, then a slower solution might come in handy. That'd be comparing the snapshot in your destination with current data in source and based on differences take proper actions (insert, update, delete rows from destination). This method would require you to scan whole tables each time you need to synchronize.