Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 6956F2E0046 for ; Wed, 21 May 2008 16:07:21 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97007-03 for ; Wed, 21 May 2008 16:07:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rn-out-0910.google.com (rn-out-0910.google.com [64.233.170.186]) by developer.postgresql.org (Postfix) with ESMTP id C15592E003A for ; Wed, 21 May 2008 16:07:11 -0300 (ADT) Received: by rn-out-0910.google.com with SMTP id v73so456827rnb.0 for ; Wed, 21 May 2008 12:07:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=gCu55c6I2VawxGGnCQdN9lrvHReacZAQczSbOoAAVvM=; b=A7sg7Zj7NgOkZuIl1UpTYfC/t230f7J/PEdjju5DWRqVJ9Z6kxr5taN/sqfWBbecE/EOTho7busBZT3VeXnrba5zksyJuJNyOQ53rwSJTIeaI6CUk4uHViD3kmn7NzSrlRzLuwMBnF6E+5LCmrCAOOmkLPJKVqqzQDSiDV1gsD8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=RDVa80EU/hWi/fJepWph3HIV7uNeQpkwZ+SXmXejfqI99J7OIlG5M8COhAt7o5b5gt+nGoYf3G2WIDFgs/BkYJDFDZgvf06l/msD5YHvySmBMMaaNed4R0BIX+qYf8XsUwsXVtwMMPuNf0/rlOyjWMp/XtJ/hruQ9rE829uONEY= Received: by 10.114.57.1 with SMTP id f1mr10907155waa.78.1211396827684; Wed, 21 May 2008 12:07:07 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Wed, 21 May 2008 12:07:07 -0700 (PDT) Message-ID: Date: Wed, 21 May 2008 13:07:07 -0600 From: "Scott Marlowe" To: "alma korte" Subject: Re: Trouble with recovering disc space Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/179 X-Sequence-Number: 29263 On Thu, May 15, 2008 at 6:54 AM, alma korte 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.