a NOOP WAL archiving command for PostgreSQL 8.3

Starting Postgres version 8.3, there is a little annoying feature (for the DBAs) in WAL archiving infrastructure of Postgres. In earlier versions you could change the archive_command on the fly to do different things at different times, or do nothing at all if you choose not to.

But 8.3 onwards, if you haven't switched on archive_mode to ON while starting the server, then you just can't change your archiving strategy while the server is running! So, I would recommend running a production database with this setting always turned ON. But wait, what do you set the archive_command to, if you do not want to archive?! The thing is, if archive_mode is ON and theres nothing in the archive_command you are likely to see the following WARNING in your log files:

WARNING: archive_mode enabled, yet archive_command is not set

And to worsen the situation, instead of recycling your transaction logs, it will keep them all because it is supposed to do archiving, but it is not able to since there's no command to execute... what happens? Your transaction log directory gets full!!!

And, if archive_command is set to something that Postgres can't execute, then you have a BIG problem.

So, here is a very simple solution: set the archive_command to something that does nothing and always returns 0 (zero). BUT (yeah there's always this big BUTT when handling databases :) ) you will see messages in your log files:

LOG: archived transaction log file "00000001000000000000003A"

And the problem with this that you actually didn't archive anything, and yet you see success messages; this can be confusing when you are analyzing the records later! So how do you keep a record of what actually happened? Did you actually archive a particular file!?

Here's a command:

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

This will keep a separate log (named dummy_archive.log in your data directory) for the transaction logs that you didn't archive, and your database will run smoothly, without any warnings in the logs. And when you actually wish to start archiving, just change this command on the fly to start actual archiving. No downtimes.

So here's the final verdict:

archive_mode = on

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

PS: I wish I could make this command's output go to the log_destination of postgres, in a generic manner! If you know of something that'll help achieve this, please comment here or write to me.

UPDATE: (Nixen (Unix like) systems only) The following archive_command lets you push these 'dummy archival messages' into Postres's server log:
archive_command = 'date >&2 && echo did dummy archiving for %f >&2 '