HI All,
I found the following posting about Vacuum in the postgres.admin newsgroup (see below my question)
I have the same problem: We are using HUGE amounts of diskspace in our production database, but the standby (created daily from a pgdump / pg_restore of our production server) is about 1/2 the size.
We have a base of about 4 million users so our datafiles are pretty big. I shut the site down and did a VACUUM ANALYZE VERBOSE <tablename> of 2 of the largest tables. It took over 4 hours. When it was all said and done, disk utilization had gone UP.
My suspicion is that there are pg_toast_xxxxx files left in the base directory? If I identify them with oid2name (waiting for netops to build that) is it ok to just delete those toast files? This is the most crucial part of our revenue year (tons of christmas shoppers buying tons) so we absolutely can't have any down time or corruption.
Any advice or links to documentation that covers this specifically would be most helpful.
thanks
----------------------- start newsgroup posting ------------------------------------
Author: Morten Guldager <PostgreSQL-admin@mogul.dk>
Date: Wed 16 Oct 15:09:40 2002 CDT
Subject: Re: [ADMIN] VACUUM FULL fails to free diskspacef
Thread: 2 messages On 2002.10.16 20:18 Bruce Momjian wrote:
> Morten Guldager wrote:
> >
> > My database cluster have one database with one table. (if we don't
> > count template0 and 1.
> >
> > My table has 3 collumns, 2 ints and a bytea. 2.5M rows and it
> consumes
> > 150G diskspace. I have a unique index on the 2 ints.
> >
> > I did a TRUNCATE on the table, and a VACUUM FULL.
> >
> > But PostgreSQL-7.2.3 did not return the space to the filesystem.
> (ext3
> > on linux)
>
> That is interesting. I originally thought maybe the indexes aren't
> truncated, but it looks like that is happening. Please try
> /contrib/oid2name to find which files are taking the space.
Ok, never used oid2name before, but here we go:
I located a datafile which must be one of the files holding the
non-freed space, it is 1073741824 bytes big.
It is: $PGDATA/base/16556/20048694
Next I did a: (mogul is the name of my database)
$ oid2name -d mogul -o 20048694
Tablename of oid 20048694 from database "mogul":
---------------------------------
20048694 = pg_toast_20048692
and then:
$ oid2name -d mogul -o 20048692
Tablename of oid 20048692 from database "mogul":
---------------------------------
20048692 = regninger
And "regninger" is the name of the table I just truncated.
I have tried to drop the indexes, that does not help.
If I drop the database the space do get freed.