Re: PITR Archive Recovery

On Thu, 2004-07-01 at 16:11, ohp@pyrenet.fr wrote:
> Many thanks for your reply Simon
> On Wed, 30 Jun 2004, Simon Riggs wrote:
>
> > Date: Wed, 30 Jun 2004 19:29:14 +0100
> > From: Simon Riggs <simon@2ndquadrant.com>
> > To: ohp@pyrenet.fr
> > Cc: pgsql-patches@postgresql.org
> > Subject: Re: [PATCHES] PITR Archive Recovery
> >
> > On Wed, 2004-06-30 at 12:27, ohp@pyrenet.fr wrote:
> > > Given that log files will be archieved, how can we purge them (ie know for
> > > sure we won't need them anymore)
> > >
> >
> > Good question - you're right I've not mentioned that.
> >
> > The answer is straightforward. Whenever you do a backup, one of the
> > transaction logs will be the current one. That means any logs before the
> > earliest one you can see can now be purged from the archive.
> >
> > So if you can see: 137,138,139 then that means anything at 136 or before
> > is able to be discarded.
> Ok, that's clear...
> BUT not very easy to put in a backup stagtegy...
> It may be ok if you user tar or cpio; but surely more complicated if you
> use backup software like Netvault or Tapeware

Of course, I CAN help with that, but you're right, it isn't in any
manual.

> >
> > However, I'd recommend keeping more than just one backup, usually 2 or
> > 3, so the actual purge point is dependant upon your data retention
> > strategy, possibly linked to tape rotation etc..
> >
> sure
> > > if I do a backup of the DATA dir, then obviously I won't need the logs
> > > that were taken before. I can't just delete them all because maybe a few
> > > will be archived during the backup.
> > >
> >
> I agree
> > Taking a full physical backup will normally need to exclude the pg_xlog
> > directory, or at least the current xlog. Since it is being written to
> > very regularly it is almost impossible to take a clean copy using
> > standard utilities - though filesystem level utilities work fine.
> >
> Would it make sense to have SQL phrases (as I recall from my informix days
> 10 years ago)
> like
> START BACKUP LEVEL 0 where cluster would be archieved on whatever you
> want, disallowing all writes and
> SART BACKUP LEVEL 1 where cluster and logs would be archieved letting
> read/write o databases possible...
>

These are possible in a variety of ways at operating system or device
level, so no these haven't been implemented (yet?) for PostgreSQL.

Best regards, Simon Riggs