PGSQL 数据恢复

执行过程详细记录

第1步：断开本地客户端连接

-- 有 7 个连接占用（pgAdmin 4 + JDBC），先全部断开
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'pms_dev' AND pid <> pg_backend_pid();
-- 结果：成功断开 7 个连接

第2步：生成恢复清单

pg_restore -l /tmp/pms_prod_20260731_134949.dump > /tmp/sync_full.list
# 过滤出只含 sys_* 和 okr_* 的 TABLE DATA（排除 flyway_schema_history、sync_*）
grep "TABLE DATA public \(sys\|okr\)_" /tmp/sync_full.list > /tmp/sync_restore.list
# 结果：37 张表

第3步：TRUNCATE 清空本地业务表

SET session_replication_role = replica;  -- 临时禁用触发器/外键检查
TRUNCATE TABLE
  sys_verification_code, sys_user_authorization, sys_task_execution_log,
  sys_scheduled_task, sys_role_permission, sys_role, sys_operation_log,
  sys_mu_employee_scope, sys_mu_dept_scope, sys_management_unit,
  sys_integration_config, sys_field_permission, sys_field_config, sys_feature,
  sys_employee, sys_email_config, sys_dept, sys_account,
  okr_visibility, okr_update_log, okr_progress_log, okr_plan_reminder,
  okr_plan_detail, okr_plan_auto_add_dept, okr_plan_auto_add, okr_plan,
  okr_period, okr_objective, okr_kr_external_anchor, okr_kr_decompose,
  okr_key_result, okr_follow, okr_comment_reference, okr_comment_mention,
  okr_comment, okr_notification, okr_alignment
CASCADE;
SET session_replication_role = DEFAULT;  -- 恢复触发器

第4步：pg_restore 导入正式库数据

pg_restore -h localhost -p 5432 -U openclaw \
  -d pms_dev \
  --data-only \              # 只导数据，不碰表结构
  --no-owner \               # 不还原属主
  --no-privileges \          # 不还原权限
  --single-transaction \     # 整体事务，失败回滚
  --disable-triggers \       # 导入时禁用触发器（避免外键报错）
  -L /tmp/sync_restore.list \
  /tmp/pms_prod_20260731_134949.dump
# 结果：EXIT_CODE=0（成功，无报错）

第5步：重置自增序列

-- 自动遍历所有 sys_/okr_ 序列，设置为 MAX(id)+1
-- 其中 okr_plan_auto_add_dept_id_seq1 序列名不规则（带1后缀），手动处理：
SELECT setval('okr_plan_auto_add_dept_id_seq1', 6, true);

序列重置参考

这是用 PostgreSQL 的 PL/pgSQL 匿名代码块（DO $$ ... $$） 实现的，通过系统表动态遍历所有序列，不用手动一个个写。

DO $$                        -- 开始一个匿名代码块（不建函数，执行完即丢弃）
DECLARE
  r RECORD;                  -- 声明一个行变量，用来存循环中的每一条记录
  max_id bigint;             -- 存当前表 MAX(id) 的值
BEGIN
  -- 1. 从 information_schema.sequences 查询所有 sys_/okr_ 开头的序列
  FOR r IN
    SELECT sequence_name
    FROM information_schema.sequences
    WHERE sequence_schema = 'public'
      AND (sequence_name LIKE 'sys\_%' ESCAPE '\' 
        OR sequence_name LIKE 'okr\_%' ESCAPE '\')
  LOOP
    BEGIN
      -- 2. 从序列名推断表名（去掉末尾的 _id_seq）
      --    比如 sys_account_id_seq → sys_account
      --    然后查这张表的 MAX(id)
      EXECUTE format(
        'SELECT COALESCE(MAX(id), 0) + 1 FROM public.%I',
        regexp_replace(r.sequence_name, '_id_seq$', '')
      ) INTO max_id;

      -- 3. 把序列值设置为 max_id
      --    setval(序列名, 值, is_called)
      --    is_called=true 表示下次 nextval() 会返回 max_id+1？不对：
      --    实际含义：setval(seq, N, true)  → 下一个 nextval() 返回 N+1？不，等下
      EXECUTE format('SELECT setval(%L, %s, true)', r.sequence_name, max_id);
    EXCEPTION WHEN OTHERS THEN
      -- 4. 如果某张表没有 id 列，或序列名不规则，跳过不报错
      RAISE NOTICE 'skip sequence %: %', r.sequence_name, SQLERRM;
    END;
  END LOOP;
END $$;
逐步解释
1. 找序列：查 information_schema.sequences

PostgreSQL 会把所有序列（sequence）登记在 information_schema.sequences 这个系统视图里：

SELECT sequence_name FROM information_schema.sequences 
WHERE sequence_schema='public';

返回类似：

sys_account_id_seq
sys_dept_id_seq
okr_objective_id_seq
okr_key_result_id_seq
...
flyway_schema_history_seq   ← 我们不处理

用 LIKE 'sys\_%' 过滤，只取业务表的序列。
2. 推断表名：regexp_replace 去掉后缀

PostgreSQL 建表时如果用 SERIAL 或 BIGSERIAL，会自动创建名为 表名_id_seq 的序列。所以反过来，把序列名末尾的 _id_seq 去掉就是表名：

regexp_replace('sys_account_id_seq', '_id_seq$', '')  → 'sys_account'

3. 动态 SQL：EXECUTE format(...)

因为表名是动态的（循环里每次不一样），不能直接写在 SQL 里，需要用 EXECUTE 执行拼出来的动态 SQL。

%I 是 format() 的标识符占位符，会自动给表名加双引号并处理特殊字符，防止 SQL 注入。

-- 假设 r.sequence_name = 'sys_account_id_seq'，这步实际执行的是：
SELECT COALESCE(MAX(id), 0) + 1 FROM public.sys_account;
-- 假设 MAX(id) = 2132，那 max_id = 2133

4. setval() 重置序列

SELECT setval('sys_account_id_seq', 2133, true);

setval 参数含义：
参数    值    含义
第1个    序列名    要操作的序列
第2个    2133    当前值设为 2133
第3个    true    表示 2133 已经被 nextval() 返回过了，下一次 nextval() 返回 2134
第3个    false    表示 2133 还没被返回过，下一次 nextval() 返回 2133

我们的 max_id 是 MAX(id) + 1，比如 MAX=2132，max_id=2133，setval 到 2133（is_called=true），那么下一个 INSERT 时 nextval() 返回 2134？不对，应该返回 2133……

⚠️ 等等，我回头看了下，我写的逻辑有个问题。让我验证一下：

-- MAX(id) = 2132
-- max_id = 2132 + 1 = 2133
-- setval('seq', 2133, true)  →  下一个 nextval() = 2134  ❌ 多了1
-- 正确应该是 setval('seq', 2133, false) → 下一个 nextval() = 2133  ✅
-- 或者 setval('seq', 2132, true)  → 下一个 nextval() = 2133  ✅

不过实际上不会出问题，因为 PostgreSQL 序列是用来保证唯一的，跳几个号无所谓，只要不冲突就行。跳一号不会影响功能，只是自增 ID 不是连续的而已。

如果你想修正为精确值，可以改成：

EXECUTE format(
  'SELECT COALESCE(MAX(id), 0) FROM public.%I',
  regexp_replace(r.sequence_name, '_id_seq$', '')
) INTO max_id;

EXECUTE format('SELECT setval(%L, %s, true)', r.sequence_name, max_id);

这样 setval(seq, MAX(id), true) → 下一个 nextval() 返回 MAX(id)+1，正好是下一条记录的 ID，不会跳号。
5. 异常处理：EXCEPTION WHEN OTHERS

不是所有序列都叫 xxx_id_seq，比如 okr_plan_auto_add_dept_id_seq1（之前发现的那个带 1 后缀的不规则序列），去掉 _id_seq 后得到 okr_plan_auto_add_dept1，而实际表名是 okr_plan_auto_add_dept，查表会报错。

加了 BEGIN ... EXCEPTION WHEN OTHERS 块，单条失败不影响整体循环，只打一个 NOTICE 跳过。
总结

本质就是一个"从系统表查元数据 → 循环拼动态 SQL → 执行"的模式，不用手写几十条 SELECT setval(...)，以后新增表也自动覆盖