Followup Question about Vacuum from newsgroup

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 < <mailto:PostgreSQL-admin(at)mogul(dot)dk>
PostgreSQL-admin(at)mogul(dot)dk>
Date: Wed 16 Oct 15:09:40 2002 CDT

<http://fts.postgresql.org/db/mw/index.html?word=Re%3A%20%5BADMIN%5D%20VACUU
M%20FULL%20fails%20to%20free%20diskspacef> Subject: Re: [ADMIN] VACUUM FULL
fails to free diskspacef
<http://fts.postgresql.org/db/mw/msg.html?mid=1065782#thread> 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.