On 25th of January, Heikki Linnakangas committed patch:

Add recovery_target='immediate' option.
 
This allows ending recovery as a consistent state has been reached. Without
this, there was no easy way to e.g restore an online backup, without
replaying any extra WAL after the backup ended.
 
MauMau and me.

Recovering from hot backups is relatively simple – you generally unpack tarball and start Pg in created directory.

The thing is – if you just want standalone DB, not a slave to master, when do you stop recovering? You can recover all the xlogs up to the newest ones, or you can set recovery_target_time, recovery_target_xid or recovery_target_name, and recover up to this point.

But how can you start from backup and make it so that it will recover minimum information from xlogs? Up to this point you basically had to watch over Pg logs, look for “consistent recovery state reached" message in logs, and then finish recovery.

This is generally good, but requires additional processes, and what's more – it never will allow to recover just minimal amount of WAL – as there will be always some delay between detecting “consistent recovery state reached" and finishing recovery.

Now, thats to MauMau and Heikki, we can actually configure PostgreSQL so that it will recover minimal possible amount of WAL, and become then finish recovery automatically.

For example, assuming you're using simple “cp" as your restore command, you might use such recovery.conf:

restore_command='cp /mnt/walarchive/%f %p'
recovery_target = 'immediate'

And this will give you standalone, working PostgreSQL, with minimal amount of WAL applied. Cool. That will simplify setting dev copies of databases.

  1. 2 comments

  2. # Hervé
    Feb 13, 2014

    Hi

    When you say as for now one can watch over pg logs to look for “consistent recovery state reached” and then finish recovery, I’m wondering how one could stop an ongoing recovery ? During that time the backend is refusing any pg connection.

  3. Feb 13, 2014

    @Herve:
    well, in your configuration you should have specified a way to finish. Usually a trigger_file. Check documentation for your recovery tool, or recovery.conf itself.

Leave a comment