>>> Jakov Sosic <jakov.sosic@xxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
