Skip site navigation (1) Skip section navigation (2)

Re: Incremental Backup Script

From: Zach Bagnall <zach(dot)bagnall(at)bulletinwireless(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Incremental Backup Script
Date: 2006-01-04 03:41:43
Message-ID: 43BB43F7.4020105@bulletinwireless.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2006-01-04 03:44:58
Subject: TRUNCATE, VACUUM, ANALYZE privileges
Previous:From: Andrew DunstanDate: 2006-01-04 02:33:41
Subject: Re: Deferrable UNIQUE INDEX?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group