AnolisOS 8.8 PostgreSQL 单用户模式的维护

2401_83375581

于 2024-08-20 08:15:00 发布

阅读量558

点赞数 13

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/2401_83375581/article/details/141185447

版权

PostgreSQL 单用户模式

前段时间帮助一个朋友看过这样一个问题，PostgreSQL 数据库提示你需要使用单用户模式进入然后进行数据库维护。

下面是具体的报错信息：

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

下面是具体的解决办法和步骤：

备注：单用户模式下维护数据库的时候需要先关闭数据库服务
pg_ctl stop -D /usr/local/pgsql-14.5/data
postgres --single -D /usr/local/pgsql-14.5/data dbname
vacuum full dbname;

那么为什么会发生这种情况呢？如何去避免这种情况的发生呢？

PostgreSQL 中的 事务 ID wraparound 问题发生在以下情况下：

1. 长时间运行的事务

如果存在长时间运行的事务，这些事务会保留较早的事务 ID 的信息，从而阻止系统清理过时的数据。这会导致数据 “冻结” 状态，增加 XID 的负担。

2. 缺乏自动化清理

如果数据库的 autovacuum 设置不当，可能会导致系统在需要时未能执行有效的清理操作。如果 autovacuum 被禁用或设置间隔时间过长，可能无法及时处理过时的事务数据。

3. 高并发写入

在高并发的写入场景中，事务 ID 会快速增加。如果没有及时的清理，这可能会导致快速接近事务 ID 的最大值。

4. 没有正常维护

如果数据库没有定期执行 VACUUM 操作，未清理的行和事务 ID 将不断累积，从而导致事务 ID 达到上限。

5. 不合适的配置

某些配置参数如 max_fsm_pages（最大自由空间管理页面数）和 max_fsm_relations（最大自由空间管理关系数）设置不当，可能导致 PostgreSQL 在执行清理时无法有效回收空间。

6. 数据修改频繁

频繁的插入、更新和删除操作会导致大量的死行（即未被回收的行），进而加速事务 ID 的消耗。

定期监控数据库中的死行即已删除或已更新但尚未被清理的行和事务 ID 使用情况。可以使用以下查询：

SELECT age(datfrozenxid) AS frozen_age, 
       datname 
FROM pg_database 
WHERE datname = 'your_database_name';

下面是关于 pg_database 的介绍

pg_database 的列

名称	类型	引用	描述
`oid`	`oid`		行标识符
`datname`	`name`		数据库名字
`datdba`	`oid`	`pg_authid.oid`	数据库的拥有者，通常是创建它的用户
`encoding`	`int4`		此数据库的字符编码的编号 `pg_encoding_to_char()`可将此编号转换成编码的名字
`datcollate`	`name`		此数据库的LC_COLLATE
`datctype`	`name`		此数据库的LC_CTYPE
`datistemplate`	`bool`		如果为真，则此数据库可被任何具有 `CREATEDB` 特权的用户克隆；如果为假，则只有超级用户或者该数据库的属主能够克隆它。
`datallowconn`	`bool`		如果为假则没有人能连接到这个数据库。这可以用来保护 `template0` 数据库不被修改。
`datconnlimit`	`int4`		设置能够连接到这个数据库的最大并发连接数。-1表示没有限制。
`datlastsysoid`	`oid`		数据库中最后一个系统 OID，对 pg_dump 特别有用
`datfrozenxid`	`xid`		在此之前的所有事务 ID 在数据库中已经被替换为一个永久的（“冻结的”) 事务ID。这用于跟踪数据库是否需要被清理，以便组织事务ID回环或者允许 `pg_xact` 被收缩。它是此数据库中所有表的 `pg_class`.`relfrozenxid` 值的最小值。
`datminmxid`	`xid`		在此之前的所有多事务ID在数据库中已经被替换为一个事务ID。这用于跟踪数据库是否需要被清理，以便组织事务ID回环或者允许 `pg_multixact` 被收缩。它是此数据库中所有表的 `pg_class`.`relminmxid` 值的最小值。
`dattablespace`	`oid`	`pg_tablespace.oid`	此数据库的默认表空间。在此数据库中，所有 `pg_class`.`reltablespace` 为 0 的表都将被存储在这个表空间中，尤其是非共享系统目录都会在其中。
`datacl`	`aclitem[]`		访问权限，更多信息参见第 5.7 节