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.Hannah Vernon– Hannah Vernon ♦2015-09-15 21:30:30 +00:00Commented Sep 15, 2015 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– Marcos2015-09-15 21:44:52 +00:00Commented Sep 15, 2015 at 21:44
- Linux, Windows, some other OS ?Hannah Vernon– Hannah Vernon ♦2015-09-15 21:46:13 +00:00Commented Sep 15, 2015 at 21:46
- Sorry, OS is Windows.Marcos– Marcos2015-09-15 21:50:13 +00:00Commented Sep 15, 2015 at 21:50
- 1So, you just want to "zip" them up, then?Hannah Vernon– Hannah Vernon ♦2015-09-15 21:53:42 +00:00Commented Sep 15, 2015 at 21:53
| Show 1 more comment
1 Answer
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_commandCraig Ringer– Craig Ringer2015-09-16 21:43:42 +00:00Commented Sep 16, 2015 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? Aboutrestore_commandI'm building solution. Thanks!Marcos– Marcos2015-09-17 14:45:30 +00:00Commented Sep 17, 2015 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– Craig Ringer2015-09-17 22:30:13 +00:00Commented Sep 17, 2015 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
%pwith 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– DylanYoung2018-02-14 21:44:32 +00:00Commented Feb 14, 2018 at 21:44 - I wouldn't want to for reasons of rotation and ease of incremental copying/archiving myself.Craig Ringer– Craig Ringer2018-02-19 04:12:07 +00:00Commented Feb 19, 2018 at 4:12