Re: Incremental Backup Script

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.