DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=iQ/2o1e8moj7Waa8czXd+WNIzxaJP/iu1j5gxYjviBBbL2InsCXoUmQh4Dd1y/+tXVqUqKtKmsX9OMih+OVV91br3kaW0YaW/fRtjs7tpULCw8jBnD4bnthoIvF3Pqu/Va9IZil4y4sxKFOj4ylznCvwt6+anBHiyVnDdaIACdo=;
References: <4E283B55.5080206@noaa.gov>
	<4E2805B4020000250003F657@gw.wicourts.gov>
	<4E287A0C.5090501@noaa.gov>
Message-ID: <1311278126.18027.YahooMailNeo@web39705.mail.mud.yahoo.com>
Date: Thu, 21 Jul 2011 12:55:26 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Reply-To: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: vacuumdb question/problem
To: David Ondrejik <David.Ondrejik@noaa.gov>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <4E287A0C.5090501@noaa.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Dave,=0A=0AYou're on the right track now, however, unloading the table to a=
 flat file using pg_dump may create a very large flat file. =A0Make sure yo=
u use compression ("-Fc" or pipe the dump through gzip, which will use two =
CPU's, one for pg_dump and one for gzip) on the dump file to minimize its s=
ize. =A0If the dump is successful you don't have to drop the table, you can=
 just truncate it and that will recover the used space and hand it back to =
the file system. =A0Truncate is very fast, but then again so is drop table.=
 =A0Be careful. =A0You can then use pg_restore to put the data =A0back into=
 the original table and that will reclaim the space.=0A=0ATake what Kevin s=
aid earlier about autovacuum and possible scheduled vacuum analyze verbose =
jobs to make dead space reusable very, very seriously. =A0Upgrading to 8.4 =
will remove any need to manually manage the free space map in 8.2 and is wo=
rth it, particularly to get a nicer version of autovacuum, although there a=
re differences in automatic casting of data type between 8.2 and 8.4 (and 9=
.x) that you should test before making a wholesale commitment to upgrading.=
 =A0The fixes to your code aren't hard, but need to be done for you to get =
consistent results pre- and post-upgrade.=0A=0AFinally, if there are natura=
l partitions to the data in that table consider using PostgreSQL's partitio=
n feature. =A0What those partitions are depends entirely on your use case(s=
). =A0If the technique fits, you would be able to drop and create new parti=
tions to clear out and populate data quite quickly. =A0Check out=A0http://w=
ww.postgresql.org/docs/8.4/interactive/ddl-partitioning.html=A0for details =
of partitioning in PG 8.4.=0A=0AGood luck!=0A=0ABob Lunney=0A=0A=0A----- Or=
iginal Message -----=0AFrom: David Ondrejik <David.Ondrejik@noaa.gov>=0ATo:=
 pgsql-admin <pgsql-admin@postgresql.org>=0ACc: =0ASent: Thursday, July 21,=
 2011 2:12 PM=0ASubject: Re: [ADMIN] vacuumdb question/problem=0A=0AI think=
 I see a (my) fatal flaw that will cause the cluster to fail.=0A=0A=0A>>=A0=
 From the info I received from previous posts, I am going to change=0A>> my=
 game plan. If anyone has thoughts as to different process or=0A>> can conf=
irm that I am on the right track, I would appreciate your=0A>> input.=0A>> =
=0A>> 1. I am going to run a CLUSTER on the table instead of a VACUUM=0A>> =
FULL.=0AKevin Grittner stated:=0A> If you have room for a second copy of yo=
ur data, that is almost=0A> always much faster, and less prone to problems.=
=0A=0AI looked at the sizes for the tables in the database and the table I =
am trying to run the cluster on is 275G and I only have 57G free.=A0 I don'=
t know how much of that 275G has data in it and how much is empty to allow =
for a second copy of the data. I am guessing the cluster would fail due to =
lack of space.=0A=0AAre there any other options??=0A=0AIf I unload the tabl=
e to a flat file; then drop the table from the database; then recreate the =
table; and finally reload the data - will that reclaim the space?=0A=0AKevi=
n - thanks for the book recommendation.=A0 Will order it tomorrow.=0A=0ATha=
nks again for all the technical help!=0A=0ADave=0A=0A=0A-- =0ASent via pgsq=
l-admin mailing list (pgsql-admin@postgresql.org)=0ATo make changes to your=
 subscription:=0Ahttp://www.postgresql.org/mailpref/pgsql-admin=0A