SymmetricDS同步MSSQL至PostgreSQL主键更新异常咨询

我完全理解你的困扰——虽然主键设计的最佳实践是保持不可变，但业务场景总有特殊情况需要覆盖。针对你遇到的复合主键更新后PostgreSQL端新增行而非更新的问题，这里有几个可行的解决方案：

方案1：改用代理主键+业务唯一约束（最推荐） #

这是从根源上规避主键变更同步问题的方案，也符合数据库设计规范：

• 在MSSQL和PostgreSQL的目标表中，新增一个代理主键列（比如sync_id）：
  • MSSQL端：ALTER TABLE your_table ADD sync_id BIGINT IDENTITY(1,1) PRIMARY KEY
  • PostgreSQL端：ALTER TABLE your_table ADD sync_id SERIAL PRIMARY KEY
• 修改SymmetricDS配置：
  • 在sym_transform_column中，将新增的sync_id设为pk=1，原复合主键列全部设为pk=0
  • 给原复合主键列添加唯一约束（两端都要加），保证业务层面的唯一性：ALTER TABLE your_table ADD CONSTRAINT uk_your_table_compound UNIQUE (pk1, pk2, pk3, pk4, pk5, pk6, pk7)
• 后续更新原复合主键列时，SymmetricDS会通过代理主键精准匹配目标行，正常更新所有列，同时业务唯一性也能得到保障。

方案2：自定义更新SQL脚本（无需修改表结构） #

如果不想调整表结构，可以通过SymmetricDS的自定义转换SQL强制更新主键列：

• 保持原复合主键列在sym_transform_column中的pk=1配置不变
• 修改sym_transform_table中的update_sql字段，指定自定义更新语句模板：

  UPDATE {target_table} 
  SET pk1 = {new.pk1}, pk2 = {new.pk2}, pk3 = {new.pk3}, pk4 = {new.pk4}, pk5 = {new.pk5}, pk6 = {new.pk6}, pk7 = {new.pk7}, value1 = {new.value1}, value2 = {new.value2}
  WHERE pk1 = {old.pk1} AND pk2 = {old.pk2} AND pk3 = {old.pk3} AND pk4 = {old.pk4} AND pk5 = {old.pk5} AND pk6 = {old.pk6} AND pk7 = {old.pk7}
  

• 这个脚本的核心是用旧主键值定位目标行，然后更新包括新主键值在内的所有列，完全绕过SymmetricDS默认不更新主键列的逻辑。

方案3：启用SymmetricDS主键变更跟踪功能（版本依赖） #

部分新版本的SymmetricDS支持直接开启主键变更同步的配置：

• 在sym_engine_properties表中添加配置项：

  INSERT INTO sym_engine_properties (name, value) VALUES ('allow.pk.changes', 'true')
  

• 这个配置会让SymmetricDS在捕获主键变更时，同时记录旧主键和新主键的值，同步时自动用旧主键定位行，更新所有列（包括主键列）。
• 注意：不同版本的SymmetricDS可能配置键名不同，建议先在测试环境验证兼容性。

额外注意事项 #

• 无论采用哪个方案，都要确保MSSQL端的SymmetricDS触发器能正确捕获主键列的旧值和新值——默认触发器应该已经支持，但如果有自定义触发器需要检查。
• 所有方案都建议先在测试环境验证，避免直接修改生产配置导致数据不一致。

内容的提问来源于stack exchange，提问作者Andrew B