PG：ERROR: cannot freeze committed xmax

原创已于 2025-02-22 16:52:38 修改 · 1.2k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2025-02-22 14:57:44 首次发布

PG 专栏收录该内容

355 篇文章

订阅专栏

原因

问题原因

事务 ID 状态不一致：
- Xmax 标记的事务已提交，但在事务日志（CLOG）中缺少对应事务的状态记录。
- 这种不一致可能是由于以下原因：
  - 数据损坏：某些数据页可能损坏，导致元组状态与事务日志不一致。
  - 旧版本升级问题：数据库可能从较旧版本升级到当前版本，旧版本可能存在未修复的 bug，导致数据页状态异常。
  - autovacuum 未正确清理：某些元组可能未被 autovacuum 正常清理，导致事务 ID 无法冻结。
事务日志（CLOG）的作用：
- CLOG 用于记录事务的提交状态。当 VACUUM FREEZE 尝试冻结事务 ID 时，需要验证该事务是否已提交。
- 如果 CLOG 中缺少对应事务的状态记录，VACUUM FREEZE 无法验证事务是否实际提交，从而导致冻结过程失败。

PostgreSQL 底层逻辑

事务 ID 和冻结机制：
- PostgreSQL 使用事务 ID（xid）来标记数据修改。为了防止事务 ID 溢出，VACUUM FREEZE 会定期回收旧的事务 ID。
- 冻结过程中，VACUUM 会检查每个元组的 Xmax（即修改该元组的事务 ID），并验证该事务是否已提交。
CLOG 的作用：
- CLOG 是事务日志，用于记录事务的提交状态。每个事务在提交时，都会在 CLOG 中记录其状态。
- 当 VACUUM FREEZE 尝试冻结事务 ID 时，会检查 CLOG 中的记录，以确认事务是否已提交。
冻结失败的原因：
- 如果 Xmax 标记的事务已提交，但 CLOG 中缺少对应事务的状态记录，VACUUM FREEZE 无法验证事务是否实际提交，从而导致冻结失败。
- 这种情况通常发生在以下场景：
  - 数据库从较旧版本升级，旧版本可能存在未修复的 bug。
  - 数据页损坏，导致元组状态与事务日志不一致。
  - autovacuum 未正确清理，导致事务 ID 无法冻结。

解决方案1

问题分析

错误含义：
- VACUUM FREEZE 的目的是回收旧的事务 ID（xid），以防止事务 ID 溢出。
- 错误提示 cannot freeze committed xid xxx 表示某个事务 ID 无法被冻结，可能是因为该事务 ID 仍然被某些查询或事务引用。
可能原因：
- 长时间运行的事务：某些事务可能长时间未提交或回滚，导致其占用的事务 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）。

问题分析

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

SELECT txid_status(572);

可能的原因：
- 数据损坏：某些数据页可能损坏，导致元组状态不一致。
- 旧版本升级问题：数据库可能从较旧版本升级到 PostgreSQL 10.14，旧版本可能存在未修复的 bug，导致数据页状态异常。
- autovacuum 未正确清理：某些元组可能未被 autovacuum 正常清理，导致事务 ID 无法冻结。
关键发现：
- 问题行在 pg_proc 和 pg_depend 表中仍然可见，且与一个自定义存储函数相关。
- 数据库初始化时间较短（约一年），初始版本为 PostgreSQL 10.x。
- autovacuum_freeze_max_age 设置为 200000000，但表的 relfrozenxid 年龄已超过该值。

解决方案

1. 手动清理问题元组

操作步骤：

备份数据库：
```
pg_dumpall -U username > backup.sql
```
在测试环境中验证：
- 在测试环境中执行以下命令，确保不会对数据库造成不可逆的损害。

执行删除操作：

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)');

检查结果：

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` 配置

操作步骤：

临时调整 autovacuum 配置：

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

重启数据库以应用更改：
```
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 标志的错误。
- 如果问题行仍然可见，可以尝试手动删除它们，但需要谨慎操作。