【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

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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/