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(dot)zeitlinger(at)torexretail(dot)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
>