Thread: Upgrading postgresql-8.4

Steve Erickson <serickson@digitiliti.com> writes:
> This went well and postgres restarted just fine.  However, now when I execute a pg_dump I get a missing chunk 0 for
pg_toast_2619while querying pg_attribute.  I did a reindex on pg_toast_2619, then tried to VACUUM ANALYZE pg_attribute
butagain got the missing chunk 0 error. 

> Did I miss a step doing the upgrade or recovery attempt, or is the
> data corrupted?

It's corrupt, but fortunately for you, 2619 is pg_statistic which is
eminently discardable data.  Just truncate pg_statistic and you should
be good.  If you aren't immediately abandoning the old database, you
might want to re-ANALYZE everything to reconstruct the stats.

We've seen one or two reports like this before, which makes me think
there might be a reproducible bug lurking somewhere around here; but
I don't suppose you have a recipe for getting a database into this
state ...

            regards, tom lane

Thanks for the reply.  I deleted all rows in pg_statistic and the VACUUM ANALYZE on pg_attribute still fails.  I tried
toreindex pg_toast_2619 and got an error "could not access status of transaction 1493786085.  Could not open file
"pg_subtrans/5909":No such file or directory.  Sure enough, there is no such file - only 5905.   

________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Monday, March 11, 2013 12:10 PM
To: Steve Erickson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Upgrading postgresql-8.4

Steve Erickson <serickson@digitiliti.com> writes:
> This went well and postgres restarted just fine.  However, now when I execute a pg_dump I get a missing chunk 0 for
pg_toast_2619while querying pg_attribute.  I did a reindex on pg_toast_2619, then tried to VACUUM ANALYZE pg_attribute
butagain got the missing chunk 0 error. 

> Did I miss a step doing the upgrade or recovery attempt, or is the
> data corrupted?

It's corrupt, but fortunately for you, 2619 is pg_statistic which is
eminently discardable data.  Just truncate pg_statistic and you should
be good.  If you aren't immediately abandoning the old database, you
might want to re-ANALYZE everything to reconstruct the stats.

We've seen one or two reports like this before, which makes me think
there might be a reproducible bug lurking somewhere around here; but
I don't suppose you have a recipe for getting a database into this
state ...

                        regards, tom lane

Steve Erickson <serickson@digitiliti.com> writes:
> Thanks for the reply.  I deleted all rows in pg_statistic and the VACUUM ANALYZE on pg_attribute still fails.

You would probably need to actually truncate pg_statistic (in a
standalone backend) to make that safe.  If you only want to use DELETE,
I'd try just pg_dump at this point rather than trying to make the
database fully usable again.

> I tried to reindex pg_toast_2619 and got an error "could not access status of transaction 1493786085.  Could not open
file"pg_subtrans/5909": No such file or directory.  Sure enough, there is no such file - only 5905.   

This suggests that your problems are larger than you've indicated so
far.  What happened to this machine anyway?

            regards, tom lane

On 03/11/2013 10:46 AM, Tom Lane wrote:
> Steve Erickson <serickson@digitiliti.com> writes:
>> Thanks for the reply.  I deleted all rows in pg_statistic and the VACUUM ANALYZE on pg_attribute still fails.
>
> You would probably need to actually truncate pg_statistic (in a
> standalone backend) to make that safe.  If you only want to use DELETE,
> I'd try just pg_dump at this point rather than trying to make the
> database fully usable again.
>
>> I tried to reindex pg_toast_2619 and got an error "could not access status of transaction 1493786085.  Could not
openfile "pg_subtrans/5909": No such file or directory.  Sure enough, there is no such file - only 5905. 
>
> This suggests that your problems are larger than you've indicated so
> far.  What happened to this machine anyway?

Possibly being bit by this?:

http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix
>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@gmail.com