当前位置：首页 > article >正文

postgresql清理wal日志

article 2025/1/22 20:32:36

一、查看最新的位点检查点

pg_controldata data路径

pg_control version number:            923
Catalog version number:               201611171
Database system identifier:           3423010635499191
Database cluster state:               in production
pg_control last modified:             Mon 20 Jan 2025 05:40:46 PM PST
Latest checkpoint location:           0/1C036630
Prior checkpoint location:            0/1C036490
Latest checkpoint's REDO location:    0/1C0365B0
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID:          585903
Latest checkpoint's NextOID:          40971
Latest checkpoint's NextMultiXactId:  2
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        12665
Latest checkpoint's oldestXID's DB:   15631
Latest checkpoint's oldestActiveXID:  585903
Latest checkpoint's remove lsn:       0/C
Time of latest checkpoint:            Mon 20 Jan 2025 05:39:22 PM PST
Minimum recovery ending location:     0/1C036990
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            logical
Current max_connections setting:      550
Current max_prepared_xacts setting:   800
Current max_locks_per_xact setting:   256
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Database system TimeLine:             197

这个就是当前的检查点
Latest checkpoint location: 0/1C036630

二、通过检查点查看wal日志对应的文件

select pg_xlogfile_name('0/1C036630');
     pg_xlogfile_name
--------------------------
 00000001000000000000001C

日志文件命名格式 000000010000000000000011
WAL日志三个组成部分：
第一部分，叫做时间线，是从1开始递增地数字(类似ORACLE 的SCN的wrap), 当低位满时，前一段加1.
第二部分，叫做LogId，是LSN的高32位(从0开始)
第三部分，叫做LogSeg，是LSN的低32位除以WAL文件的大小，WAL文件的大小默认是16M