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 26CE2D90A3 for ; Fri, 16 Sep 2005 13:27:05 -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 06072-10 for ; Fri, 16 Sep 2005 16:27:01 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.197]) by svr1.postgresql.org (Postfix) with ESMTP id 1B061D8275 for ; Fri, 16 Sep 2005 13:26:59 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id c2so73725nfe for ; Fri, 16 Sep 2005 09:26:56 -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=FogccJEEQT2tH+3/G6tIY0kWsF3UmwI3aF3w3SKcUhvB5Y56X4Oia8bt5Rbpe1OsuqjttjA6AY9tGNsrySss1g1RoPdmx4H4BBC5g+PFV9gl37Ptahst7XGu3OEfbLeY73Vdt5TYou4m1UgPtw3LB2tu9SSnA1/kFuOevRQLQuo= Received: by 10.48.43.9 with SMTP id q9mr2674nfq; Fri, 16 Sep 2005 09:26:56 -0700 (PDT) Received: by 10.48.250.10 with HTTP; Fri, 16 Sep 2005 09:26:56 -0700 (PDT) Message-ID: Date: Fri, 16 Sep 2005 17:26:56 +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: <1126886236.12350.106.camel@localhost> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_32035_7700708.1126888016843" References: <1126811405.12350.29.camel@localhost> <1126886236.12350.106.camel@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.179 required=5 tests=[AWL=0.059, HTML_50_60=0.095, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/187 X-Sequence-Number: 18924 ------=_Part_32035_7700708.1126888016843 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Warren, Actually, in my particular case (old pgsql version, and a big db), reindex= =20 and vacuum full are extremely expensive in operation costs, and I am running them myself on planned conditions. But that's my case.=20 Scripting this, with a cron hand,=20 would be ok, and I guess that is something you will prolly find out there.= =20 The reindex process is something to watch at, and not to leave running=20 during the night, without some bird eye on it, at least on my scenery. Best wishes, Guido. On 9/16/05, Warren Snelling wrote: >=20 > Guido, >=20 > Thanks for the pointers. I have routinely vacuumed the db, but not > using full. >=20 > By itself, the "vacuum full" run overnight did not free the space I was > expecting - du still showed the old database taking about 12 Gb disk, > and the fresh databases taking about 6.5 Gb. Running "reindex" on one > large table freed ~5 Gb, so the old db now takes 7 Gb. Reindexing the > remaining tables should get the databases to the same size. >=20 > Would you suggest scripting the vacuum full / reindex process to run > periodically (once a week or so)? Right now disk space on this machine > is not an issue, but vacuuming and reindexing occasionally should keep > the db from consuming too much excess space. >=20 > Thanks again, > warren >=20 > > still thinking on this topic, read about the REINDEX command, that > > will recreate your indexes, per table or per database. > > > > Regards, > > Guido. > > > > > > On 9/15/05, Guido Barosio wrote: > > Hi Warren > > > > On the space issue, seems at a first sight that the old db > > needs a vacuum 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 understand better which are the effects of > > the different vacuum modes. This will clear out your doubts on > > why such amount of space is being allocated, but not being > > used. > > > > My two cents there. > > > > Best wishes, > > Guido. > > > > On 9/15/05, Warren Snelling > > wrote: > > All, > > > > 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, > > > > pg_dumpall -p 5432 -c | psql -p 5433 template1 > > > > 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. > > > > 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? > > > > 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... > > > > Thanks, > > warren > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > > > > > > > -- > > "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. > > > > > > > > -- > > "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 --=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_32035_7700708.1126888016843 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Warren,

Actually, in my particular case (old pgsql version, and a big db), reindex and vacuum full are extremely expensive in operation costs,
and I am running them myself on planned conditions. But that's my case. Scr= ipting this, with a cron hand,
would be ok, and I guess that is something you will prolly find out there. =

The reindex process is something to watch at, and not to leave running duri= ng the night, without some bird eye on it,
at least on my scenery.

Best wishes,
Guido.


On 9/16/05, Warren Snelling <snelling@email.marc.usda.gov> wrote:
Guido,

Thanks for the pointers.  I have routinely vacuumed= the db, but not
using full.

By itself, the "vacuum full&quo= t; run overnight did not free the space I was
expecting - du still showe= d the old database taking about 12 Gb disk,
and the fresh databases taking about 6.5 Gb.  Running "r= eindex" on one
large table freed ~5 Gb, so the old db now takes 7 G= b.  Reindexing the
remaining tables should get the databases t= o the same size.

Would you suggest scripting the vacuum full / reindex process to ru= n
periodically (once a week or so)?  Right now disk space on t= his machine
is not an issue, but vacuuming and reindexing occasionally s= hould keep
the db from consuming too much excess space.

Thanks again,
wa= rren

> still thinking on this topic, read about the REINDEX comma= nd, that
> will recreate your indexes, per table or per database.
>
> Regards,
> Guido.
>
>
> On 9/15/05= , Guido Barosio <gbarosio@gmail.co= m> wrote:
>         Hi= Warren
>
>         On = the space issue, seems at a first sight that the old db
>         needs a vacuum ful= l to free some room.
>        = ; Try to identify your biggest tables, isolate the vacuum
> &nbs= p;       against them, and then escalate to t= he whole db, with a vacuum
>       = ;  full.
>
>         Thinking t= hat you should double check the vacuum
>     = ;    documentation, to understand better which are the effec= ts of
>         the different= vacuum modes. This will clear out your doubts on
>         why such amount of= space is being allocated, but not being
>    &nb= sp;    used.
>
>     &n= bsp;   My two cents there.
>
>    = ;     Best wishes,
>     = ;    Guido.
>
>     &nb= sp;   On 9/15/05, Warren Snelling < snelling@email.marc.usda.go= v>
>         wrote:>           &nbs= p;     All,
>
>    &nbs= p;            Couple questions from to a recent upgrade from 7.4.8
>  &nb= sp;            =   to 8.0.3.  With
>      = ;           servers for both versions running on the same
>   &nbs= p;             = machine,
>
>        &nb= sp;          pg_dumpall -p 5432 -c | psql -p 5433 template1
>
>  &= nbsp;           &nbs= p;  appears to have migrated the databases smoothly.  At
> = ;            &n= bsp;   least I didn't
>      =            notice any errors in the process, and all the data
>   = ;            &n= bsp; appears to be there.
>
>     &nbs= p;           The one troubling thing is the new 8.0 databases take
>  &n= bsp;            = ;  about half the
>       &nb= sp;         disk space as the 7.4 databases.  Similar dump | psql
>&nbs= p;            &= nbsp;   to recreate the
>     &nbs= p;           databases on other machines running 7.4.7 and 7.4.8
>  &nbs= p;            &= nbsp; show the same thing
>       =           - the fresh copies take half the space of the
>   &nbs= p;             old.  What might be
>      &n= bsp;          happening with the old db, so it takes so much more
>  &nbs= p;            &= nbsp; space?  Could half
>      &nb= sp;          the data be missi= ng?
>
>         &n= bsp;       What's the best way to move data from 8.0 to 7.4?  The
>&nb= sp;            =     8.0 pg_dump
>      &= nbsp;          writes a dump that doesn't restore with 7.4 psql, and
>  &n= bsp;            = ;  7.4 pg_dump
>        =          doesn't seem to handle an 8.0 database.  At least I
> =             &nb= sp;   haven't found the
>     &nbs= p;           right switch= es...
>
>         =         Thanks,
>   = ;            &n= bsp; warren
>
>
>           &= nbsp;     ---------------------------(end of
>&nb= sp;            =     broadcast)---------------------------
>     =             TIP 2: Don't 'kill -9' the postmaster
>
>
>
>
&g= t;         --
>  &n= bsp;      "Adopting the position that you are= smarter than an automatic
>       = ;  optimization algorithm is generally a good way to achieve less
>         performance, not m= ore" - Tom Lane.
>
>
>
> --
> "Adop= ting the position that you are smarter than an automatic
> optimizati= on algorithm is generally a good way to achieve less
> performance, not more" - Tom Lane.

=


--
"Adopting the position that you are s= marter than an automatic
optimization algorithm is generally a good way = to achieve less
performance, not more" - Tom Lane. ------=_Part_32035_7700708.1126888016843--