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

pg_dump + --inserts + ON CONFLICT DO NOTHING 可安全实现增量同步：先用 --inserts 导出新增数据，再用 sed 替换为 INSERT ... ON CONFLICT DO NOTHING，依赖唯一约束跳过重复，避免误更新或主键冲突。

增量同步不是靠“全量比对”，而是靠业务主键或时间戳字段识别新增。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 冲突，否则插入可能跳过值或报错

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 can't specify target table for update in FROM clause）

因为 --where 是静态条件，比如 --where="created_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_CHECKS=0）？开着的话，插入顺序不对会失败；但关了之后，得自己确保引用完整性，否则后续查关联数据就空了