Date: Tue, 3 Mar 2009 20:40:05 +0100
From: Jakov Sosic <jakov.sosic@srce.hr>
To: pgsql-admin@postgresql.org
Subject: Backing up postgresql database
Message-ID: <20090303204005.3607451e@pc-jsosic.srce.hr>
Organization: SRCE
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi!

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. DB is approx 150GB.

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. I mean that is really
apsurd. Here is my config related to WAL:

fsync = on
synchronous_commit = on
wal_sync_method = fsync
wal_buffers = 512kB
wal_writer_delay = 3000ms
commit_delay = 10
commit_siblings = 5
checkpoint_segments = 32
checkpoint_warning = 300s
archive_mode = on
archive_command = 'test ! -f /data/backup/WAL/%f && cp
%p /data/backup/WAL/%f'


This is absolutely unacceptable to backup this volume of information.
Quick calculations gives approx 292 GB of data per day, on a database
that has only 150GB ondisk.

Where am I making mistake? Why is PostgreSQL generating so much WAL's?

I'm using 8.3.4 from official PostgreSQL yum repository on CentOS 5.2.
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.

If you need more information, I will post it.

Thank you.

-- 
|    Jakov Sosic    |    ICQ: 28410271    |   PGP: 0x965CAE2D   |
=================================================================
| start fighting cancer -> http://www.worldcommunitygrid.org/   |