Re: Planning hot/live backups?

I back up around 10 Gig of data every half hour using pg_dump. I don't
backup the entire database at once. Instead I backup at the schema
namespace level. But I do all of them every half hour. It takes four
minutes. That includes the time to copy the files to the backup server.
I do each schema namespace backup consecutively. I also run vacuum full
analyze once a day. My system is up 24/7 as well. I don't backup in
the middle of the night. There is so little back. But I could. I am
able to have more backups by not doing it when there are only a handful
of transactions.

Thanks,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Steve Poe
Sent: Monday, March 24, 2008 3:23 PM
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Planning hot/live backups?

The owners of the animal hospital where I work at want to consider
live/hot
backups through out the day so we're less likely to lose a whole
day of transaction. We use Postgresql 8.0.15. We do 3AM
backups, using pg_dumpall, to a file when there is very little activity.

The hospital enjoys the overall performance of the veterinary
application running
on Postgresql. I know doing a mid-day backup when up to 60 computers
(consistently
35-40) are access client/patient information, it will cause some
frustration. I understand
there needs to be balance of performance and backup of current records.

While I know that not all situations are the same, I am hoping there
is a performance
latency that others have experienced when doing backups during the day
and/or
planning for cluster (or other types of redundancy).

My animal hospital operates 24x7 and is in the south part of the San
Francisco Bay area. Outside
of sharing your experiences/input with me, I would not mind if you/your
company
do this type of consulting offline.

Thank you.

Steve

-
Sent via pgsql-performance mailing list
(pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance