Re: incremental backups

OK, that was before going home from work, so it could be excusable :-D
I read your mail now in more detail, and I can't answer it other than
that we use here a standby data base based on WAL log shipping, and the
procedure of building the standby finishes with a script
inserting/deleting a few 1000s of lines in a bogus table so there is for
sure a WAL file archived. That might fit your needs or might not...

Cheers,
Csaba.

On Thu, 2006-01-26 at 18:48, Rick Gigger wrote:
> Um, no you didn't read my email at all. I am aware of all of that
> and it is clearly outlined in the docs. My email was about a
> specific detail in the process. Please read it if you want to know
> what my actual question was.
>
> Thanks,
>
> Rick
>
> On Jan 26, 2006, at 10:41 AM, Csaba Nagy wrote:
>
> > I didn't read your mail very carefully, but I guess you want:
> >
> > - turn on WAL archiving, and archive all WAL logs;
> > - take the file system backup at regular time points, optionally you
> > can keep them also for point in time recovery;
> >
> > Then you always have all the WAL files you need to recover to any
> > point
> > in time you need. You can then supply all the WAL files which are
> > needed
> > by the last file system backup to recover after a crash, or you can
> > supply all the WAL files up to the time point just before your student
> > DBA deleted all your data.
> >
> > HTH,
> > Csaba.
> >
> >
> > On Thu, 2006-01-26 at 18:33, Rick Gigger wrote:
> >> I am looking into using WAL archiving for incremental backups. It
> >> all seems fairly straightforward except for one thing.
> >>
> >> So you set up the archiving of the WAL files. Then you set up cron
> >> or something to regularly do a physical backup of the data
> >> directory. But when you do the physical backup you don't have the
> >> last WAL file archived yet that you need to restore that physical
> >> backup. So you always need to keep at least two physical backups
> >> around so that you know that at least one of them has the WAL files
> >> needed for recovery.
> >>
> >> The question I have is: how do I know if I can use the latest one?
> >> That is if I first do physical backup A and then later do physical
> >> backup B and then I want to do a restore. How do I know when I've
> >> got the files I need to use B so that I don't have to go all the way
> >> back to A?
> >>
> >> My initial thoughts are that I could:
> >>
> >> a) just before or after calling pg_stop_backup check the file system
> >> to see what the last archived WAL file is on disk and make sure that
> >> that I get the next one before I try restoring from that backup.
> >>
> >> b) just before or after calling pg_stop_backup check postgres to see
> >> to see what the current active WAL file is and make sure it has been
> >> archived before I try to restore from that backup.
> >>
> >> c) Always just use backup A.
> >>
> >> No c seems the easiest but is that even fail safe? I realize it
> >> wouldn't really ever happen in an active production environment that
> >> was set up right but say you did backup A and backup B and during
> >> that whole time you had few writes in postgres that you never filled
> >> up a whole WAL file so both of the backups are invalid. Then you
> >> would have to always go to option a or b above to verify that a given
> >> backup was good so that any previous backups could be deleted.
> >>
> >> Wouldn't it make things a lot easier if the backup history file not
> >> only gave you the name of the first file that you need but also the
> >> last one? Then you could look at a given backup and say I need this
> >> start file and this end file. Then you could delete all archived WAL
> >> files before start file. And you could delete any old physical dumps
> >> because you know that your last physical dump was good. It would
> >> just save you the step in the backups process of figuring out what
> >> that file is. And it seems like pg_stop_backup could determine that
> >> on it's own.
> >>
> >> Does that make sense? Am I totally off base here?
> >>
> >> Rick
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 6: explain analyze is your friend
> >
> >
>