On Fri, Sep 2, 2016 at 2:58 PM, Amee Sankhesara - Quipment India < amee.sankhes...@quipment.nl> wrote:
> Hi, > > > > I have setup PITR in PostgreSQL. I am taking base backup at every specific > interval and also kept WAL files of size 16 MB each. > > > > Now the situation is that even there is no any major change in database, > it suddenly started creating too many WAL files. > > > > I have gathered statistics with count of WAL files created on specific > dates as shown below: > > > > Date | WAL file count > > -----------| ------------- > > 2016-08-31 | 1569 > > 2016-08-30 | 3031 > > 2016-08-29 | 2664 > > 2016-08-28 | 1251 > > 2016-08-27 | 1231 > > 2016-08-26 | 1946 > > 2016-08-25 | 1850 > > 2016-08-24 | 1666 > > 2016-08-23 | 1562 > > 2016-08-22 | 1525 > > 2016-08-21 | 765 > > 2016-08-20 | 761 > > 2016-08-19 | 1180 > > 2016-08-18 | 1077 > > 2016-08-17 | 1064 > > 2016-08-16 | 832 > > 2016-08-15 | 732 > > 2016-08-14 | 402 > > 2016-08-13 | 691 > > 2016-08-12 | 1991 > > 2016-08-11 | 465 > > > > here we are expecting normal count to be between 600 to 800 according to > our database transactions. But in above statistics you can see major > fluctuation in file counts. I do not understand where the problem is and > how can I find the root cause of the problem ? > Why do you think this is a problem ? PostgreSQL generates WAL for a reason. If there are no much transactions on the database, then, it could be because of maintenance operations like VACUUM. > > > Also I would like to inform that few days back I had performed full vacuum > on 2 or 3 tables which were having size of 3 to 4 GB. But I do not guess it > is because of this vacuum. > Yes, more WALs are generated when you perform VACUUM. > > > So could you please provide me guidelines to get this problem solved ? > If you are really concerned about huge WAL generation, then, consider analyzing your Application and see if you can reduce any transactions to avoid WAL generation. Regards, Venkata B N Fujitsu Australia
