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

Re: Incremental Backup Script

From: Rick Gigger <rick(at)alpinenetworking(dot)com>
To: Zach Bagnall <zach(dot)bagnall(at)bulletinwireless(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Incremental Backup Script
Date: 2006-01-04 07:52:08
Message-ID: 1FEC4B88-9936-4608-B997-51FC93E5AB8D@alpinenetworking.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
>


In response to

pgsql-hackers by date

Next:From: Gregor ZeitlingerDate: 2006-01-04 09:30:24
Subject: Re: Incremental Backup Script
Previous:From: Rick GiggerDate: 2006-01-04 07:42:01
Subject: Re: [DOCS] Online backup vs Continuous backup

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