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?
- Heikki