Re: PITR Archive Recovery - Mailing list pgsql-patches

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
>
> 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...


> Best regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Best regards
--
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)