I like compact WAL files. Actually I do:

archive_command = 'cp "%p"C:\\%f"'

What is the best way to do it?

  • Can you add more details about what you are talking about? As it stands this is pretty unclear. – Max Vernon Sep 15 '15 at 21:30
  • I'm doing an incremental backup with wal files. I'm copying the files generated in pg_xlog in another directory. But I would like to store them compressed. Thanks @MaxVernon. – Marcos Sep 15 '15 at 21:44
  • Linux, Windows, some other OS ? – Max Vernon Sep 15 '15 at 21:46
  • Sorry, OS is Windows. – Marcos Sep 15 '15 at 21:50
  • 1
    So, you just want to "zip" them up, then? – Max Vernon Sep 15 '15 at 21:53

Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file.

On most platforms you just gzip them, e.g.

archive_command = 'gzip -c < "%p" > /archive/path/%f"'

On Windows this won't work natively due to the lack of the gzip command. You'll need to install a compression utility that's usable from the command line, since the Windows zip support isn't directly usable from the commandline.

I suggest installing 7-zip, then using it in your archive_command, with something like (untested):

archive_command = '%PROGRAMFILES%\7zip\7z.exe a "C:\\TheWALArchive\%f" "%p"'

Note that your restore_command will need a corresponding decompression step.

  • @Marcos That command doesn't make sense. See the one I wrote above and the documentation for restore_command – Craig Ringer Sep 16 '15 at 21:43
  • I use '"C:\\Program Files\\7-Zip\\7z.exe" a "archivedir\\%f" "%p"'. The files is compress. Do you think it makes sense to compress the files? About restore_command I'm building solution. Thanks! – Marcos Sep 17 '15 at 14:45
  • 1
    @Marcos Yes, compressing WAL archives makes a lot of sense. Especially if you have an archive_timeout set since then they will be mostly zeroes and compress extremely well. – Craig Ringer Sep 17 '15 at 22:30
  • @CraigRinger I'm curious. Is there any benefit/drawback to storing all the WAL files in a single 7zip archive? Note also that compression from 7z is substantially better than gzip, so it's worth considering on unix too. Also, you need to prefix %p with a . if you don't want to copy the full path to the WAL file. Incorporating these sugestions: archive_command = '%PROGRAMFILES%\7zip\7z.exe a "C:\\TheWALArchive\wal_archive" ".%p"'. Does that make sense? – DylanYoung Feb 14 '18 at 21:44
  • I wouldn't want to for reasons of rotation and ease of incremental copying/archiving myself. – Craig Ringer Feb 19 '18 at 4:12

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.