Re: drop database, vacuum full and disk space

On Thu, Feb 28, 2008 at 12:17 PM, Antonio Grassi <agrassic(at)gmail(dot)com> wrote:

> Hi list. I've searched the archives and I've seen a lot of posts related
> to database size, vacuuming, etc., but I can't totally understand this.
>
> I've a small database of about 300Mb (when initially loaded), whose data
> is almost completely replaced in the night (via various "drop table" and the
> load of dumps of such tables from other postgresql installation).
>
> Altough I've autovacuum activated, the size of this database is now about
> 3Gb. I've also full vacuumed the base by hand from time to time since the
> base was created, but that didn't help either.
>
> Yesterday I recreated the database with another name, thus having database
> A with 3Gb and database B with 300Mb. Then I made a "drop database A", and
> ran vacuumdb -af. But the space used by postgresql didn't go down. This
> makes me think that vacuum full just releases pages associated to existent
> database objects, is this correct?
>
> So my question is: is there a way to reclaim those 3Gb for the operating
> system, without deleting (rm) the data dir, doing initdb again and
> recreating the database (to finish up with one database of about 300Mb and
> 300Mb of disk usage)?
>
> Thanks in advance,
> Antonio
>
> PS: Yes, I've very few disk space, and no, recreating the database from
> scratch (in the 'rm' sense) wouldn't be too easy (work place
> particularities).
>

Try running a reindex (reindexdb -e -d <db>) and cluster all of the tables.
These are blocking transactions, but should release all of the bloat in your
db.

The cluster has to be run on a table by table basis and will cluster the
table on the primary key if no other index is provided. See the
documentation for more information.

HTH,

Chris

--
Come see how to SAVE money on fuel, decrease harmful emissions, and even
make MONEY. Visit http://colafuelguy.mybpi.com and join the revolution!