Skip site navigation (1) Skip section navigation (2)

Re: Trouble with recovering disc space

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "alma korte" <postgres(at)zmail(dot)sk>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Trouble with recovering disc space
Date: 2008-05-21 19:07:07
Message-ID: dcc563d10805211207y27596b96nc0f7984122458c3d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Thu, May 15, 2008 at 6:54 AM, alma korte <postgres(at)zmail(dot)sk> wrote:
> Dear list,
>
> I have the following issue with recovering disc space...
>
> Interscan Web Security Suite(Trend Micro gateway antivirus appl.) uses
> Postgres database to store access logs(to the internet) in table tb_url_usage.
>
> This table occupies approximately 12GB based on oid:
>
> -rw-------  1 iscan iscan 1073741824 May 15 14:37 48495427
> -rw-------  1 iscan iscan 1073741824 May 15 02:00 48495427.1
> -rw-------  1 iscan iscan 1073741824 May 15 11:25 48495427.10
> -rw-------  1 iscan iscan 1073741824 May 14 02:01 48495427.2
> -rw-------  1 iscan iscan 1073741824 May 14 02:01 48495427.3
> -rw-------  1 iscan iscan 1073741824 May 14 13:34 48495427.4
> -rw-------  1 iscan iscan 1073741824 May 14 13:34 48495427.5
> -rw-------  1 iscan iscan 1073741824 May 15 14:37 48495427.6
> -rw-------  1 iscan iscan 1073741824 May 15 13:19 48495427.7
> -rw-------  1 iscan iscan 1073741824 May 15 11:58 48495427.8
> -rw-------  1 iscan iscan 1073741824 May 15 11:30 48495427.9
> -rw-------  1 iscan iscan  399007744 May 15 09:07 48495427.11
>
> Now, if I run a DELETE(some rows) on this table after that VACUUM FULL it
> does not return any space to the operating system. These files remains. If I
> am right, VACUUM FULL should decrease the physical size of the table, am I?
> May I delete som of these files? Or how can I free up space on filesystem ?
>
> I am running Postgresql version 8.0 on Fedora Core 4. If you need some
> additional information I send you...

Most of the time this happens there's still a transaction somewhere
that needs to be able to "see" those old rows.  Either there's a
connection somewhere to your db with an open transaction, or a
prepared transaction that stalled.  I don't know if 8.0 supported
prepared transactions or no.  The more common cause is the first on,
an idle transaction that's been connected for a while.

In response to

pgsql-admin by date

Next:From: Scott MarloweDate: 2008-05-21 19:08:29
Subject: Re: [Admin]To estimate the size of db
Previous:From: Hyatt, GordonDate: 2008-05-21 15:03:02
Subject: FW: v8.2.6->8.2.7 upgrade renamed /var/lib/pgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group