PostgreSQL的pg_xlog文件数计算和在线清理
postgresql的pg_xlog是记录数据库事务信息用的，类似oracle的redo信息，也叫wal日志(write ahead log)，就是在写数据到磁盘里成为固定数据之前，先写入到日志里，然后一定条件下触发调用fsync()将此数据刷到磁盘。好处不多说，下面主要看看postgresql通常会保留多少个pg_xlog文件，以及如何在线清理。
一、环境及参数
环境:
postgresql 9.2.4(单台，未做流复制)
vmware 6.0
centos 6.2
参数:
postgres=# show checkpoint_segments ; --设置相对大一点,默认太小，防止频繁checkpoint
checkpoint_segments
---------------------
30
(1 row)
postgres=# show checkpoint_timeout ;
checkpoint_timeout
--------------------
5min
(1 row)
postgres=# show checkpoint_completion_target ;
checkpoint_completion_target
------------------------------
0.5
(1 row)
postgres=# show wal_keep_segments ; --未开启流复制预留xlog参数
wal_keep_segments
-------------------
0
(1 row)
postgres=# show archive_mode ; --关闭归档
archive_mode
--------------
off
(1 row)
理论上合理的pg_xlog一般在(2+checkpoint_completion_target)*checkpoint_segment+1左右浮动，但是高并发环境下如果checkpoint_segment设置比较大，且checkpoint较少被触发的时候，pg_xlog下会存有很多的事务日志，严重会耗掉磁盘存储，所以设置checkpoint_segment的大小要视磁盘容量和每个pg_xlog文件的大小来看，有些时候需要手工调整参数来收缩。
二、测试过程:
1.模拟pg_xlog文件增长
模拟大数据量载入,开启多个窗口插入数据,每个窗口插入1000W数据,可以看到pg_xlog增长很多，原来只有8个文件
postgres=# create table tbl_kenyon(id int,cname varchar(50),remark text);
CREATE TABLE
postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5(generate_series('kenyon good boy');
INSERT 0 10000000
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,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000
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,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000
postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000
此时检查pg_xlog下面的文件数量
[postgres@localhost pg_xlog]$ ll
total 4063240
-rw------- 1 postgres postgres 67108864 Jul 14 23:05 00000001000000020000002F
-rw------- 1 postgres postgres 67108864 Jul 14 23:13 000000010000000200000030
-rw------- 1 postgres postgres 67108864 Jul 14 23:18 000000010000000200000031
-rw------- 1 postgres postgres 67108864 Jul 14 23:25 000000010000000200000032
-rw------- 1 postgres postgres 67108864 Jul 14 22:42 000000010000000200000033
-rw------- 1 postgres postgres 67108864 Jul 14 22:42 000000010000000200000034
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000035
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000036
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 000000010000000200000037
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000038
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000039
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003A
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 00000001000000020000003B
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003C
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 00000001000000020000003D
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003E
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000000
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000001
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000002
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000003
-rw-------. 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000004
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000005
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000006
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000007
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000008
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000009
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000000A
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 00000001000000030000000B
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000C
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000000D
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000E
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000F
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000010
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000011
-rw------- 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000012
-rw------- 1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000013
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000014
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000015
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000016
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000017
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000018
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000019
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001A
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001B
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001C
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001D
-rw------- 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001E
-rw------- 1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001F
-rw----
