Date: Thu, 11 Feb 2010 20:39:30 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Albert Shih <Albert.Shih@obspm.fr>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-admin@postgresql.org
Subject: Re: How to manage WAL
Message-ID: <20100211233930.GL3145@alvh.no-ip.org>
References: <20100211223245.GA13434@obspm.fr>
	<4B7437B1020000250002F1FD@gw.wicourts.gov>
	<20100211233225.GA55871@obspm.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20100211233225.GA55871@obspm.fr>
User-Agent: Mutt/1.5.20 (2009-06-14)

Albert Shih wrote:
>  Le 11/02/2010 à 17:00:33-0600, Kevin Grittner a écrit
> > Albert Shih <Albert.Shih@obspm.fr> wrote:
> >  
> > > I would like to known what's best way to manage the WAL.
> >  
> > You should design a policy which meets your particular needs.  We
> > keep two PITR base backups and all the WAL files from before the
> > start of the first (based on the information in the backup file)
> > through current.  We also save monthly archival PITR base backups
> > with just the WAL files needed to restore them (again, based on the
> > backup files).  Your needs may be different.
> 
> Hum...I'm not sure I really understand what you doing. 
> 
> Maybe I'm doing everything wrong but this is what I did : 
> 
> 	t=0  	SELECT pg_start_backup('label');
> 		rsync /pgsql backup_server:/
> 		SELECT pg_stop_backup();
> 		
> 	t> 1 	cron with rsync /WAL backup_server:
> 
> but after some time the /WAL become big. So is it a solution to 
> stop the database, delete everything in /WAL and begin a new cycle (t=0 select etc...) ? 

You could do that if you wanted, but there's a window during which you
have no backup at all.  Perhaps it's better to take a second base
backup, start archiving to a different directory /WAL2, and when the
second base backup is done you can delete /WAL

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.