Re: pg_xlog Concern

On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>
wrote:

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

What do you see in the "archive_status" directory ?
Other way around could be, if you have log_checkpoints parameter set to
"on", then checkpoints information will be logged into the postgresql
logfile. This will help you understand how checkpoints are behaving.

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

Do you mean to say, pg_xlog is not getting cleared forever ? or is it
getting cleared periodically and you are uncertain about the behaviour ?

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

What is the *archive_timeout* value you have ?

Regards,
Venkata Balaji N

Fujitsu Australia