Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 696C59FBE57 for ; Tue, 15 May 2007 20:25:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08602-08 for ; Tue, 15 May 2007 20:25:07 -0300 (ADT) X-Greylist: whitelisted by SQLgrey-1.7.5 Received: from mail13.syd.optusnet.com.au (mail13.syd.optusnet.com.au [211.29.132.194]) by postgresql.org (Postfix) with ESMTP id 696CC9FBF5B for ; Tue, 15 May 2007 20:25:11 -0300 (ADT) Received: from www3.nicku.org (c211-30-174-44.farfl1.nsw.optusnet.com.au [211.30.174.44]) by mail13.syd.optusnet.com.au (8.13.1/8.13.1) with ESMTP id l4FNP6U4014985; Wed, 16 May 2007 09:25:07 +1000 Received: from localhost (localhost.localdomain [127.0.0.1]) by www3.nicku.org (Postfix) with ESMTP id 79D96246DE6; Wed, 16 May 2007 09:25:06 +1000 (EST) X-Virus-Scanned: amavisd-new at example.com Received: from www3.nicku.org ([127.0.0.1]) by localhost (www3.nicku.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 215UT+NWqonj; Wed, 16 May 2007 09:25:05 +1000 (EST) Received: by www3.nicku.org (Postfix, from userid 1000) id 279C7246F5B; Wed, 16 May 2007 09:25:05 +1000 (EST) Date: Wed, 16 May 2007 09:25:05 +1000 From: Nick Urbanik To: pgsql-admin@postgresql.org Subject: Re: How overcome wait for vacuum full? Message-ID: <20070515232505.GA16021@nicku.org> Mail-Followup-To: pgsql-admin@postgresql.org References: <20070515221356.GG5264@nicku.org> <464A3E24.6060204@polimetrix.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="YiEDa0DAkWCtVeE4" Content-Disposition: inline In-Reply-To: <464A3E24.6060204@polimetrix.com> User-Agent: Mutt/1.4.2.2i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/125 X-Sequence-Number: 25445 --YiEDa0DAkWCtVeE4 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Thank you Geoff, On 15/05/07 16:11 -0700, Geoff Tolley wrote: >Nick Urbanik wrote: >If you're waiting on vacuum to get around to the one table, you can run=20 >VACUUM FULL instead. Clearly that is what I should have done! It has reached the table now. > >Personally I've found that dropping indices (including the PK), then >vacuum full, then recreating indices can be an awful lot faster than >just leaving vacuum full to its own devices. I'll investigate this for next time. >Yes you ought to be able to reclaim disk space by the copy/drop >original/rename procedure, but that gets hard to manage if you have >triggers or foreign keys on the table in question. You might want to >use CREATE TABLE table_copy (LIKE orig_table) to preserve column >defaults if you go down that path. It seems that PostgreSQL 7.3.8 does not support create table table_name like original_table. Can you suggest a way I can create a duplicate table with the same triggers and other stuff that this table has? What is the most efficient way to copy the data? --=20 Nick Urbanik RHCE http://nicku.org nicku@nicku.org GPG: 7FFA CDC7 5A77 0558 DC7A 790A 16DF EC5B BB9D 2C24 ID: BB9D2C24 --YiEDa0DAkWCtVeE4 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) iD4DBQFGSkFRFt/sW7udLCQRAlITAKDjaxDUz/45eXWdBwpnWL3W8/5OhQCVG4i8 unrMdfZY26uFQTqw5XBNNg== =k+hK -----END PGP SIGNATURE----- --YiEDa0DAkWCtVeE4--