Re: How to reduce WAL files in Point in time recovery

[Venkata B Nagothi <nag1010@xxxxxxxxx>]

On Fri, Sep 2, 2016 at 2:58 PM, Amee Sankhesara - Quipment India <amee.sankhesara@xxxxxxxxxxx> 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