测试库与生产库怎么仅同步新增增量数据_无损发布与更新方案

pg_dump --inserts ON CONFLICT DO NOTHING 可安全实现增量同步先用 --inserts 导出新增数据再用 sed 替换为 INSERT ... ON CONFLICT DO NOTHING依赖唯一约束跳过重复避免误更新或主键冲突。如何用 pg_dump --inserts --on-conflict-do-nothing 实现 PostgreSQL 增量同步增量同步不是靠“全量比对”而是靠业务主键或时间戳字段识别新增。postgresql 本身不提供内置增量复制除非用逻辑复制槽但开发侧可控场景下最轻量、无损的方式是导出新增记录再安全插入。常见错误现象INSERT INTO ... SELECT 直接跨库执行遇到重复主键就报 duplicate key violates unique constraint或者用 ON CONFLICT DO UPDATE 误改老数据——这违反“仅同步新增”的前提。必须确认目标表有唯一约束如 id 或 (tenant_id, event_id)否则 ON CONFLICT 无意义导出时加 --inserts不用 --column-inserts后者性能差且易因列顺序错位失败生成的 SQL 中把 INSERT 替换为 INSERT ... ON CONFLICT DO NOTHING一行命令即可sed s/INSERT INTO/INSERT INTO ON CONFLICT DO NOTHING/g dump.sql safe_insert.sql若源库用的是 serial 或 identity确保目标库同字段未设 DEFAULT 冲突否则插入可能跳过值或报错MySQL 的 REPLACE INTO 和 INSERT IGNORE 怎么选REPLACE INTO 是先删后插会触发 DELETE INSERT 两个事件自增 ID 可能跳变、外键关联记录若没设 ON DELETE CASCADE 会失败INSERT IGNORE 才真正符合“仅新增”语义——冲突就跳过不扰动存量。使用场景ETL 脚本跑定时任务同步日志表、订单明细等只追加不修改的业务表。务必确认目标表有 UNIQUE KEY 或 PRIMARY KEY否则 IGNORE 不生效INSERT IGNORE 对 NOT NULL 字段缺失值报错不是忽略得提前清洗数据避免在大事务中混用MySQL 的 INSERT IGNORE 在事务里遇到冲突仍会占用自增 ID长期运行可能导致 ID 浪费替代方案用 INSERT ... SELECT ... WHERE NOT EXISTS更明确但需注意子查询不能引用目标表会报 You cant specify target table for update in FROM clause为什么不能直接用 mysqldump --where 或 pg_dump -t 做增量因为 --where 是静态条件比如 --wherecreated_at 2024-06-01它依赖应用层严格保证 created_at 写入即准确、无回填、无时区错乱——现实中常有延迟写入、批量补数、跨时区服务混用等情况导致漏数据或重复。性能影响WHERE 条件若没走索引mysqldump 会全表扫描pg_dump -t 单表导出不带条件根本不是增量。正确做法是维护一个 sync_cursor 表存每张表最后同步的 id 或 updated_at每次取 WHERE id ?并用 SELECT MAX(id) 更新游标游标更新必须和插入在同一个事务里完成MySQL 支持PG 需 BEGIN; INSERT ...; UPDATE cursor; COMMIT;否则断点续传不可靠别信 SHOW MASTER STATUS 或 pg_logical_slot_get_changes ——它们面向 DBA 级复制开发侧接入成本高、权限要求严、容易积压上线前最容易被忽略的三个检查点无损发布的“无损”不是指技术上没报错而是指业务上没感知、数据上没偏差。这三个点一漏可能凌晨两点被叫醒查数据对不齐。目标库的字符集和排序规则COLLATION是否和源库一致尤其 MySQL 中 utf8mb4_0900_as_cs 和 utf8mb4_general_ci 对大小写敏感性不同会导致 ON DUPLICATE KEY 判定结果不一致同步脚本是否校验了行数导出后、导入前、导入后分别 SELECT COUNT(*)三者不等就得停——别依赖“看起来没报错”有没有关掉目标库的外键检查SET FOREIGN_KEY_CHECKS0开着的话插入顺序不对会失败但关了之后得自己确保引用完整性否则后续查关联数据就空了