Re: point in time recovery and moving datafiles online

Tom,
Again, many thanks for the reply.

On Thu, 2002-02-21 at 21:27, Tom Lane wrote:
> 
> No, you're missing my point.  You don't need intra-file consistency any
> more than you need cross-file consistency.  You merely need to be sure
> that you have captured all the state of pages that are not updated
> anywhere in the series of WAL entries that you have.
> 
> I had originally started to compose email suggesting that locking on a
> per-disk-page basis (not a per-file basis) would be better, but I do
> not believe you need even that, for two reasons:
> 
> 1. PG will always write changes to data files in page-size write
> operations.  The Unix kernel guarantees that these writes appear atomic
> from the point of view of other processes.  So the data-file-backup
> process will see pagewise consistent data in any case.
> 
> 2. Even if the backup process managed to acquire an inconsistent
> (partially updated) copy of a page due to a concurrent write by a
> Postgres backend, we do not care.  The WAL activity is designed to
> ensure recovery from partial-page disk writes, and backing up such an
> inconsistent page copy would be isomorphic to a system failure after a
> partial page write.  Replay of the WAL will ensure that the page will be
> fully written from the WAL data.

Wow.  I hadn't appreciated that.  This is way cooler than I realised.

> In short, all you need is a mechanism for archiving off the WAL data and
> locating a checkpoint record in the WAL as a starting point for replay.
> Your data-file backup mechanism can be plain ol' tar or cp -r.  No
> interlocks needed or wanted.

So, the whole job is much easier than I thought.  This is a good thing.

I will rethink my strategy.  It looks like the tasks now are to manage
the archival of WAL files, provide an interface to manage the recovery
process, produce some guidelines/scripts for managing hot backups, and
write the documentation.


-- 
Marc        marc@bloodnok.com