On Thu, May 15, 2008 at 6:54 AM, alma korte <postgres@xxxxxxxx> 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.
