pg_xlog Concern - Mailing list pgsql-general

Hi,

I have done below changes in my postgresql.conf.

Checkpoint_segments= 200

and

checkpoint_timeout= 5min

I am also doing archiving and below changes in my postgresql.conf.

Wal_level=archive

archive_command= cp %p /dbkup/momacpp_213_live/%f

archive_mode=on

Achieving is going smoothly in /dbkup/momacpp_213_live folder.

But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were approx. 30000. Due to archiving pg_xlog folder size is decreasing now but it’s taking one week to come in normal size.

I have 2 Question:

First: When I have given checkpoint segments=200 (As per my knowledge WAL file should be generated 200*2+3=403 only) but why it’s generating too much file. MY each WAL file is 16 MB.

Second: Why pg_xlog size is increasing too much it should be only (403*16 MB = 6448 MB) and if Production team is not entering data in bulk and if normal production is going then it’s size remain same as per logic.

How I handle this case (pg_xlog folder size) when Production people entering the data in bulk, kindly suggest.  I am missing something in my postgresql.conf and somewhere else.

Note: On daily basis Production team is entering the data in bulk (approx. 30-50 GB) in the database and there are only 2 databases on this server.

Regards,