如何在PostgreSQL中编写存储过程实现数据的增量同步？-SQL-PHP中文网

postgresql无法仅靠create function实现可靠增量同步，因其函数无状态、不支持跨会话共享变量，必须结合控制表+外部调度+显式状态管理。

如何在postgresql中编写存储过程实现数据的增量同步？

PostgreSQL 本身不支持传统意义上的“存储过程”（如 Oracle 的 CREATE PROCEDURE）来长期维护同步状态或跨会话持久化变量，直接用 PL/pgSQL 存储过程做可靠增量同步是行不通的。它缺乏会话间共享状态的能力，也无法安全处理 DELETE、并发冲突、断点续传等核心问题。

必须换思路：用函数 + 控制表 + 外部调度（如 pg_cron 或外部脚本）组合实现，否则必然漏数据、丢删除、重复写入。

为什么不能只靠 `CREATE FUNCTION` 实现增量同步？

PL/pgSQL 函数是无状态的——每次调用都是新会话，v_last_sync_time 这类变量一退出就消失。你无法在函数里“记住上次同步到哪”，除非显式读写控制表。
常见翻车现象：
• 写个 SELECT MAX(updated_at) FROM source 当作本次终点，但中间有延迟或事务未提交，导致下次启动时跳过部分变更
• 用 NOW() - INTERVAL '1 hour' 硬切窗口，任务晚执行 2 小时，那一小时的数据就永远丢了
• 函数里没加 FOR UPDATE 或排他锁，高并发下多个实例同时读同一控制表，写出脏数据

必须建一张控制表并手动维护 `last_sync_time`

这是唯一能跨次运行、支撑断点续传的方案。控制表结构示例：

PostgreSQL 18.4 ubuntu

PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。

下载

CREATE TABLE sync_control (
  table_name TEXT PRIMARY KEY,
  last_sync_time TIMESTAMP WITH TIME ZONE,
  last_handle_time TIMESTAMP WITH TIME ZONE
);

关键操作规则：
• 每次同步开始前，先 SELECT last_sync_time FROM sync_control WHERE table_name = 'orders'
• 同步完成后，必须用 UPDATE sync_control SET last_sync_time = v_current_max_time WHERE table_name = 'orders'，而不是仅靠 SELECT MAX() 临时算
• 失败时要在 EXCEPTION 块里记录日志，并保留旧值，不能清空或覆盖
• 字段类型必须是 TIMESTAMP WITH TIME ZONE，避免时区错位导致漏数据

用 `MERGE` 或 `INSERT ... ON CONFLICT` 处理增/改，但删必须单独写

PostgreSQL 没有标准 MERGE 语法（9.5+ 的 INSERT ... ON CONFLICT 只能覆盖“冲突时更新”，不感知源端已删除的行）。所以完整逻辑必须拆成三步：
• 先同步新增和修改（用 INSERT ... ON CONFLICT DO UPDATE）
• 再同步删除（需源端提供删除标记字段，或通过全量主键比对）
• 删除逻辑必须加时间过滤：例如 DELETE FROM target WHERE id IN (SELECT id FROM deleted_log WHERE delete_time > v_last_sync_time)，否则会误删刚同步进来的新数据
容易踩的坑：
• 源表主键没建唯一索引 → ON CONFLICT 匹配失败或性能暴跌
• updated_at 字段允许 NULL → 查询条件漏写 AND updated_at IS NOT NULL，NULL 行永远不被拉取
• 没对源数据去重 → 同一主键多条变更记录进函数，触发多次 ON CONFLICT，可能违反业务约束

真正可靠的增量同步必须脱离纯 SQL，引入外部协调

哪怕你把函数写得再严谨，只要调度器（如 cron）每次起新进程，就绕不开状态丢失问题。生产环境必须：
• 用 pg_cron 扩展或外部脚本（Python/bash）驱动函数调用，且每次调用都明确传入 table_name 和期望的 sync_window
• 对 WAL 级别同步（如 pglogrepl），根本不用写存储过程，而是起一个长连接监听逻辑复制流
• 若用 Seatunnel 或 DTS 等工具，它们内部已封装控制表、checkpoint、DDL 捕获等机制，此时手写函数反而增加故障面
最常被忽略的一点：业务层是否真实更新 updated_at？ORM 的 save(update_fields=[...]) 可能跳过时间戳字段，导致某次批量更新后，这批记录在后续所有增量同步中都被忽略。