Efficient cleanup and the end of recovery

In recent years, recovery.conf has become more and more powerful. Back in the early days (that is, before PostgreSQL 9.0), there was barely anything more than restore_command and some setting related to recovery_target_time. More modern versions of PostgreSQL already offer a lot more and give you the chance to control your replay process in a nice and professional way.

In this section, you will learn what kind of settings there are and how you can make use of those features easily.

Gaining control over the restart points

So far, we have archived the XLOG indefinitely. Just like in real life, infinity is a concept that causes trouble. As John Maynard Keynes stated in his famous book, The General Theory of Employment, Interest, and Money:

"In the long run, we are all dead."

What applies to Keynesian stimulus is equally true in the case of XLOG archiving; you simply cannot keep doing it forever. At some point, the XLOG has to be thrown away.

To make cleanup easy, you can put archive_cleanup_command into recovery.conf. Just like most other commands (for example, restore_command), this is a generic shell script. The script you will put in here will be executed at every restart point. What is a restart point? Every time PostgreSQL switches from file-based replay to streaming-based replay, you face a restart point. In fact, starting streaming again is considered to be a restart point.

You can make PostgreSQL execute some cleanup routine (or anything else) as soon as the restart point is reached. It is easily possible to clean the older XLOG or trigger some notifications.

The following script shows how you can clean any XLOG that is older than a day:

#!/bin/sh
find /archive "-type f -mtime +1 -exec rm -f {} \;

Keep in mind that your script can be of any kind of complexity. You have to decide on a proper policy to handle the XLOG. Every business case is different, and you have all the flexibility to control your archives and replication behavior.

Tweaking the end of your recovery

The recovery_end_command parameter serves purposes similar to archive_cleanup_command. It triggers some script execution when your recovery (or XLOG streaming) has completed.

Again, you can use this to clean the old XLOG, send out notifications, or perform any other kind of desired action.