pg_wal 目录下 wal 日志文件异常累积过大

背景

我准备复制一个数据库实例, 先停库然后通过 rsync 复制

 rsync -a --delete  /var/runtime/database/* /var/runtime/database_v1_`date +%F`

发现快一分钟还没完成, 检查发现 /var/runtime/database/pg_wal 占用大约 8.8G 过大

当前配置

max_wal_size=10G
checkpoint_timeout=‘60min’
archive_mode=off
没开归档,没有备库,没有逻辑复制

分析解决过程

首先想到的方法是, 将 max_wal_size改为 1G
修改之后重载配置, 发现 pg_wal 还是 8.8G
然后重启数据库实例,发现 pg_wal 还是 8.8G
然后进行如下分析

根据你的描述，即使手动执行了多次 CHECKPOINT，pg_wal 目录的占用仍然没有减少，这表明可能存在以下几个原因需要排查：

1. 活动事务未完成

如果有长时间运行的事务（例如长时间运行的查询或未提交的事务），PostgreSQL 将不会删除旧的 WAL 文件。这是因为这些文件可能仍然被活动事务需要。

检查是否有长时间运行的事务：

SELECT pid, state, usename, query, age(clock_timestamp(), xact_start) AS transaction_age
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL
ORDER BY transaction_age DESC;

如果有长时间未完成的事务，请评估是否可以中止它们。

2. 备份滞后或归档未完成

如果启用了 archive_mode 且 archive_command 配置不正确或无法成功运行，WAL 文件将不会被删除。

检查归档配置和状态：

SHOW archive_mode;
SHOW archive_command;

查看未归档的 WAL 文件：

SELECT * FROM pg_stat_archiver;

如果 archived_count 没有增加或 last_failed_time 频繁出现，则表明归档可能存在问题。

3. 保留了过多的 WAL 文件

pg_wal 中的文件数量可能与 wal_keep_segments 或 wal_keep_size 配置相关。如果这些值设置过大，即使 max_wal_size 是 1GB，PostgreSQL 仍然会保留这些文件。

检查相关配置：

SHOW wal_keep_size;
SHOW max_wal_size;

调整配置（如果适用）：

ALTER SYSTEM SET wal_keep_size = '128MB';

4. 逻辑复制槽未释放

如果有未清理的逻辑复制槽，PostgreSQL 会保留相关的 WAL 文件，以便逻辑复制进程能够访问。

检查复制槽状态：

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

如果有未使用的复制槽，可以手动删除：

SELECT pg_drop_replication_slot('slot_name');

5. 文件系统问题

有时，即使数据库删除了文件，操作系统仍然报告高存储占用。这可能是由于文件被某些进程占用，但无法被释放。

检查是否有被占用的文件：

lsof +D /var/runtime/database/pg_wal

如果有占用，请考虑终止相关进程。

6. 强制触发 WAL 清理

如果上述方法无效，可以尝试强制清理 pg_wal 目录，方法如下：

1. 确保数据库没有长时间运行的事务和未完成的归档操作。
2. 执行 VACUUM 和 CHECKPOINT：

   VACUUM;
   CHECKPOINT;
   

3. 重启 PostgreSQL 服务：

   sudo systemctl restart postgresql
   

结果

根据上述思路进行排查之后, 发现是 文件被占用导致的

[root@localhost database]# lsof +D /var/runtime/database/pg_wal
COMMAND     PID     USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
postgres 270061 postgres    4u   REG  259,3 16777216 4997643 /var/runtime/database/pg_wal/000000010000000800000075
postgres 270064 postgres    4u   REG  259,3 16777216 4997643 /var/runtime/database/pg_wal/000000010000000800000075

lsof +D 是啥意思

输出字段含义：

• COMMAND：打开文件的进程名称。
• PID：打开文件的进程 ID。
• USER：运行进程的用户。
• FD：文件描述符。
• TYPE：文件类型（如 REG 表示常规文件）。
• NAME：被打开的文件路径。

检查进程

ps -ef | grep 270061 | grep -v grep 
ps -ef | grep 270064 | grep -v grep 

[root@localhost pg_wal]# ps -ef | grep 270061 | grep -v grep 
postgres  270061  270059  0 10:57 ?        00:00:00 postgres: checkpointer 
[root@localhost pg_wal]# 
[root@localhost pg_wal]# 
[root@localhost pg_wal]# ps -ef | grep 270064 | grep -v grep 
postgres  270064  270059  0 10:57 ?        00:00:00 postgres: walwriter 

发现一个是 checkpointer 进程, 一个是 walwriter 进程, 这两个进程一个是刷脏,并归档 wal, 一个是 将 wal buffer 中的数据同步到 wal 文件中
说明正在写当前 wal 文件

检查 000000010000000800000075

从名字来看, 该 wal 文件是最小的文件(一般也是最老的 wal 文件)

[root@localhost pg_wal]# ll |head
total 9191428
-rw------- 1 postgres postgres 16777216 Dec 31 11:17 000000010000000800000075
-rw------- 1 postgres postgres 16777216 Dec 16 10:48 000000010000000800000076
-rw------- 1 postgres postgres 16777216 Dec 16 10:45 000000010000000800000077
-rw------- 1 postgres postgres 16777216 Dec 16 10:46 000000010000000800000078
-rw------- 1 postgres postgres 16777216 Dec 16 10:48 000000010000000800000079
-rw------- 1 postgres postgres 16777216 Dec 16 10:48 00000001000000080000007A
-rw------- 1 postgres postgres 16777216 Dec 16 10:45 00000001000000080000007B
-rw------- 1 postgres postgres 16777216 Dec 16 10:45 00000001000000080000007C
-rw------- 1 postgres postgres 16777216 Dec 16 10:48 00000001000000080000007D
[root@localhost pg_wal]# 
[root@localhost pg_wal]# 
[root@localhost pg_wal]# du -sh .
8.8G	.
[root@localhost pg_wal]# ll | tail
-rw------- 1 postgres postgres 16777216 Dec 16 10:49 000000010000000A0000009D
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A0000009E
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A0000009F
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A000000A0
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A000000A1
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A000000A2
-rw------- 1 postgres postgres 16777216 Dec 16 10:53 000000010000000A000000A3
-rw------- 1 postgres postgres 16777216 Dec 16 10:52 000000010000000A000000A4
-rw------- 1 postgres postgres 16777216 Dec 16 10:50 000000010000000A000000A5
drwx------ 2 postgres postgres     4096 Nov 18 20:33 archive_status

但实际观察发现该文件 并不最老的文件, 此时就联想到 PG 12 引入的 wal_recycle 配置, 检查之后, 发现该配置确实是开的

[root@localhost pg_wal]# psql -Upostgres
psql (15.6)
Type "help" for help.

postgres=# show wal_recycle ;
 wal_recycle 
-------------
 on
(1 row)

pg_archivecleanup 能删除老的 WAL 吗

很容易想到, 通过 pg_archivecleanup 来清理老的 WAL 日志
但是在 wal_recycle=on 的环境中，不能通过 pg_archivecleanup 来清理 WAL 文件，因为启用了回收机制后，WAL 文件并不是简单地按文件名顺序删除，而是会被回收并重新利用。

为什么 wal_recycle=on 影响清理？

• WAL 文件回收： 当 wal_recycle=on 时，PostgreSQL 会将不再需要的旧 WAL 文件的空间回收并重新利用。回收的文件会被重命名，并在需要时重新使用。因此，pg_archivecleanup 在这种情况下无法直接删除这些文件。

• 文件被回收利用： 文件名顺序不再准确表示文件是否可以删除，因为文件会被回收并重新命名，可能在之后再次被写入。因此，手动清理这些文件有可能会导致不一致的状态。

如何处理 wal_recycle=on 下的 WAL 文件管理？

1. 调整 max_wal_size 和 checkpoint_timeout

通过调整这些参数，可以更好地管理 WAL 文件的使用和回收：

• max_wal_size：控制 WAL 日志的最大总大小。
• checkpoint_timeout：控制触发检查点的时间间隔。通过减少该值，确保更频繁的检查点，以便清理不再需要的 WAL 文件。
• checkpoint_completion_target：控制检查点完成的目标，以避免突然增加的 WAL 文件量。

2. 强制检查点

执行 CHECKPOINT 命令来强制进行检查点，强制清理不再需要的 WAL 文件：

sql

复制代码

CHECKPOINT;

这样做有时可以帮助将不再需要的 WAL 文件释放，但在 wal_recycle=on 的情况下，它可能不会直接删除 WAL 文件，只会释放空间以便重新利用。

3. 检查长事务或复制槽

确保没有长时间运行的事务或复制槽依赖于这些文件。检查这些事务和复制槽，确保 WAL 文件不会被意外保留。

4. 使用 pg_waldump 查看 WAL 文件内容

pg_waldump 是 PostgreSQL 提供的一个工具，可以用于查看 WAL 文件的内容，帮助你了解当前哪些文件是活跃的，哪些文件可以回收。

最终方法

1. 修改 wal_recycle 为 off
2. 重启数据库
3. 查看 pg_wal 下面文件个数, 快 500个
4. 循环checkpoint 与 切换 wal 文件

for ((i=0;i<500;i++))
do
psql -U postgres <<EOF
checkpoint;
select pg_switch_wal();
EOF
done

5. 最终将 wal 文件个数降下来了, pg_wal 空间占用也很小了