PG：ERROR: cannot freeze committed xmax

原因

问题原因

1. 事务 ID 状态不一致：

   • Xmax 标记的事务已提交，但在事务日志（CLOG）中缺少对应事务的状态记录。
   • 这种不一致可能是由于以下原因：
     • 数据损坏：某些数据页可能损坏，导致元组状态与事务日志不一致。
     • 旧版本升级问题：数据库可能从较旧版本升级到当前版本，旧版本可能存在未修复的 bug，导致数据页状态异常。
     • autovacuum 未正确清理：某些元组可能未被 autovacuum 正常清理，导致事务 ID 无法冻结。

2. 事务日志（CLOG）的作用：

   • CLOG 用于记录事务的提交状态。当 VACUUM FREEZE 尝试冻结事务 ID 时，需要验证该事务是否已提交。
   • 如果 CLOG 中缺少对应事务的状态记录，VACUUM FREEZE 无法验证事务是否实际提交，从而导致冻结过程失败。

PostgreSQL 底层逻辑

1. 事务 ID 和冻结机制：

   • PostgreSQL 使用事务 ID（xid）来标记数据修改。为了防止事务 ID 溢出，VACUUM FREEZE 会定期回收旧的事务 ID。
   • 冻结过程中，VACUUM 会检查每个元组的 Xmax（即修改该元组的事务 ID），并验证该事务是否已提交。

2. CLOG 的作用：

   • CLOG 是事务日志，用于记录事务的提交状态。每个事务在提交时，都会在 CLOG 中记录其状态。
   • 当 VACUUM FREEZE 尝试冻结事务 ID 时，会检查 CLOG 中的记录，以确认事务是否已提交。

3. 冻结失败的原因：

   • 如果 Xmax 标记的事务已提交，但 CLOG 中缺少对应事务的状态记录，VACUUM FREEZE 无法验证事务是否实际提交，从而导致冻结失败。
   • 这种情况通常发生在以下场景：
     • 数据库从较旧版本升级，旧版本可能存在未修复的 bug。
     • 数据页损坏，导致元组状态与事务日志不一致。
     • autovacuum 未正确清理，导致事务 ID 无法冻结。

解决方案1

问题分析

1. 错误含义：

   • VACUUM FREEZE 的目的是回收旧的事务 ID（xid），以防止事务 ID 溢出。
   • 错误提示 cannot freeze committed xid xxx 表示某个事务 ID 无法被冻结，可能是因为该事务 ID 仍然被某些查询或事务引用。

2. 可能原因：

   • 长时间运行的事务：某些事务可能长时间未提交或回滚，导致其占用的事务 ID 无法被冻结。
   • 未正确关闭的连接：某些客户端连接可能未正常关闭，导致事务 ID 仍然被占用。
   • 配置问题：autovacuum 配置可能不足以处理当前的工作负载。

排查步骤

1. 检查长时间运行的事务

运行以下查询，检查是否有长时间运行的事务：

SELECT pid, query_start, query, state
FROM pg_stat_activity
WHERE state = 'active' AND query IS NOT NULL;

• 如果发现有长时间运行的事务，可以尝试终止这些事务：

  SELECT pg_terminate_backend(pid);
  

2. 检查未提交的事务

运行以下查询，检查是否有未提交的事务：

SELECT pid, query_start, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

• 如果发现有未提交的事务，可以尝试终止这些事务：

  SELECT pg_terminate_backend(pid);
  

3. 检查 autovacuum 配置

autovacuum 是 PostgreSQL 的自动清理机制，负责定期运行 VACUUM 和 ANALYZE。如果配置不当，可能会导致事务 ID 冻结失败。

运行以下命令，检查 autovacuum 配置：

SHOW autovacuum;
SHOW autovacuum_freeze_max_age;
SHOW autovacuum_multixact_freeze_max_age;

• 确保 autovacuum 是启用的，并且 autovacuum_freeze_max_age 和 autovacuum_multixact_freeze_max_age 的值适合您的工作负载。

4. 检查事务 ID 使用情况

运行以下查询，检查当前事务 ID 的使用情况：

SELECT pg_current_xact_id();

• 如果事务 ID 接近 2^32 - 1（即 4294967295），需要尽快解决事务 ID 溢出问题。

5. 检查表的 relfrozenxid

运行以下查询，检查表的冻结事务 ID：

SELECT relname, relfrozenxid
FROM pg_stat_user_tables
WHERE relfrozenxid != 0;

• 如果某些表的 relfrozenxid 过旧，可能需要手动运行 VACUUM FREEZE 或调整 autovacuum 配置。

解决方法

1. 手动运行 VACUUM FREEZE

尝试手动运行 VACUUM FREEZE，以强制回收旧的事务 ID：

VACUUM (FREEZE, ANALYZE);

• 如果仍然报错，可以尝试分表运行：

  VACUUM (FREEZE, ANALYZE) table_name;
  

2. 调整 autovacuum 配置

根据工作负载，调整 autovacuum 配置：

ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_freeze_max_age = 100000000;
ALTER SYSTEM SET autovacuum_multixact_freeze_max_age = 100000000;

• 重启数据库以应用更改：

  sudo systemctl restart postgresql
  

3. 检查并终止异常连接

如果发现有异常连接或长时间运行的事务，可以终止这些连接：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' OR state = 'idle in transaction';

4. 备份并重启数据库

如果上述方法无效，可以考虑备份数据库并重启数据库：

pg_dumpall -U username > backup.sql
sudo systemctl restart postgresql

总结

• 优先检查长时间运行的事务和未提交的事务。
• 调整 autovacuum 配置，确保自动清理机制正常运行。
• 手动运行 VACUUM FREEZE，分表处理以避免冲突。
• 备份数据库并重启，作为最后的手段。

如果问题仍然存在，可以提供更多的上下文信息（例如数据库版本、表结构、工作负载等），以便进一步分析。

解决方案2

PostgreSQL 数据库中出现的 ERROR: cannot freeze committed xmax 错误的讨论。以下是该内容的总结和分析：

问题描述

• 错误信息：ERROR: cannot freeze committed xmax 572
• 环境：PostgreSQL 10.14，运行在 RHEL 系统上。
• 现象：该错误发生在 autovacuum 过程中，导致 autovacuum freeze 进程失败。
• 受影响的表：pg_proc 和 pg_depend 两个系统表。
• 查询结果：
  • txid_status(572) 显示事务 ID 572 已提交。
  • 在 pg_proc 和 pg_depend 表中，事务 ID 572 仍然存在，且某些元组的 xmax 被标记为回滚（xmax_rolled_back = t）。

问题分析

1. 事务 ID 状态不一致：
   • 事务 ID 572 在 txid_status 查询中显示为已提交，但在某些表的页面级别上，元组的 xmax 被标记为回滚。
   • 这种不一致可能是由于数据页的元组状态与事务 ID 的实际状态不匹配。

SELECT txid_status(572);

2. 可能的原因：

   • 数据损坏：某些数据页可能损坏，导致元组状态不一致。
   • 旧版本升级问题：数据库可能从较旧版本升级到 PostgreSQL 10.14，旧版本可能存在未修复的 bug，导致数据页状态异常。
   • autovacuum 未正确清理：某些元组可能未被 autovacuum 正常清理，导致事务 ID 无法冻结。

3. 关键发现：

   • 问题行在 pg_proc 和 pg_depend 表中仍然可见，且与一个自定义存储函数相关。
   • 数据库初始化时间较短（约一年），初始版本为 PostgreSQL 10.x。
   • autovacuum_freeze_max_age 设置为 200000000，但表的 relfrozenxid 年龄已超过该值。

解决方案

1. 手动清理问题元组

• 操作步骤：
  1. 备份数据库：

     pg_dumpall -U username > backup.sql
     

  2. 在测试环境中验证：
     • 在测试环境中执行以下命令，确保不会对数据库造成不可逆的损害。
  3. 执行删除操作：

     DELETE FROM pg_proc WHERE ctid = '(75,19)';
     DELETE FROM pg_depend WHERE ctid IN ('(55,76)', '(55,77)', '(55,78)', '(55,79)', '(55,80)');
     

  4. 检查结果：

     SELECT COUNT(*) FROM pg_proc WHERE ctid = '(75,19)';
     SELECT COUNT(*) FROM pg_depend WHERE ctid IN ('(55,76)', '(55,77)', '(55,78)', '(55,79)', '(55,80)');
     

• 注意事项：
  • 直接操作系统表存在风险，建议在测试环境验证后再执行。

2. 调整 autovacuum 配置

• 操作步骤：
  1. 临时调整 autovacuum 配置：

     ALTER SYSTEM SET vacuum_freeze_min_age = 100000000;
     ALTER SYSTEM SET autovacuum_freeze_max_age = 200000000;
     

  2. 重启数据库以应用更改：

     sudo systemctl restart postgresql
     

• 参考：PostgreSQL的AutoVacuum原理及autovacuum不工作问题解析 [²]

3. 检查并修复数据损坏

• 操作步骤：
  1. 使用 CHECKPOINT 和 VACUUM 检查数据一致性：

     CHECKPOINT;
     VACUUM (FULL, ANALYZE);
     

  2. 如果问题仍然存在：
     • 备份数据库并重新初始化。
• 参考：PostgreSQL中VACUUM操作的优化、监控与故障排查技巧 [⁵]

4. 升级数据库

• 操作步骤：
  1. 备份数据库：

     pg_dumpall -U username > backup.sql
     

  2. 升级数据库：
     • 根据您的数据库版本，选择合适的升级路径。例如，从 PostgreSQL 10.14 升级到最新版本。
  3. 验证升级结果：
     • 确保升级后数据库正常运行，并再次尝试 VACUUM FREEZE 操作。
• 参考：PostgreSQL 官方安全漏洞列表 [⁶]

总结

• 优先检查长时间运行的事务和未提交的事务。
• 调整 autovacuum 配置，确保自动清理机制正常运行。
• 手动运行 VACUUM FREEZE，分表处理以避免冲突。
• 备份数据库并重启，作为最后的手段。
• 升级数据库，以避免已知的 bug。

如果问题仍然存在，可以提供更多的上下文信息（例如数据库版本、表结构、工作负载等），以便进一步分析。

专家建议

• Álvaro Herrera（PostgreSQL 开发者）提到：
  • 这些事务 ID 可能较旧，可能与数据库初始化时的版本有关。
  • 一个可能的候选修复是 PostgreSQL 10.4 中的补丁，修复了 all-visible 标志的错误。
  • 如果问题行仍然可见，可以尝试手动删除它们，但需要谨慎操作。

总结

• 问题原因：事务 ID 状态不一致，可能是由于数据损坏或旧版本升级问题。
• 解决方法：手动清理问题元组、调整 autovacuum 配置、检查数据一致性或升级数据库。
• 注意事项：直接操作系统表存在风险，建议在测试环境验证后再执行。

如果您需要进一步的帮助，可以提供更多上下文信息或尝试上述解决方案。