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

On Fri, Sep 2, 2016 at 2:58 PM, Amee Sankhesara - Quipment India <
amee(dot)sankhesara(at)quipment(dot)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