On February 8th, Simon Riggs committed a couple new functions that will allow Hot Standby to be paused and resumed. You can already *read* from the Hot Standby without pausing, but you could never pause the application of changes in the past. You might want to do this if you have a very high-write-volume server, and some very expensive queries that you want to run on a slave.
Basic Recovery Control functions for use in Hot Standby. Pause, Resume,
Status check functions only. Also, new recovery.conf parameter to
pause_at_recovery_target, default on.
The basic idea is that if you have a read-only standby system, you can give it the command:
pg_xlog_replay_pause() and the standby will stop applying changes. Then you can use the database in read-only mode without new changes being applied. When you’re done you can issue the command:
pg_xlog_replay_resume() and proceed with applying logs.
There are some related features that I can’t wait to test out around named restore points for replay. But the ability to pause replay and run queries is just awesome.
This is a feature that Simon talked about back in 2009 at FOSDEM, and I am very excited to see it implemented.