Re: incremental backups

Sorry for my sharp reply! It looks like we are after the same thing
so that does help a little although it doesn't really answer my
question. I set up my backups system using pg_dump back in 7.3
because that's all there was. I am finally moving to 8.1 and want to
switch to doing incrementals because the dumps are just getting too
big. If you didn't mind showing me your scripts it would probably be
a good staring point for me.

Thanks,

Rick

On Jan 27, 2006, at 3:32 AM, Csaba Nagy wrote:

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