Re: Recovery target 'immediate'

On Fri, Apr 19, 2013 at 10:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Apr 18, 2013 at 2:11 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> I just found out that if you use continuous archiving and online backups,
>> it's surprisingly difficult to restore a backup, without replaying any more
>> WAL than necessary.
>>
>> If you don't set a recovery target, PostgreSQL will recover all the WAL it
>> finds. You can set recovery target time to a point immediately after the
>> end-of-backup record, but that's tricky. You have to somehow find out the
>> exact time when the backup ended, and set it to that. But if you set it any
>> too early, recovery will abort with "requested recovery stop point is before
>> consistent recovery point" error. And that's not quite precise anyway; not
>> all record types carry timestamps, so you will always replay a few extra
>> records until the first timestamped record comes along. Setting
>> recovery_target_xid is similarly difficult. If you were well prepared, you
>> created a named recovery point with pg_create_restore_point() immediately
>> after the backup ended, and you can use that, but that requires forethought.
>>
>> It seems that we're missing a setting, something like recovery_target =
>> 'immediate', which would mean "stop as soon as consistency is reached". Or
>> am I missing some trick?
>
> You know, I've been wondering for years how you're supposed to do
> this. Huge +1 for adding something like this, if it doesn't exist
> already.

I also don't know good way to do that. +1

Regards,

--
Fujii Masao