Once a day I have to synchronize table between two databases.

Source: Microsoft SQL Server

Destination: PostgreSQL

Table contains up to 30 million rows.

For the first time i will copy all table, but then for effectiveness my plan is to insert/update only changed rows.

In this way if I delete row from source database, it will not be deleted from the destination database.

The problem is that I don’t know which rows were deleted from the source database.

My dirty thoughts right now tend to use binary search - to compare the sum of the rows on each side and thus catch the deleted rows.

I’m at a dead end - please share your thoughts on this...

CodePudding user response：

In SQL Server you can enable Change Tracking to track which rows are Inserted, Updated, or Deleted since the last time you synchronized the tables.

CodePudding user response：

with TDS FDWs (Foreign Data Wrapper), map the source table with a temp table in pg, an use a join to find/exclude the rows that you need.