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 |
|
|
