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

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMwNQpMb2dnZWQgYnk6ICAg
ICAgICAgIEFtZWUgU2Fua2hlc2FyYQpFbWFpbCBhZGRyZXNzOiAgICAgIGFt
ZWUuc2Fua2hlc2FyYUBxdWlwbWVudC5ubApQb3N0Z3JlU1FMIHZlcnNpb246
IDkuNC44Ck9wZXJhdGluZyBzeXN0ZW06ICAgV2luZG93cyBTZXJ2ZXIgMjAw
OApEZXNjcmlwdGlvbjogICAgICAgIAoKSSBoYXZlIHNldHVwIFBJVFIgaW4g
UG9zdGdyZVNxbC4gSSBhbSB0YWtpbmcgYmFzZSBiYWNrdXAgYXQgZXZlcnkg
c3BlY2lmaWMKaW50ZXJ2YWwgYW5kIGFsc28ga2VwdCBXQUwgZmlsZXMgb2Yg
c2l6ZSAxNiBNQiBlYWNoLiANCg0KTm93IHRoZSBzaXR1YXRpb24gaXMgdGhh
dCBldmVuIHRoZXJlIGlzIG5vIGFueSBtYWpvciBjaGFuZ2UgaW4gZGF0YWJh
c2UsIGl0CnN1ZGRlbmx5IHN0YXJ0ZWQgY3JlYXRpbmcgdG9vIG1hbnkgV0FM
IGZpbGVzLg0KDQpJIGhhdmUgZ2F0aGVyZWQgc3RhdGlzdGljcyB3aXRoIGNv
dW50IG9mIFdBTCBmaWxlcyBjcmVhdGVkIG9uIHNwZWNpZmljIGRhdGVzCmFz
IHNob3duIGJlbG93OiANCg0KRGF0ZSAgICAgICB8IFdBTCBmaWxlIGNvdW50
DQotLS0tLS0tLS0tLXwgLS0tLS0tLS0tLS0tLQ0KMjAxNi0wOC0zMSB8ICAx
NTY5IA0KMjAxNi0wOC0zMCB8ICAzMDMxDQoyMDE2LTA4LTI5IHwgIDI2NjQN
CjIwMTYtMDgtMjggfCAgMTI1MQ0KMjAxNi0wOC0yNyB8ICAxMjMxDQoyMDE2
LTA4LTI2IHwgIDE5NDYNCjIwMTYtMDgtMjUgfCAgMTg1MA0KMjAxNi0wOC0y
NCB8ICAxNjY2DQoyMDE2LTA4LTIzIHwgIDE1NjINCjIwMTYtMDgtMjIgfCAg
MTUyNQ0KMjAxNi0wOC0yMSB8ICAgNzY1DQoyMDE2LTA4LTIwIHwgICA3NjEN
CjIwMTYtMDgtMTkgfCAgMTE4MA0KMjAxNi0wOC0xOCB8ICAxMDc3DQoyMDE2
LTA4LTE3IHwgIDEwNjQNCjIwMTYtMDgtMTYgfCAgIDgzMg0KMjAxNi0wOC0x
NSB8ICAgNzMyDQoyMDE2LTA4LTE0IHwgICA0MDINCjIwMTYtMDgtMTMgfCAg
IDY5MQ0KMjAxNi0wOC0xMiB8ICAxOTkxDQoyMDE2LTA4LTExIHwgICA0NjUN
Cg0KaGVyZSB3ZSBhcmUgZXhwZWN0aW5nIG5vcm1hbCBjb3VudCB0byBiZSBi
ZXR3ZWVuIDYwMCB0byA4MDAgYWNjb3JkaW5nIHRvIG91cgpkYXRhYmFzZSB0
cmFuc2FjdGlvbnMuIEJ1dCBpbiBhYm92ZSBzdGF0aXN0aWNzIHlvdSBjYW4g
c2VlIG1ham9yIGZsdWN0dWF0aW9uCmluIGZpbGUgY291bnRzLiBJIGRvIG5v
dCB1bmRlcnN0YW5kIHdoZXJlIHRoZSBwcm9ibGVtIGlzIGFuZCBob3cgY2Fu
IEkgZmluZAp0aGUgcm9vdCBjYXVzZSBvZiB0aGUgcHJvYmxlbSA/IA0KDQpB
bHNvIEkgd291bGQgbGlrZSB0byBpbmZvcm0gdGhhdCBmZXcgZGF5cyBiYWNr
IEkgaGFkIHBlcmZvcm1lZCBmdWxsIHZhY3V1bQpvbiAyIG9yIDMgdGFibGVz
IHdoaWNoIHdlcmUgaGF2aW5nIHNpemUgb2YgMyB0byA0IEdCLiBCdXQgSSBk
byBub3QgZ3Vlc3MgaXQKaXMgYmVjYXVzZSBvZiB0aGlzIHZhY3V1bS4NCg0K
U28gY291bGQgeW91IHBsZWFzZSBwcm92aWRlIG1lIGd1aWRlbGluZXMgdG8g
Z2V0IHRoaXMgcHJvYmxlbSBzb2x2ZWQgPyANCg0KDQpUaGFua3MgYW5kIFJl
Z2FyZHMsDQpBbWVlCgo=

Hi



2016-09-01 16:23 GMT+02:00 <amee.sankhesara@quipment.nl>:

> The following bug has been logged on the website:
>
> Bug reference:      14305
> Logged by:          Amee Sankhesara
> Email address:      amee.sankhesara@quipment.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 ?
>
>
This is not a bug. Please, ask on related pgsql-general@postgresql.org
mailing list.

Regards

Pavel Stehule


>
> Thanks and Regards,
> Amee
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>