Message-Id: <49AD3C83.EE98.0025.0@wicourts.gov>
Date: Tue, 03 Mar 2009 14:19:47 -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>
In-Reply-To: <20090303204005.3607451e@pc-jsosic.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: 
> I have a problem with WAL files... I have a fairly busy server, with
> lots of transactions every second. Database is backend for Zabbix
> monitoring application, and it writes performance counters of 500+
> hosts every few seconds.
> 
> I'm trying to implement "Continuous Archiving" backup solution, but
> I have a problem. In 1 hour of copying WAL files to another hard
> drive, PostgreSQL generated 304 WAL files of 16 MB's.
 
What you are copying is all the information required to restore the
database to the state it was in after the commit of any one of these
transactions.  Out of curiosity, how much space would you have thought
that would take?
 
> This is absolutely unacceptable to backup this volume of
> information.
 
Then you need to choose a different backup strategy.  You probably
don't need to keep a lot of these for a long time.  We keep enough to
restore from the latest two base backups, plus the minimum set
required to restore a "snapshot" for the first base backup of each
month.
 
> Quick calculations gives approx 292 GB of data per day, on a
> database that has only 150GB ondisk.
 
The WAL file volume will have nothing to do with the size of the
database; rather it reflects all activity which modified the database
over some period of time.
 
> Where am I making mistake? Why is PostgreSQL generating so much
> WAL's?
 
It records each change made to every row in the database.  Do fewer
updates or keep the WAL files for less time?
 
> I've looked for pg_clearxlogtail program, as suggested in some
> threads on this list, but I can't find that program on my hard disk,
> and google isn't helping either.
 
It's on pgfoundry.org, but no need to go after it.  What it's designed
to help with is improving the compression of WAL files which are sent
before they're full.  It sounds like yours are full.  You should
probably be compressing the WAL files.  They compress down to about 4
MB to 8 MB each when full if you pipe them through gzip.
 
I hope this helps.
 
-Kevin