Re: Recovery target 'immediate'

On 26 April 2013 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> That said, maybe the easier choice for a *system* (such as v-thingy)
>> would be to simply to the full backup using pg_basebackup -x (or
>> similar), therefor not needing the log archive at all when restoring.
>> Yes, it makes the base backup slightly larger, but also much
>> simpler... As a bonus, your base backup would still work if you hosed
>> your log archive.
>
> It doesn't appear to me that that resolves Heikki's complaint: if you
> recover from such a backup, the state that you get is still rather vague
> no? The system will replay to the end of whichever WAL file it last
> copied.
>
> I think it'd be a great idea to ensure that pg_stop_backup creates a
> well defined restore stop point that corresponds to some instant during
> the execution of pg_stop_backup. Obviously, if other sessions are
> changing the database state meanwhile, it's impossible to pin it down
> more precisely than that; but I think this would satisfy the principle
> of least astonishment, and it's not clear that what we have now does.

Restore points are definitely the way to go here, this is what they
were created for. Stopping at a labelled location has a defined
meaning for the user, which is much better than just "stop anywhere
convenient", which I found so frightening.

It should be straightforward to create a restore point with the same
name as used in pg_start_backup('text');

pg_basebackup backups would need to use a unique key, which is harder
to achieve. If we write a WAL record at backup start that would make
the starting LSN unique, so we could then use that for the restore
point name for that backup.

If people want anything else they can request an additional restore
point at the end of the backup.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services