Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AD79A2E0048 for ; Thu, 28 Feb 2008 13:17:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 58190-06 for ; Thu, 28 Feb 2008 13:17:35 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.188]) by postgresql.org (Postfix) with ESMTP id 106992E012C for ; Thu, 28 Feb 2008 13:17:33 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so4327297fka.3 for ; Thu, 28 Feb 2008 09:17:32 -0800 (PST) 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:mime-version:content-type; bh=YXFHo+3SvkpMtDuNhHgJEzp3+cEFmCeaMhMRS+mAbhM=; b=UEVJHaJuL+0RGBBzsO7rg7dAQxR8cEAToNKQGuNanZH6Tmn0XOEgxjyM00QeayDc0IAMFPA54AuDzDiuD90/IzOjGn0iu2jHPL6i/ERaaQmvHbRGkEQIyANqdgeObsCEg1ejvuvyzkm603vHfTwZppTECqCe0FLyV5Gvm3JGnho= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=kYYNMYkXf6IM4JBVCxsMWbERwGgO5T+iZ3Si37umD0XT/0lupxiEFFFjqgFrfeKbS60CiT7Nb/Ejd1EF4iZbbJN2lgQngoVSGKU86MlDRyiV6F6tSyoeNHLwNaWa1JyFTVG7hvP4tIBop4Vw3E7BT+xZW/MOBo7uyUYJAwSl1hc= Received: by 10.82.161.19 with SMTP id j19mr15979174bue.0.1204219052539; Thu, 28 Feb 2008 09:17:32 -0800 (PST) Received: by 10.82.183.10 with HTTP; Thu, 28 Feb 2008 09:17:32 -0800 (PST) Message-ID: <7fb931000802280917u40d75389t791954b8ea8e00eb@mail.gmail.com> Date: Thu, 28 Feb 2008 15:17:32 -0200 From: "Antonio Grassi" To: pgsql-admin@postgresql.org Subject: drop database, vacuum full and disk space MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19354_10041391.1204219052535" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/402 X-Sequence-Number: 28476 ------=_Part_19354_10041391.1204219052535 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi list. I've searched the archives and I've seen a lot of posts related to database size, vacuuming, etc., but I can't totally understand this. I've a small database of about 300Mb (when initially loaded), whose data is almost completely replaced in the night (via various "drop table" and the load of dumps of such tables from other postgresql installation). Altough I've autovacuum activated, the size of this database is now about 3Gb. I've also full vacuumed the base by hand from time to time since the base was created, but that didn't help either. Yesterday I recreated the database with another name, thus having database A with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran vacuumdb -af. But the space used by postgresql didn't go down. This makes me think that vacuum full just releases pages associated to existent database objects, is this correct? So my question is: is there a way to reclaim those 3Gb for the operating system, without deleting (rm) the data dir, doing initdb again and recreating the database (to finish up with one database of about 300Mb and 300Mb of disk usage)? Thanks in advance, Antonio PS: Yes, I've very few disk space, and no, recreating the database from scratch (in the 'rm' sense) wouldn't be too easy (work place particularities). ------=_Part_19354_10041391.1204219052535 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi list. I've searched the archives and I've seen a lot of posts related to database size, vacuuming, etc., but I can't totally understand this.

I've a small database of about 300Mb (when initially loaded), whose data is almost completely replaced in the night (via various "drop table" and the load of dumps of such tables from other postgresql installation).

Altough I've autovacuum activated, the size of this database is now about 3Gb. I've also full vacuumed the base by hand from time to time since the base was created, but that didn't help either.

Yesterday I recreated the database with another name, thus having database A with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran vacuumdb -af. But the space used by postgresql didn't go down. This makes me think that vacuum full just releases pages associated to existent database objects, is this correct?

So my question is: is there a way to reclaim those 3Gb for the operating system, without deleting (rm) the data dir, doing initdb again and recreating the database (to finish up with one database of about 300Mb and 300Mb of disk usage)?

Thanks in advance,
Antonio

PS: Yes, I've very few disk space, and no, recreating the database from scratch (in the 'rm' sense) wouldn't be too easy (work place particularities).
------=_Part_19354_10041391.1204219052535--