Message-Id: <49AE40A7.EE98.0025.0@wicourts.gov>
Date: Wed, 04 Mar 2009 08:49:43 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-admin@postgresql.org>,"Jakov Sosic" <jakov.sosic@srce.hr>
Subject: Re: Backing up postgresql database
References: <20090303204005.3607451e@pc-jsosic.srce.hr>
	<49AD3C83.EE98.0025.0@wicourts.gov> <49ADC204.3050200@srce.hr>
In-Reply-To: <49ADC204.3050200@srce.hr>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

>>> Jakov Sosic <jakov.sosic@srce.hr> wrote: 
> How do you mean, do fewer updates?
 
Well, without knowing the application it's hard for me to say; but, as
a guess, perhaps the application could be modified to accumulate, say,
a minute's worth of data and update it in summary, rather than
updating each sample once per second.  That would leave you vulnerable
to losing up to a minute's worth of data, but would cut the rate of
WAL generation to less than 2% of its current rate.
 
> And what do you mean by keeping WAL's for less time?
 
Do base backups more frequently.  If you need to keep more than two
base backup's worth, only keep "snapshots" of older backups -- just
the base and enough WAL files to cover the range specified in the
*.backup file generated by the start and stop functions run during the
base backup.
 
> I've read about gziping WAL's, and I will do it offcourse, but that
> only makes problem a little smaller, doesn't solve it :)
 
Well, cutting the scope of a problem by two thirds is sometimes
enough.  If you combine that with the summary updates mentioned above,
you might reduce the space needed to store WAL files to less than 1%
of current requirements.  Judicious use of the snapshot technique
could let you keep a few snapshots going back 90 days and only require
a small fraction of 1% of what you're thinking it will take based on
current numbers.
 
-Kevin