PostgreSQL 10.16手动VACUUM事务Idle多日原因排查求助

问题背景 #

近日收到数据库错误提示，为避免事务ID回卷导致数据丢失，手动执行了VACUUM/ANALYZE：

ERROR:  database is not accepting commands to avoid wraparound data loss in database "xxxx"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

我们已配置自动VACUUM，数据库多年运行正常，对此次错误原因存疑。11/23启动VACUUM/ANALYZE后数据库恢复正常，但该事务自11/24起处于idle状态，查询到的进程信息如下：

datid | datname | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |        backend_start         | xact_start |          query_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |                           query                           |  backend_type
-------+---------+------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+-----------------------------------------------------------+----------------
 16452 | XXXX | 7992 |       10 | postgres | psql             |             |                 |          -1 | 2024-11-23 05:09:41.97984-05 |            | 2024-11-24 11:36:01.526247-05 | 2024-11-24 11:36:01.768001-05 | Client          | ClientRead | idle  |             |              | vacuum (verbose,analyze) XXXX_XXXXXX.XXXXXX; | client backend
(1 row)

当前数据库运行正常，服务器CPU、内存使用率处于正常范围，请问该VACUUM/ANALYZE事务为何处于idle状态？

原因分析 #

从进程查询结果的关键字段可以明确：这个VACUUM(VERBOSE, ANALYZE)任务已经执行完成，当前的idle状态只是对应的psql客户端会话未退出，处于等待用户输入的状态，具体依据如下：

• xact_start字段为空，说明当前没有活跃事务，证明VACUUM/ANALYZE已经结束执行
• state为idle、wait_event_type为Client、wait_event为ClientRead，表示后端进程正在等待客户端（此处是psql）发送新的命令，而非在运行VACUUM任务
• query字段显示的是该会话最后执行的命令，PostgreSQL会在进程处于idle状态时保留最后执行的SQL语句，并非当前正在运行的任务

另外补充关于此前事务ID回卷错误的可能原因：尽管配置了自动VACUUM，但可能因以下情况触发风险：

• 存在长期运行的大事务，占据了较旧的xmin，导致自动VACUUM无法清理足够的旧事务ID
• 自动VACUUM相关参数（如vacuum_freeze_min_age、vacuum_freeze_table_age）设置不合理，未及时触发冻结操作
• 部分表的自动VACUUM被手动禁用

建议操作 #

• 可直接终止该idle会话，执行命令：SELECT pg_terminate_backend(7992);，不会对数据库运行造成影响
• 检查自动VACUUM的配置参数，排查是否存在长期运行的事务，避免再次触发事务ID回卷风险
• 手动执行大表的VACUUM(ANALYZE)后，及时关闭psql会话，避免残留idle进程

内容的提问来源于stack exchange，提问作者CJ Chang