Re: PITR Base Backup on an idle 8.1 server

On Mon, 2007-06-04 at 12:55 +0200, Marco Colombo wrote:
> Greg Smith wrote:
> > The way you're grabbing
> > files directly from the xlog directory only works because your commit
> > workload is so trivial that you can get away with it, and because you
> > haven't then tried to apply future archive logs.
>
> Well, it's only because I don't need future logs, just like I don't need
> "future" files. Backup is at 2:00 AM, any change after that is
> potentially lost. That includes e-mails, web contents, and database
> contents. The database contents are in no way different to us.
>
> It's the "your commit workload is so trivial that you can get away with
> it" I don't really get, but more on this later.
>
> > In the general case,
> > circumventing the archiving when the backup is going on won't guarantee
> > everything is ordered just right for PITR to work correctly.
>
> Generic PITR? You mean if backup is at 2:00 AM and the server crashes
> (all disks lost) at 2:00 PM, you want to be able to recover to some
> time like 11:00 AM, and be precise about it? That's PITR to me - and the
> "precise" part is key here... either the time or the transaction ID
> would do, the point is being able to draw a line and say "anything
> before this is correct".

> my method

...is dangerous and anyone reading this thread would be well advised to
read the manual in full rather than treating this as a new and clever
technique. I'm adding this as a footnote so that the archives are clear
on this point, so we don't get loads of new DBAs picking up this idea
but missing the exact point of danger.

Making the assumption that its OK to archive WAL files in the pg_xlog
directory exposes you to the risk of having them deleted by the
archiver, which will invalidate your backup. That might not happen all
of the time, but I'm willing to bet that the time you need it is the
time it didn't work for you. Even if this doesn't effect you, it might
effect others, so I want to be certain to stamp this out before the fire
spreads.

You can still do the lock file test using a safe method. I'll document
that idea so we can steer people in the right direction.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com