Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id EED632E004C for ; Mon, 12 May 2008 04:25:40 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65668-01 for ; Mon, 12 May 2008 04:25:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by developer.postgresql.org (Postfix) with ESMTP id 643542E004A for ; Mon, 12 May 2008 04:25:36 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so3272241wag.5 for ; Mon, 12 May 2008 00:25:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=iO3Wxxi9QQ7CC+N030SisX+KNmWpJKlE474Vt+SJqLA=; b=kHJ0O8kho/tn6/YN3cuYi0PzPkqk9YYMO8Cv22WGafz40Em5fXUl1+LrUbrjuOr2zZ98dURKsQT7awGez9rGQt6J6pak214lgrHfYVmwDVhiE1slJcgk3rNZi4lTAk5dQPoBiyYqgJE68fGt7L8YEHUDU+JnjMjJXXO5peuwYtM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=v4Ka/9z2/mtgLHdsv9suOTKcKokx7jeXB/bfVSK3lmPYBB0TlDJodM5QCTriqJm8KzP0mulmgpNCw2nYD0Gr1V3LRYqkTGw+nDPLSQhw5GW1t30dBkL4r2xaHazdaIzWI8ihyMqVP9Z7YDmcHMKt22hNE0glpS7J3OumuWs0CVw= Received: by 10.142.135.9 with SMTP id i9mr3126639wfd.286.1210577134837; Mon, 12 May 2008 00:25:34 -0700 (PDT) Received: by 10.142.88.10 with HTTP; Mon, 12 May 2008 00:25:34 -0700 (PDT) Message-ID: <8d01be800805120025u3a49fa39rc7033a2480b4982f@mail.gmail.com> Date: Mon, 12 May 2008 09:25:34 +0200 From: "James Farrugia" To: "Tom Lane" Subject: Re: Orphan TOAST object Cc: pgsql-admin@postgresql.org In-Reply-To: <20763.1210526763@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19_13322358.1210577134809" References: <8d01be800805110946r792efacdp3dbe02829b6da7a9@mail.gmail.com> <20553.1210525788@sss.pgh.pa.us> <8d01be800805111017w6910acdapaf66f0b56908d36e@mail.gmail.com> <20763.1210526763@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200805/86 X-Sequence-Number: 29170 ------=_Part_19_13322358.1210577134809 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Tom, First of all thanks for the immediate replies! Was actually waiting for the right moment to upgrade to 8.3 but migrating a live 1Tb database is a bit daunting especially if you have never done it before (as in my case). If I'm not mistaken i can upgrade to the latest minor version without having to dump and restore so I'll do that. One last thing...can we run into data-loss problems with successfully vacuumed tables even if there is one unvacuumed database object; what would have happened if I ignored to vacuum that rogue pg_toast (which was the only unvacuumed object within the entire database)? Thanks again. James On 5/11/08, Tom Lane wrote: > > "James Farrugia" writes: > > I'm running 8.2.1. > > You really need to update to 8.2.latest. There are several known > data-corruption problems in 8.2.1, and it seems possible that one of > them ate the pg_depend row you needed. > > > I cleanly forgot about pg_depend! > > Even after re-indexing I wasn't able to find an entry in pg_depend > having > > the TOAST's OID. I guess that by creating foo again and linking > > pg_toast_xxx with foo in pg_depend by hand i can make it go away. > > Yeah, that's probably the cleanest recovery strategy. > > regards, tom lane > ------=_Part_19_13322358.1210577134809 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi Tom,
 
First of all thanks for the immediate replies! 
Was actually waiting for the right moment to upgrade to 8.3 but migrating a live 1Tb database is a bit daunting especially if you have never done it before (as in my case).  If I'm not mistaken i can upgrade to the latest minor version without having to dump and restore so I'll do that.
 
One last thing...can we run into data-loss problems with successfully vacuumed tables even if there is one unvacuumed database object; what would have happened if I ignored to vacuum that rogue pg_toast (which was the only unvacuumed object within the entire database)? 
 
Thanks again.

James
 
On 5/11/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"James Farrugia" <james.farrugia@gmail.com> writes:
> I'm running 8.2.1.

You really need to update to 8.2.latest.  There are several known
data-corruption problems in 8.2.1, and it seems possible that one of
them ate the pg_depend row you needed.

> I cleanly forgot about pg_depend!
> Even after re-indexing I wasn't able to find an entry in pg_depend having
> the TOAST's OID.  I guess that by creating foo again and linking
> pg_toast_xxx with foo in pg_depend by hand i can make it go away.

Yeah, that's probably the cleanest recovery strategy.

                       regards, tom lane

------=_Part_19_13322358.1210577134809--