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