pg_xlog Concern

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,

*Sachin Srivastava*
Assistant Technical Lead(Oracle/PostgreSQL) | TSG
*Cyient* | www.cyient.com