I'm setting up a backup strategy based on WAL files archiving and transmission via rsync.
As
- DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the beginning
- I'm planning to backup remotely once a day
I wonder if I should decrease WAL file size to decrease storage consumption, because from my understanding Postgres switches to another WAL file and archives the current one once it reaches 16MB
size, which in my case could normally take more than one day (maybe more than one week); in this case I would end up loosing the small number of operations happened in between. Alternatively I could force WAL file switch via select pg_switch_xlog()
(which in any case I will use to backup once a day) but would end up having lots of 16MB
files where probably 1MB
files would be more than enough.
Am I missing something? If not how can I reduce WAL file size?
I've also been suggested to use streaming replication but I must use a NAS as my backup device and don't know if it allows such strategy.
Best Answer
If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use
pg_clearxlogtail
utility to zero the end of any WAL segment as part of your archive script before yougzip
your archive files.In 9.4 and above you just need to
gzip
the file, with no need forpg_clearxlogtail
.The change was made commit 9a20a9b, but it's only in tag
REL9_4_BETA1
, so the change will first appear in 9.4:BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.
Update: As Jeff noted,
pg_clearxlogtail
hasn't been updated for 9.3. The incompatibility is very simple though; the definition ofXLogRecPtr
changed fromto
when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.
The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.