2

I'm setting up a backup strategy based on WAL files archiving and transmission via rsync.

As

  1. DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the beginning
  2. 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.

migrated from stackoverflow.com May 28 '14 at 13:45

This question came from our site for professional and enthusiast programmers.

  • I don't know off the top of my head, but it looks like this documentation will help greatly if you spend some time reading it. – Sam May 27 '14 at 22:08
4

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 pg_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:

commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
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 */
} XLogRecPtr;

to

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.

  • Are you sure that is in 9.3? I thought it was new in 9.4 – jjanes May 28 '14 at 6:23
  • Not at all sure, I was just checking that. Of course, the original poster didn't specify their version and could just as easily be on 9.1 or 8.3... – Craig Ringer May 28 '14 at 6:24
  • Updated to reflect version. It's one of the problems with always running master while doing dev work - features that aren't out to the public yet feel old and familiar. – Craig Ringer May 28 '14 at 6:29
  • 1
    @mettjus unfortunately pg_clearxlogtail was never updated to work in 9.3. I think the change to make it was work is pretty trivial, but no one ever bothered to do it on a program that is soon to be obsolete. – jjanes May 28 '14 at 14:44
  • 1
    @mettjus I just stayed on 9.2, because the improvements in 9.3 were not very important to me. You can try compressing the files without doing anything else and see how that goes. If you never did any bulk operations in the database, the tails of the files will be zero anyway. – jjanes May 28 '14 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.