Обсуждение: VACUUM FULL fails to free diskspace

Hi,

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)


/Morten %-)

(This message may show up twice if the list-moderator is too fast.
I failed to post from the rigth address the first try :-)

Morten Guldager wrote:
> Hi,
>
> 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.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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.



/Morten %-)