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/ |