Subject: Re: Largest DATABASE
From: Rod Taylor <pg@rbt.ca>
To: Jamil <jamil.figueira@ibi.com.br>
Cc: "'pgsql-benchmarks@postgresql.org'" <pgsql-benchmarks@postgresql.org>
In-Reply-To: <A1529E2B5C106443B913EDFCB79854A11273E314@CEAMAIL>
References: <A1529E2B5C106443B913EDFCB79854A11273E314@CEAMAIL>
Content-Type: text/plain; charset=iso-8859-1
Message-Id: <1095194766.98565.102.camel@jester>
Mime-Version: 1.0
Date: Tue, 14 Sep 2004 16:46:06 -0400
Content-Transfer-Encoding: 8bit

> 	I would like to know witch was the largest database that you ever
> had to administrate. My database is about to 160GB and I´ve got some
> problems to backup it using the pg_dump command and I too have some problems
> to run vaccum.

Tell me about it. We started to do more fine grained scheduling of
vacuum a little while back we we past the 120GB mark.

You can try out the vacuum daemon, but it really didn't help me (little
tables were ignored too much, big tables done too often).

Check VACUUM VERBOSE output to see if you need to vacuum all of the
structures at the current rate.


We're still doing the dump, but running pg_dump on a different machine.
Most of the CPU time it eats up is in formatting the data for the dump.


Another option (which can take some effort) is to take the database
offline, fsync, take a filesystem snapshot, restart the database, tar up
the snapshot, remove the snapshot.

Database downtime can be short enough that if clients reattempt a failed
connection for some time (a couple of minutes), they'll simply see a
hicup and not a failure.


Looking forward to PITR making backups much friendlier.