[[toc]]

适用范围

Postgresql 10+

问题概述

1. WAL 段日志维护

1. 随着 Postgresql 数据库运行，会生成越来越多的WAL段文件，在 $PGATA/pg_wal 目录下的WAL段文件是如何维护呢

解决方案

1. 谁在对WAL段日志进行维护

postgres: walwriter 进程将事务日志写入 WAL 段日志文件。
postgres: checkpointer 进程对WAL段日志进行维护，不断清理过旧的 WAL 段日志文件，生成新 WAL 段日志文件，保证 WAL 段日志文件占用存储空间不会无限膨胀。

2. 影响WAL段日志文件的参数

min_wal_size:

WAL段日志文件，使用磁盘存储最低值。当使用率低于此设置，旧的 WAL 文件总是在检查点被回收以供将来使用（如何理解，用示例说明），而不是被删除。此机制确保保留足够的 WAL 空间来处理 WAL 使用中的峰值，例如在运行大型批处理作业时。默认值为 80 MB。

示例：说明 WAL 日志文件回收利用机制

postgres@[local]:1931=#13165 select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 00000001000000060000008A    
(1 row)

postgres@[local]:1931=#13165 \! ls -l  $PGDATA/pg_wal/                    
total 81924
-rw------- 1 postgres postgres 16777216 Sep  5 12:32 00000001000000060000008A <<
-rw------- 1 postgres postgres 16777216 Sep  5 12:15 00000001000000060000008B
-rw------- 1 postgres postgres 16777216 Sep  5 12:15 00000001000000060000008C
-rw------- 1 postgres postgres 16777216 Sep  5 12:15 00000001000000060000008D

drwx------ 2 postgres postgres       96 Sep  4 21:06 archive_status
复制

从示例中可知，当前 WAL 段日志为 00000001000000060000008A ，但实际产生了 8B,8C,BD,都比8A要大,但文件最后修改的时间戳反而要旧。实际上Postgres处理的方式是先创建一个硬链接文件指向旧文件，然后再删除旧文件的方法。
查看 00000001000000060000008D 内容：

[postgres@pg14 pg_wal]$ pg_waldump 00000001000000060000008D
pg_waldump: fatal: could not find a valid record after 6/8D000000
复制

报错说明，这个文件是由旧文件重命名产生。那这个文件由哪个文件重命名得来呢？使用系统命名hexdump查看二进制内容：

[postgres@pg14 pg_wal]$ hexdump -n 16 00000001000000060000008D
0000000 d10d 0007 0001 0000 0000 0800 0006 0000
0000010           ---- ---- ---- ---- ----
                  时间线        LogSeg  LogId       
复制

根据上面的输出，可以知道 00000001000000060000008D 原文件名为 000000010000000600000008 ，再执行pg_waldum 不再报错。

cp 00000001000000060000008D /tmp/000000010000000600000008
[postgres@pg14 pg_wal]$ pg_waldump /tmp/000000010000000600000008|more
rmgr: Heap        len (rec/tot):     54/    54, tx:    1651818, lsn: 6/08000048, prev 6/07FFFFE8, desc: DELETE off 133 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16405/625149 blk 38275
rmgr: Heap        len (rec/tot):     54/    54, tx:    1651818, lsn: 6/08000080, prev 6/08000048, desc: DELETE off 134 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16405/625149 blk 38275
rmgr: Heap        len (rec/tot):     54/    54, tx:    1651818, lsn: 6/080000B8, prev 6/08000080, desc: DELETE off 135 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16405/625149 blk 38275
rmgr: Heap        len (rec/tot):     54/    54, tx:    1651818, lsn: 6/080000F0, prev 6/080000B8, desc: DELETE off 136 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16405/625149 blk 38275
复制

max_wal_size:

在自动检查点期间让 WAL 增长的最大大小。这是一个软限制；WAL 大小 在特殊情况下可能会超出max_wal_size，例如重负载、失败archive_command或高wal_keep_size设置。默认值为 1 GB。增加此参数可以增加崩溃恢复所需的时间。
下面用示例说明max_wal_size 不是硬限制：

postgres@[local]:1931=#14023 show max_wal_size;     
 max_wal_size 
--------------
 2GB
2GB/16MB=128
[postgres@pg14 pg_wal]$ ls -ltr|grep  00000001|wc -l
129
复制

checkpoint_timeout：

自动 WAL 检查点之间的最长时间。如果指定此值没有单位，则以秒为单位。有效范围在 30 秒到 1 天之间。默认值为五分钟 ( 5min)。增加此参数可以增加崩溃恢复所需的时。

checkpoint_completion_target：

在日志检查点时，为了防止出现I/O尖刺，使用参数checkpoint_completion_target 控制检查点完成的时间，作为检查点之间总时间的一部分。默认值为 0.9，它将检查点分布在几乎所有可用的时间间隔中，提供相当一致的 I/O 负载，同时也为检查点完成开销留出一些时间。不建议减小此参数，因为它会导致检查点更快地完成。这会导致检查点期间的 I/O 速率更高，然后在检查点完成和下一个计划检查点之间的 I/O 时间段较少。假如checkpoint_timeout设置是5分钟，而wal生成了10G，那么设置成0.9就允许我在4.5分钟内完成checkpoint，调大这个值就可以降低checkpoint对性能的影响，但是万一数据库出现故障，那么这个值设置越大数据就越危险。

3. 导致checkpoint产生的因素

• 达到检查点配置时间(checkpoint_timeout)
• 达到 max_wal_size
• 数据库正常shutdown
• 手动执行CHECKPOINT命令
• 执行需要检查点的命令（例如pg_start_backup 或pg_ctl stop|restart等等）

4. WAL 日志清理

WAL 日志自动清理

在参数文件中配置参数：
例如：

archive_cleanup_command = 'pg_archivecleanup /pgdata/14/data/pg_wal %r'
或
alter system set archive_cleanup_command = 'pg_archivecleanup /pgdata/14/data/pg_wal %r'

复制

WAL 日志手动清理

通过手动执行命令pg_archivecleanup ，进行清理
示例：

# 获取最近检查点的WAL段文件
$ pg_controldata 
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7073645365709360220
Database cluster state:               in production
pg_control last modified:             Mon 05 Sep 2022 12:58:53 PM CST
Latest checkpoint location:           6/D7132FC8
Latest checkpoint's REDO location:    6/CA42A648
Latest checkpoint's REDO WAL file:    0000000100000006000000CA   <<<<
Latest checkpoint's TimeLineID:       1

# 执行命令 pg_archivecleanup 
pg_archivecleanup /pgdata/14/data/pg_wal 0000000100000006000000CA

复制

5. 查询WAL段文件生成频次的脚本

脚本中需手动写入 WAL 段文件所在目录，此示例中为 /pgdata/14/data/pg_wal ，可按实际目录替换。

with wal as 
( select t1.file, t1.file_ls, (pg_stat_file(t1.file)).size as size, (pg_stat_file(t1.file)).access as access, (pg_stat_file(t1.file)).modification as last_update_time, (pg_stat_file(t1.file)).change as change, (pg_stat_file(t1.file)).creation as creation, (pg_stat_file(t1.file)).isdir as isdir from (select dir||'/'||pg_ls_dir(t0.dir) as file, pg_ls_dir(t0.dir) as file_ls from ( select '/pgdata/14/data/pg_wal'::text as dir ) t0 ) t1 where 1=1 order by (pg_stat_file(file)).modification desc ) 
select to_char(date_trunc('day',w.last_update_time),'yyyymmdd') as "day", 
sum(case when date_part('hour',w.last_update_time) >=0 and date_part('hour',w.last_update_time) <24 then 1 else 0 end) as "all", 
sum(case when date_part('hour',w.last_update_time) >=0 and date_part('hour',w.last_update_time) <1 then 1 else 0 end) as "01", 
sum(case when date_part('hour',w.last_update_time) >=1 and date_part('hour',w.last_update_time) <2 then 1 else 0 end) as "02", 
sum(case when date_part('hour',w.last_update_time) >=2 and date_part('hour',w.last_update_time) <3 then 1 else 0 end) as "03", 
sum(case when date_part('hour',w.last_update_time) >=3 and date_part('hour',w.last_update_time) <4 then 1 else 0 end) as "04", 
sum(case when date_part('hour',w.last_update_time) >=4 and date_part('hour',w.last_update_time) <5 then 1 else 0 end) as "05", 
sum(case when date_part('hour',w.last_update_time) >=5 and date_part('hour',w.last_update_time) <6 then 1 else 0 end) as "06", 
sum(case when date_part('hour',w.last_update_time) >=6 and date_part('hour',w.last_update_time) <7 then 1 else 0 end) as "07", 
sum(case when date_part('hour',w.last_update_time) >=7 and date_part('hour',w.last_update_time) <8 then 1 else 0 end) as "08", 
sum(case when date_part('hour',w.last_update_time) >=8 and date_part('hour',w.last_update_time) <9 then 1 else 0 end) as "09", 
sum(case when date_part('hour',w.last_update_time) >=9 and date_part('hour',w.last_update_time) <10 then 1 else 0 end) as "10", 
sum(case when date_part('hour',w.last_update_time) >=10 and date_part('hour',w.last_update_time) <11 then 1 else 0 end) as "11", 
sum(case when date_part('hour',w.last_update_time) >=11 and date_part('hour',w.last_update_time) <12 then 1 else 0 end) as "12", 
sum(case when date_part('hour',w.last_update_time) >=12 and date_part('hour',w.last_update_time) <13 then 1 else 0 end) as "13", 
sum(case when date_part('hour',w.last_update_time) >=13 and date_part('hour',w.last_update_time) <14 then 1 else 0 end) as "14", 
sum(case when date_part('hour',w.last_update_time) >=14 and date_part('hour',w.last_update_time) <15 then 1 else 0 end) as "15", 
sum(case when date_part('hour',w.last_update_time) >=15 and date_part('hour',w.last_update_time) <16 then 1 else 0 end) as "16", 
sum(case when date_part('hour',w.last_update_time) >=16 and date_part('hour',w.last_update_time) <17 then 1 else 0 end) as "17", 
sum(case when date_part('hour',w.last_update_time) >=17 and date_part('hour',w.last_update_time) <18 then 1 else 0 end) as "18", 
sum(case when date_part('hour',w.last_update_time) >=18 and date_part('hour',w.last_update_time) <19 then 1 else 0 end) as "19", 
sum(case when date_part('hour',w.last_update_time) >=19 and date_part('hour',w.last_update_time) <20 then 1 else 0 end) as "20", 
sum(case when date_part('hour',w.last_update_time) >=20 and date_part('hour',w.last_update_time) <21 then 1 else 0 end) as "21", 
sum(case when date_part('hour',w.last_update_time) >=21 and date_part('hour',w.last_update_time) <22 then 1 else 0 end) as "22", 
sum(case when date_part('hour',w.last_update_time) >=22 and date_part('hour',w.last_update_time) <23 then 1 else 0 end) as "23", 
sum(case when date_part('hour',w.last_update_time) >=23 and date_part('hour',w.last_update_time) <24 then 1 else 0 end) as "24" 
from wal w where 1=1 and w.file_ls not in ('archive_status') 
group by to_char(date_trunc('day',w.last_update_time),'yyyymmdd') 
order by to_char(date_trunc('day',w.last_update_time),'yyyymmdd') desc ;
复制

参考文档

https://pg-internal.vonng.com/#/ch9
https://www.postgresql.org/docs/14/runtime-config-wal.html#GUC-FULL-PAGE-WRITES