BUG #14305: How to reduce WAL files in Point in time recovery

From: amee(dot)sankhesara(at)quipment(dot)nl
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14305: How to reduce WAL files in Point in time recovery
Date: 2016-09-01 14:23:47
Message-ID: 20160901142347.15674.11418@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14305
Logged by: Amee Sankhesara
Email address: amee(dot)sankhesara(at)quipment(dot)nl
PostgreSQL version: 9.4.8
Operating system: Windows Server 2008
Description:

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 ?

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.

So could you please provide me guidelines to get this problem solved ?

Thanks and Regards,
Amee

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-09-01 14:27:57 Re: BUG #14295: Hot standby crash during tsvector rebuild
Previous Message Spencer Thomason 2016-09-01 14:22:33 Re: BUG #14295: Hot standby crash during tsvector rebuild