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 you
gzip your archive files.
In 9.4 and above you just need to
gzip the file, with no need for
The change was made commit 9a20a9b, but it's only in tag
REL9_4_BETA1, so the change will first appear in 9.4:
Author: Heikki Linnakangas <[email protected]>
Date: Mon Jul 8 11:23:56 2013 +0300
Improve scalability of WAL insertions.
This patch replaces WALInsertLock with a number of WAL insertion slots,
allowing multiple backends to insert WAL records to the WAL buffers
concurrently. This is particularly useful for parallel loading large amounts
of data on a system with many CPUs.
This has one user-visible change: switching to a new WAL segment with
pg_switch_xlog() now fills the remaining unused portion of the segment with
zeros. This potentially adds some overhead, but it has been a very common
practice by DBA's to clear the "tail" of the segment with an external
pg_clearxlogtail utility anyway, to make the WAL files compress better.
With this patch, it's no longer necessary to do that.
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 of
XLogRecPtr changed from
typedef struct XLogRecPtr
uint32 xlogid; /* log file #, 0 based */
uint32 xrecoff; /* byte offset of location in log file */
typedef uint64 XLogRecPtr;
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.