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 '

Postgres development notes

Tom Lane uses 'emacs + glimpse' for development (source)

GMail provides IMAP access!!

Just saw a new feature from GMail... They now provide IMAP access!!! Just go to the 'Settings' section, and look for 'Forwarding and POP/IMAP' section. If you still see just POP in the section header, you will have to wait a little more to get this feature enabled for your account.

Here are the instructions for configuring Thinderbird to use IMAP...

Just follow the instructions above and see all your labels also being shown in your account (something that was not possible by using POP!)

This has been a long awaited feature....

Now this also makes one more much awaited features possible: Finding the bulkiest/biggest emails you have in your account, and the ability to delete them... No more need to write the cryptic search terms in GMail (has:attachment) and trying to figure out which mail to delete!!!

All you have to do now is register your GMail account using the IMAP inteface in a mail client of your choice and sort the messages by size. Go to the end of the sorted list and see if you want to delete the biggest messages...

This was not possible earlier with GMAIL+POP+Thunderbird because of a bug in Thunderbird. Thunderbird can now properly sort messages based on the apparent size of the mail (shown by the mail header) rather than having to download the message to know it's actual size.

(and it now provides 4GB if mailbox size!)

Enjoy GMail...