Search Postgresql Archives

pg_xlog Concern

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux