X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 15FF8D902F for ; Thu, 15 Sep 2005 17:47:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40927-01 for ; Thu, 15 Sep 2005 20:47:28 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.200]) by svr1.postgresql.org (Postfix) with ESMTP id C9940D900F for ; Thu, 15 Sep 2005 17:47:29 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id c2so43529nfe for ; Thu, 15 Sep 2005 13:47:31 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WY1oLLi30c7Qg/QTZHLhwuAgjFghZMKKhVHmFQxjbMjv1j6N2+3Fn93P3MtaHWYZ/LjQEv/y9u4awL4RS/mayWRygphc47RNTi5QN0A296mjdxDsxSNNjd/9CimSqkk8LO9kNowJUNj7edOX4X2GiPWqXJ619KgQzPWgbAERTUM= Received: by 10.48.4.20 with SMTP id 20mr49514nfd; Thu, 15 Sep 2005 13:47:31 -0700 (PDT) Received: by 10.48.250.10 with HTTP; Thu, 15 Sep 2005 13:47:31 -0700 (PDT) Message-ID: Date: Thu, 15 Sep 2005 21:47:31 +0100 From: Guido Barosio Reply-To: gbarosio@gmail.com To: snelling@email.marc.usda.gov Subject: Re: 7.4 <-> 8.0 Cc: pgsql-admin@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27557_32942533.1126817251817" References: <1126811405.12350.29.camel@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.161 required=5 tests=[AWL=0.080, HTML_30_40=0.056, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/178 X-Sequence-Number: 18915 ------=_Part_27557_32942533.1126817251817 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline still thinking on this topic, read about the REINDEX command, that will=20 recreate your indexes, per table or per database. Regards, Guido. On 9/15/05, Guido Barosio wrote:=20 >=20 > Hi Warren > On the space issue, seems at a first sight that the old db needs a vacuu= m=20 > full to free some room. > Try to identify your biggest tables, isolate the vacuum against them, and= =20 > then escalate to the whole db, with a vacuum full. > Thinking that you should double check the vacuum documentation, to=20 > understand better which are the effects of the different vacuum modes. Th= is=20 > will clear out your doubts on why such amount of space is being allocated= ,=20 > but not being used.=20 > My two cents there. > Best wishes, > Guido. > On 9/15/05, Warren Snelling wrote:=20 > >=20 > > All, > >=20 > > Couple questions from to a recent upgrade from 7.4.8 to 8.0.3. With > > servers for both versions running on the same machine,=20 > >=20 > > pg_dumpall -p 5432 -c | psql -p 5433 template1 > >=20 > > appears to have migrated the databases smoothly. At least I didn't > > notice any errors in the process, and all the data appears to be there. > >=20 > > The one troubling thing is the new 8.0 databases take about half the > > disk space as the 7.4 databases. Similar dump | psql to recreate the > > databases on other machines running 7.4.7 and 7.4.8 show the same thing > > - the fresh copies take half the space of the old. What might be=20 > > happening with the old db, so it takes so much more space? Could half > > the data be missing? > >=20 > > What's the best way to move data from 8.0 to 7.4? The 8.0 pg_dump > > writes a dump that doesn't restore with 7.4 psql, and 7.4 pg_dump > > doesn't seem to handle an 8.0 database. At least I haven't found the > > right switches... > >=20 > > Thanks, > > warren > >=20 > >=20 > > ---------------------------(end of broadcast)--------------------------= - > > TIP 2: Don't 'kill -9' the postmaster=20 > >=20 >=20 >=20 >=20 > --=20 > "Adopting the position that you are smarter than an automatic > optimization algorithm is generally a good way to achieve less > performance, not more" - Tom Lane.=20 --=20 "Adopting the position that you are smarter than an automatic optimization algorithm is generally a good way to achieve less performance, not more" - Tom Lane. ------=_Part_27557_32942533.1126817251817 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
still thinking on this topic, read about the REINDEX command, that wil= l recreate your indexes, per table or per database.
 
Regards,
Guido.

 
On 9/15/05, = Guido Barosio <gbarosio@gmail.= com> wrote:
Hi Warren
 
On the space issue, seems at a first sight that the old db needs a vac= uum full to free some room.
Try to identify your biggest tables, isolate the vacuum against them, = and then escalate to the whole db, with a vacuum full.
 
Thinking that you should double check the vacuum documentation, to und= erstand better which are the effects of the different vacuum modes. This wi= ll clear out your doubts on why such amount of space is being allocated, bu= t not being used.=20
 
My two cents there.
 
Best wishes,
Guido.
 
On 9/15/05, = Warren Snelling <sne= lling@email.marc.usda.gov > wrote:=20
All,

Couple questions fro= m to a recent upgrade from 7.4.8 to 8.0.3.  With
servers for b= oth versions running on the same machine,=20

  pg_dumpall -p 5432 -c | psql -p 5433 template1

a= ppears to have migrated the databases smoothly.  At least I didn'= t
notice any errors in the process, and all the data appears to be there= .

The one troubling thing is the new=20 8.0 databases take about half the
disk space as the 7.4 databases. =  Similar dump | psql to recreate the
databases on other machines ru= nning 7.4.7 and 7.4.8 show the same thing
- the fresh copies take half t= he space of the old.  What might be=20
happening with the old db, so it takes so much more space?  C= ould half
the data be missing?

What's the best way to move data f= rom 8.0 to 7.4?  The 8.0 pg_dump
writes a dump that doesn't re= store with 7.4 psql, and=20 7.4 pg_dump
doesn't seem to handle an 8.0 database.  At least = I haven't found the
right switches...

Thanks,
warren

---------------------------(end of broadcast)---------------------------<= br>TIP 2: Don't 'kill -9' the postmaster=20



--
"Adopting the position that you are smarter than an aut= omatic
optimization algorithm is generally a good way to achieve lessperformance, not more" - Tom Lane.=20



--
"Adopting t= he position that you are smarter than an automatic
optimization algorith= m is generally a good way to achieve less
performance, not more" - = Tom Lane.=20 ------=_Part_27557_32942533.1126817251817--