【PgSQL日常管理】pg_wal日志清理

开启归档模式

[postgres@host pg_wal]$cd /data/pgsql_5432/data/pg_wal/
[postgres@host pg_wal]$ ll |grep backup
-rw------- 1 postgres postgres 353 May 6 10:20 00000001000004F7000000EA.00000028.backup

wal_log 里面.backup 的标记，说明这个WAL log 以前的数据已经备份了，如果清理这个WAL LOG 之前的log 是安全的。

例如：在pg_rman的备份路径下， file_arclog.txt记录了备份wal的信息

$ pwd
/pgshare/rman/20210914/201147
$ cat file_arclog.txt |tail -5
0000000100000052000000B0 f 84311 1867921062 0600 2021-09-14 20:08:14
0000000100000052000000B1 f 118592 2113401927 0600 2021-09-14 20:10:15
0000000100000052000000B2 f 16409 3805329391 0600 2021-09-14 20:11:47
0000000100000052000000B3 f 16499 2358477753 0600 2021-09-14 20:11:53
0000000100000052000000B3.00000060.backup f 189 435977242 0600 2021-09-14 20:11:53

清理掉已经归档的日志。

pg_archivecleanup  $PGDATA/pg_wal/   0000000100000646000000D1.00000028.backup中间有空格

[postgres@host pg_wal]$ pg_archivecleanup /data/pgsql_5432/data/pg_wal/   00000001000004F7000000EA.00000028.backup
[postgres@host pg_wal]$ ll
total 181392
-rw------- 1 postgres postgres 16777216 May 6 10:11 00000001000004F7000000EA
-rw------- 1 postgres postgres 353 May 6 10:20 00000001000004F7000000EA.00000028.backup
-rw------- 1 postgres postgres 16777216 May 6 10:12 00000001000004F7000000EB
-rw------- 1 postgres postgres 16777216 May 6 10:13 00000001000004F7000000EC
-rw------- 1 postgres postgres 16777216 May 6 10:14 00000001000004F7000000ED
-rw------- 1 postgres postgres 16777216 May 6 10:15 00000001000004F7000000EE
-rw------- 1 postgres postgres 16777216 May 6 10:16 00000001000004F7000000EF
-rw------- 1 postgres postgres 16777216 May 6 10:17 00000001000004F7000000F0
-rw------- 1 postgres postgres 16777216 May 6 10:18 00000001000004F7000000F1
-rw------- 1 postgres postgres 16777216 May 6 10:19 00000001000004F7000000F2
-rw------- 1 postgres postgres 16777216 May 6 10:20 00000001000004F7000000F3
-rw------- 1 postgres postgres 16777216 May 6 10:21 00000001000004F7000000F4

根据checkpoint对wal日志进行清理：

pg_controldata

$ pg_controldata|grep checkpoint
Latest checkpoint location: 7A2/B6006508
Latest checkpoint's REDO location: 7A2/AF033120
Latest checkpoint's REDO WAL file: 00000001000007A2000000AF
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:24197057
Latest checkpoint's NextOID: 377294
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 479
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 24197057
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:4068836
Latest checkpoint's newestCommitTsXid:24197056
Time of latest checkpoint: Fri 28 Jan 2022 04:00:03 PM CST

可以采用系统命令rm清理，也可以使用pg_archivecleanup清理

$ pg_archivecleanup /data/pgsql_5432/data/pg_wal/ 00000001000007A2000000AF

这里pg_wal目录下wal日志几百G ，所以对wal日志的配置进行调整：

和WAL日志数量相关的几个参数：
wal_keep_segments
checkpoint_completion_target
max_wal_size
min_wal_size

postgres=# show wal_keep_segments;
 wal_keep_segments 
-------------------
 1024
(1 row)

postgres=# show checkpoint_completion_target;
 checkpoint_completion_target 
------------------------------
 0.1
(1 row)

postgres=# show max_wal_size;
 max_wal_size 
--------------
 64GB
(1 row)

postgres=# show min_wal_size;
 min_wal_size 
--------------
 4GB
(1 row)


修改为：
postgres=# show max_wal_size;
 max_wal_size 
--------------
 1GB
(1 row)

postgres=# show min_wal_size;
 min_wal_size 
--------------
 80MB
(1 row)

reload生效
$ pg_ctl reload -D /data/pgsql/data/