On Tue, 2010-09-21 at 20:39 +0800, Christopher Gorge A. Marges wrote:
> We are using 7.4.13 and run the pg_autovacuum. Over the years, the
> database has grown so our maintenance plan was to "move" everything
> except for the last year. Since the server is kept up always using a
> full vacuum is out of the question. However the space is running out
> and we tried installing contrib/dbsize to see which tables were using
> up space. The report of database_size is correct, we are using up to
> 120G of space, but adding up the reported sizes from relation_size
> does not add up. So where does the unused space go?
Bloat. All databases have bloat. If you aren't maintaining "enough" you
are going to continue to use up more and more hd space.
Also, 7.4 is about to be deprecated. I *STRONGLY* Suggest you migrate to
at least 8.2. Preferably 8.4 but that will likely take more work do to
removal of implicit casts in 8.3.
>
> The steps we do to move the data is as follows:
> 1) dump the data (per table)
> 2) restore the data to another server
> 3) delete the dumped records from the production server
> 4) vacuum analyze
You could consider installing a version of Slony that still supports 7.4
and then "replicate" the data to the new server.
>
> Is there anyway to reclaim the space (the space used up by the tables
> themselves are less than 20G).
Not without an outage on the relation.
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt