Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3B6542E0056 for ; Fri, 15 Feb 2008 06:56:14 -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 65787-02 for ; Fri, 15 Feb 2008 06:56:11 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by postgresql.org (Postfix) with ESMTP id DF88F2E0052 for ; Fri, 15 Feb 2008 06:56:10 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so1251253waf.5 for ; Fri, 15 Feb 2008 02:56:09 -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:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=AFRMjlrzlvTfGOq6oYU/fiMZhKxiVG2hzFH57DvIxvI=; b=F3yu2mz7JD7FAgQPNheXO2fopeuaCUtxEVWrF3xPDcGRODX11jN0gp/k25V2+VenXKBXt3JGdIvvcB3NhKmP4NZFpQvRnSxA2u1YdCj1o5OfFRlvkYhzKHelLDYNaoksztMUKNUvM+sWnZnwdiDlkoAS/KqNnmgeUq8q04davAI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=UkS5TI9HgtL+YnVdyEmWjPrmcsbWzGn16PWNj7ngDX9vhN71QlPq9lRAKpGTZPwiQjGdhzEoDR9AaKqQMach6sCITmHKKFMjOERMbViYU/O0bOAXg/C37cJ15pz/kqdEoWJZT+/ML4s//bvaux3vMhB9yBnUFGXJSlBiQXNg6dM= Received: by 10.114.52.1 with SMTP id z1mr2704417waz.123.1203072969818; Fri, 15 Feb 2008 02:56:09 -0800 (PST) Received: by 10.114.177.8 with HTTP; Fri, 15 Feb 2008 02:56:09 -0800 (PST) Message-ID: Date: Fri, 15 Feb 2008 13:56:09 +0300 From: if Reply-To: zeylienospam@gmail.com To: pgsql-admin@postgresql.org Subject: vaccuming very large table problem MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/188 X-Sequence-Number: 28262 Hello list! We use postgresql as a backend to our email gateway, and keep al emails for in database. Using postgres version 7.4.8 (yes, i know it's old), and rather specific table schema (the application was desined that way) -- all emails split into 2kb parts and fed up into pg_largeobject. So, long story short, i now have a catch-22 situation -- database using about 0.7TB and we are running out of space ;-) I can delete some old stuff but i cannot run full vacuum to reclaim disk space (i takes way more than full weekend) and i also cannot dump/restore as there's no free space (2x database) So, with this restrictions aplied, i figured out that i can somehow zero out all old entries in pg_largeobject or even physically delete these files, and rebuild all neccesary indexes. What is the best way to do this? IMO, dd'ing /dev/zero to this files will cause postgres to reinitialize these empty blocks, and after this will still need to vacuum full over 0.7TB, am i right? And if i delete them, then start postmaster, there'll be lots of complaining but will the latest data be saved? How can i delete, for instance, first 70% of data reasonably fast? P.S. Please cc me, as i'm not subscribed yet. Thanks in advance! regards, if