When developing or testing application code, I like to do it against a pretty fresh snapshot of the production server. I have a script which refreshes my dev server by cloning the production server from its most recent base backup, and recovering it forward from files in the wal archive. With a few tricks thrown in to pre-stage a copy of the wal and a copy of the base backup, this can be extremely fast, less than 15 seconds if the base backup it is working from is reasonable recent.
This works pretty well, except that the dev copy can be out of date by up to archive_timeout, which is annoying.
I was thinking of making the dev server become a warm standby so it can end the cloning process by connecting to the production server and fetching the WAL records since the last log file was archived. I would want it to be promoted as soon as it asks for more WAL records from the master, but the master doesn't have any more. But, there doesn't seem to be any option to do that cleanly, i.e. no setting is available like:
recovery_target = 'caught-up'
Is there any way to achieve this cleanly? The best thing I can think of is to create a background job which sleeps for a number of seconds that "should be sufficient" and then creates a trigger file. That seems pretty grotty.
Is there a better solution?
Cheers,
Jeff