国庆节2号 PostgreSQL 停机罢工协助解决问题得 66.66元的红包-优快云博客

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

回顾一下2025年哪里也没有出去玩的国庆节，除了单位值班，还和另外的一个同学抢救了一次PostgreSQL 急速冻结炸弹的问题。假期期间，10月2日下午接到一条私信，信息下方截图。最近单位忙，业务多假期虽说有8天但是值班，且还的负责整体的各个业务线整体的数据库稳定，还的安排其他人员的值班工作。当天下午拜完菩萨，望菩萨保佑，刚跨出寺庙这微信就来了。

PostgreSQL 数据库事务ID回卷

当时要回去所以一边走路一边回复，期间里面还有一些事情，最后问题应该是解决了，同学还给了66.66的红包。从开始问问题的下午2点多，到收到红包的10月3日凌晨3点多，14个小时的抢救。

66.66红包

这里想梳理一下如果遇到这样的情况，应该怎么处理，总结一篇帖子，方便后续有人遇到这样的情况，能快速处理。

首先第一个问题，为什么会出现这个问题，可以简单进行总结

1 Autovacuum 对于表的规律vaccum工作实效了，可能是针对某张表，当然也可能是全部失效。

2 数据库存在没有commit的事务

3 逻辑复制槽对方无法消费数据，或逻辑复制槽失效，但没有清理导致无法进行事务ID的回收。

但最终我们归结于一个问题，我们没有做好事务TXID的监控，也就是我们俗称的age老化。

1 查看当前的数据库中的表，哪些表超过3天没有autovacuum 过

postgres=# SELECT
postgres-#     schemaname AS "模式名",
postgres-#     relname AS "表名",
postgres-#     pg_size_pretty(pg_relation_size(relid)) AS "表大小",
postgres-#     n_dead_tup AS "死元组数量",
postgres-#     last_autovacuum AS "最后一次 Autovacuum 完成时间",
postgres-#     CASE
postgres-#         WHEN last_autovacuum IS NULL THEN '从未运行'
postgres-#         ELSE (now() - last_autovacuum)::interval(0) || ' 前'
postgres-#     END AS "距离上次运行间隔"
postgres-# FROM
postgres-#     pg_stat_user_tables
postgres-# WHERE
postgres-#     last_autovacuum IS NULL
postgres-#     OR last_autovacuum < (now() - interval '3 days')
postgres-# ORDER BY
postgres-#     last_autovacuum NULLS FIRST, n_dead_tup DESC;
 模式名 | 表名 | 表大小 | 死元组数量 | 最后一次 Autovacuum 完成时间 | 距离上次运行间隔 
--------+------+--------+------------+------------------------------+------------------
(0 rows)

postgres=#

同时我们可以查阅当前PG中那个数据库的age最大，然后进入后，扫描其中age最大的10个表是那些

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    c.relfrozenxid AS oldest_unfrozen_txid,
    age(c.relfrozenxid) AS txid_distance_to_current,
    (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS freeze_max_age_limit
FROM
    pg_class c
JOIN
    pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind = 'r' -- 只查找常规表
    AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') -- 排除系统自带的模式
ORDER BY
    age(c.relfrozenxid) DESC -- 距离当前事务 ID 越远，值越大
LIMIT 10;

这里我们模拟一下，让数据库进入单用户模式，通过单用户模式来模拟一些操作,在你进入单用户模式中，请注意两点。

1 请在启动数据库前，物理备份你的数据库目录下的数据文件。 2 在进入到单用户模式，进行回收操作的时候，不能人为进行停止操作

backend> cuum IS NULL THEN '从未运行'
        ELSE (now() - last_autovacuum)::interval(0) || ' 前'
    END AS "距离上次运行间隔"
FROM
    pg_stat_user_tables
WHERE
    last_autovacuum IS NULL
    OR last_autovacuum < (now() - interval '3 days')
ORDER B2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "WHEN" at character 9
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:          WHEN last_autovacuum IS NULL THEN '从未运行'

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "ELSE" at character 9
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:          ELSE (now() - last_autovacuum)::interval(0) || ' 前'

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "AS" at character 9
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:      END AS "距离上次运行间隔"

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "FROM" at character 1
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:  FROM

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "pg_stat_user_tables" at character 5
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:      pg_stat_user_tables

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "WHERE" at character 1
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:  WHERE

backend> 2025-06-02 05:56:58.405 EDT [4150] ERROR:  syntax error at or near "last_autovacuum" at character 5
2025-06-02 05:56:58.405 EDT [4150] STATEMENT:      last_autovacuum IS NULL

backend> 2025-06-02 05:56:58.406 EDT [4150] ERROR:  syntax error at or near "OR" at character 5
2025-06-02 05:56:58.406 EDT [4150] STATEMENT:      OR last_autovacuum < (now() - interval '3 days')

backend> Y
    last_autovacuum NULLS FIRST, n_dead_tup DESC;2025-06-02 05:56:58.423 EDT [4150] ERROR:  syntax error at or near "ORDER" at character 1
2025-06-02 05:56:58.423 EDT [4150] STATEMENT:  ORDER BY

这里一些情况需要告知客户或者领导，不要因为等的时间太长，而正在进行vacuum操作又kill他。语句在单用户模式执行时，不能直接带着tab 回车等符号粘贴，否则会被认为是多个单条语句

1 如何进入单用户模式

[postgres@postgresql17 ~]$ 
[postgres@postgresql17 ~]$ 
[postgres@postgresql17 ~]$ 
[postgres@postgresql17 ~]$ /usr/local/postgres/bin/postgres --single -D /pgdata/data/  postgres -P

PostgreSQL stand-alone backend 17.4
backend> 
backend> 
backend> 
backend> 
backend>

在登录到单用户模式后，有几个问题

1 单用户模式直接就进入到了backend模式，这里进入后并不能像之前操作Postgres中一样，通过简短的快捷操作，获得数据库系统的信息，这里仅仅只能通过对表的一些操作获得系统中的你需要的信息。如 \l 查看当前数据库的信息，是不可以的，只能通过 select * from pg_database;来获取信息

backend> select * from pg_database;
  1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype (typeid = 25, len = -1, typmod = -1, byval = f)
 15: datlocale (typeid = 25, len = -1, typmod = -1, byval = f)
 16: daticurules (typeid = 25, len = -1, typmod = -1, byval = f)
 17: datcollversion (typeid = 25, len = -1, typmod = -1, byval = f)
 18: datacl (typeid = 1034, len = -1, typmod = -1, byval = f)
 ----
  1: oid = "5" (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname = "postgres" (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding = "6" (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider = "c" (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid = "1395194" (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid = "1" (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 17: datcollversion = "2.28" (typeid = 25, len = -1, typmod = -1, byval = f)
 ----
  1: oid = "16384" (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname = "my_pgbench_db" (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding = "6" (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider = "c" (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid = "730" (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid = "1" (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 17: datcollversion = "2.28" (typeid = 25, len = -1, typmod = -1, byval = f)
 ----
  1: oid = "1" (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname = "template1" (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding = "6" (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider = "c" (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid = "730" (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid = "1" (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 17: datcollversion = "2.28" (typeid = 25, len = -1, typmod = -1, byval = f)
 18: datacl = "{=c/postgres,postgres=CTc/postgres}" (typeid = 1034, len = -1, typmod = -1, byval = f)
 ----
  1: oid = "4" (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname = "template0" (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding = "6" (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider = "c" (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid = "730" (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid = "1" (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 18: datacl = "{=c/postgres,postgres=CTc/postgres}" (typeid = 1034, len = -1, typmod = -1, byval = f)
 ----
  1: oid = "16591" (typeid = 26, len = 4, typmod = -1, byval = t)
  2: datname = "wrapdb" (typeid = 19, len = 64, typmod = -1, byval = f)
  3: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval = t)
  4: encoding = "6" (typeid = 23, len = 4, typmod = -1, byval = t)
  5: datlocprovider = "c" (typeid = 18, len = 1, typmod = -1, byval = t)
  6: datistemplate = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  7: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
  8: dathasloginevt = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
  9: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
 10: datfrozenxid = "730" (typeid = 28, len = 4, typmod = -1, byval = t)
 11: datminmxid = "1" (typeid = 28, len = 4, typmod = -1, byval = t)
 12: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1, byval = t)
 13: datcollate = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 14: datctype = "en_US.UTF-8" (typeid = 25, len = -1, typmod = -1, byval = f)
 17: datcollversion = "2.28" (typeid = 25, len = -1, typmod = -1, byval = f)
 ----
backend>

我们下面做一个练习

postgres=# \l
                                                       List of databases
     Name      |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
---------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 my_pgbench_db | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 postgres      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
               |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
               |          |          |                 |             |             |        |           | postgres=CTc/postgres
 wrapdb        | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
(5 rows)

postgres=# \c wrapdb
You are now connected to database "wrapdb" as user "postgres".
wrapdb=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | bigtbl        | table    | postgres
 public | bigtbl_id_seq | sequence | postgres
(2 rows)

wrapdb=# \d bigtbl
                             Table "public.bigtbl"
 Column  |  Type   | Collation | Nullable |              Default               
---------+---------+-----------+----------+------------------------------------
 id      | integer |           | not null | nextval('bigtbl_id_seq'::regclass)
 payload | text    |           |          | 
Indexes:
    "bigtbl_pkey" PRIMARY KEY, btree (id)

wrapdb=#

我们假设我们的autovacuum 的进程掉了，系统不在进行autovacuum

postgres=# SELECT
    datname AS "数据库名",
    age(datfrozenxid) AS "XID 年龄 (已消耗的 ID 数量)",
    current_setting('autovacuum_freeze_max_age') AS "Autovacuum 冻结阈值",
    (age(datfrozenxid) / current_setting('autovacuum_freeze_max_age')::float) * 100 AS "接近阈值的百分比"
FROM
    pg_database
ORDER BY
    age(datfrozenxid) DESC;
 数据库名  | XID 年龄 (已消耗的 ID 数量) | Autovacuum 冻结阈值 |  接近阈值的百分比  
-----------+-----------------------------+---------------------+--------------------
 wrapdb    |                        1188 | 100001              | 1.1879881201187987
 postgres  |                        1137 | 100001              | 1.1369886301136989
 template1 |                        1135 | 100001              |  1.134988650113499
 template0 |                        1132 | 100001              | 1.1319886801131989

然后我们进入到单用户模式，这里注意我们要对数据库进行vacuum freeze 的工作，对整个数据库进行相关的操作，如果你明确是某个表导致的问题，也可以针对这个表进行vacuum freeze的操作。

国庆节2号 PostgreSQL 停机罢工 协助 解决问题得 66.66元的红包

国庆节2号 PostgreSQL 停机罢工协助解决问题得 66.66元的红包