Re: Incremental Backup Script

I would certainly like some instructions on this as well.

On Jan 3, 2006, at 8:41 PM, Zach Bagnall wrote:

> On 12/26/05 11:04, Qingqing Zhou wrote:
>> ""Gregor Zeitlinger"" <gregor.zeitlinger@torexretail.de> wrote
>>> Also, I was wondering whether it is always safe to copy the  
>>> current WAL file, i.e. may the current WAL file be invalid in any  
>>> circumstance?
>>>
>> If you mean "current WAL file" is the xlog segment in use, then it  
>> is dangerous. We only backup the xlog segments that have been  
>> fully used up.
>
> As per docs, if the databases are rarely updated it could take a  
> long time for the WAL segment to "roll over". We need to backup the  
> current segment to guarantee we have the latest trasactions  
> archived at time of failure.
>
> http://www.postgresql.org/docs/8.1/interactive/backup-online.html
> "If you are concerned about being able to recover right up to the  
> current instant, you may want to take additional steps to ensure  
> that the current, partially-filled WAL segment is also copied  
> someplace. This is particularly important if your server generates  
> only little WAL traffic (or has slack periods where it does so),  
> since it could take a long time before a WAL segment file is  
> completely filled and ready to archive. One possible way to handle  
> this is to set up a cron job that periodically (once a minute,  
> perhaps) identifies the current WAL segment file and saves it  
> someplace safe."
>
> Gregor: can you explain how to identify the current file? I had  
> implemented a backup and restore script for PITR but stumbled at  
> this point. The page above does not specify how this is to be done.
>
> I appreciate the addition of PITR - it's better than nothing  
> (nothing being full dumps) in some respects. Ideally, we need to be  
> able to dump deltas for a single database. In practice, restoration  
> using the PITR method is awkward. I guess you would tarball the  
> current data files, do a full restore, do a full dump of the  
> database you are interested in, ditch the restored data files and  
> replace them with the ones you tarballed, then do a database load  
> from the full dump. The only way to avoid having the other  
> databases on the server offline is to restore to a second  
> postgresql instance. Not complaining, just saying :-)
>
>
>
>> Regards,
>> Qingqing
>
> Zach.
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>