Using the PostgreSQL extension tds_fdw to validate data migration from SQL Server to Amazon Aurora PostgreSQL

This article discusses using the PostgreSQL tds_fdw extension to validate data migration from SQL Server to Amazon Aurora PostgreSQL, especially for tables without primary keys.

• Data validation is crucial during database migrations
• Alternative validation strategies include:
  • Count comparison
  • Temporary primary key
  • Foreign Data Wrapper (FDW) techniques
  • Third-party comparison tools
• The method involves:
  • Installing tds_fdw extension in Aurora PostgreSQL
  • Creating a foreign server linking to SQL Server
  • Creating a foreign table matching source table structure
  • Using EXCEPT ALL query to compare data between source and target
• Helps identify row-level and column-level discrepancies during migration

The approach provides a robust method for validating data migrations, especially for tables lacking primary keys.