From: Jakov Sosic <jakov.sosic@srce.hr>
To: pgsql-admin@postgresql.org
Subject: Backing up postgresql database
Date: Tue, 3 Mar 2009 20:40:05 +0100
Message-ID: <20090303204005.3607451e@pc-jsosic.srce.hr> (raw)
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/ |
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jakov.sosic@srce.hr
Subject: Re: Backing up postgresql database
In-Reply-To: <20090303204005.3607451e@pc-jsosic.srce.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox