PostgreSQL的pg_xlog文件数计算和在线清理(三)
postgres 67108864 Jul 14 22:45 000000010000000300000025
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000026
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 000000010000000300000027
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000028
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 000000010000000300000029
-rw-------. 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002A
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002B
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002C
-rw------- 1 postgres postgres 67108864 Jul 14 22:56 00000001000000030000002D
drwx------. 2 postgres postgres 4096 Jun 12 23:19 archive_status
[postgres@localhost pg_xlog]$ ll|wc -l
64
2.模拟pg_xlog文件清理
这类文件对DB是很重要的，不能手工直接清理。每个文件64MB，是
源码编译时--with-wal-segsize时指定的，或者登陆查看
postgres=# show wal_segment_size ;
wal_segment_size
------------------
64MB
(1 row)
要收缩这个事务日志空间，减少事务日志数，可以调整参数 checkpoint_segments，本次调为3
postgres=# show checkpoint_segments ;
checkpoint_segments
---------------------
3
(1 row)
postgres=# checkpoint;
CHECKPOINT
xlog文件有可能不是立即减少的，如果想模拟立即减少，可以模拟
数据库频繁checkpoint，如重新做上述的大
数据库加载或者更新操作，此时去看pg_xlog的文件数，能看到在减少了，开启两个窗口操作:
postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000
postgres=# insert into tbl_kenyon select generate_series(1,2000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 2000000
[postgres@localhost pg_xlog]$ ll|wc -l
62
[postgres@localhost pg_xlog]$ ll|wc -l
49
[postgres@localhost pg_xlog]$ ll|wc -l
46
[postgres@localhost pg_xlog]$ ll|wc -l
46
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
40
[postgres@localhost pg_xlog]$ ll|wc -l
40
[postgres@localhost pg_xlog]$ ll|wc -l
31
[postgres@localhost pg_xlog]$ ll|wc -l
25
后续发生的checkpoint，将会逐步收缩pg_xlog的文件数量，直到满足条件为止。
3.其他
如果是流复制，还有wal_keep_segments这个参数也要考虑一下。后续。
4.说明
checkpoint_segments (integer)
Maximum number of log file segments between automatic WAL checkpoints (each segment
is normally 16 megabytes). The default is three segments. Increasing this parameter can increase
the amount of time needed for crash recovery. This parameter can only be set in the
postgresql.conf file or on the server command line.