Backing up postgresql database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux