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 4A702D9067 for ; Thu, 15 Sep 2005 17:35:43 -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 36900-06 for ; Thu, 15 Sep 2005 20:35:39 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.195]) by svr1.postgresql.org (Postfix) with ESMTP id DB999D9064 for ; Thu, 15 Sep 2005 17:35:38 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id c2so43248nfe for ; Thu, 15 Sep 2005 13:35:40 -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=IdYuZkXAH6jbs0zr7jxZs+7Y3U8jcdfIOZbdJYCnJUFz3Te8PajRoYJtZJUhulo50lv9l93kHXzwsplFi3fHQIwWNt7jXiQMEsBMLyGr4c8+gdMkXfumzlwPHUxbP5gLk8B1SCObUVtDEoBUCfLbVjHB1OE8CqwozFNLKfPIdE8= Received: by 10.48.144.4 with SMTP id r4mr49334nfd; Thu, 15 Sep 2005 13:35:40 -0700 (PDT) Received: by 10.48.250.10 with HTTP; Thu, 15 Sep 2005 13:35:40 -0700 (PDT) Message-ID: Date: Thu, 15 Sep 2005 21:35:40 +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: <1126811405.12350.29.camel@localhost> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27533_8986774.1126816540318" References: <1126811405.12350.29.camel@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.384 required=5 tests=[AWL=-0.145, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/176 X-Sequence-Number: 18913 ------=_Part_27533_8986774.1126816540318 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Warren On the space issue, seems at a first sight that the old db needs a vacuum= =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. This= =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 > 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 > 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 "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_27533_8986774.1126816540318 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
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:
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,

  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
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



--
"Adopting the = position that you are smarter than an automatic
optimization algorithm i= s generally a good way to achieve less
performance, not more" - Tom= Lane.=20 ------=_Part_27533_8986774.1126816540318--