Thread: error connecting to database: could not open relation
error connecting to database: could not open relation
From
"PontoSI - Consultoria, Informática e Serviços LDA"
Date:
Hi, I've had a server crash on a machine running FreeBSD 6 and PG 8.2.5. The database was running at the time of the crash, and probably there was some lost data. When I try to start PG in single mode (and width -P) he complains that he "could not open relation with OID 2661". Because the file is missing, I've tried to create a zeroed file with the size of a page too see if we would eat it so I can dump the data, but without success. I've also tried pgfsck without any luck. Is there any way to get around the missing files and dump directly the values stored inside the actual table files? Regards, João Pinheiro
On Thu, Apr 24, 2008 at 05:44:04AM +0100, "PontoSI - Consultoria, Informática e Serviços LDA" wrote: > > Hi, > I've had a server crash on a machine running FreeBSD 6 and PG 8.2.5. The > database was running at the time of the crash, and probably there was > some lost data. When I try to start PG in single mode (and width -P) he > complains that he "could not open relation with OID 2661". Because the > file is missing, Umm, 2661 is pg_cast_source_target_index, so reindexing pg_cast should do it. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
On Thu, Apr 24, 2008 at 08:17:15AM +0100, "PontoSI - Consultoria, Informática e Serviços LDA" wrote: > > %/usr/local/bin/postgres --single -P -D /usr/local/pgsql/data/ <dbname> > FATAL: XX000: could not open relation with OID 2661 > LOCATION: relation_open, heapam.c:700 > % > > <dbname> is the name of the database. Ok, 2661 is definitly pg_cast_source_target_index on my system so the -P should have caused postgres to ignore it. I'm out of ideas so I'm sending it to the list to see if anyone else has any. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes: > Ok, 2661 is definitly pg_cast_source_target_index on my system so the > -P should have caused postgres to ignore it. That was my first thought too. However, if the pg_internal.init file were missing/broken then the thing would try to rebuild it, and I think that would involve opening most system indexes. Try inserting a correct pg_internal.init file --- ideally from a recent backup, but if you haven't got one then it most likely will work to copy it from another DB in the same installation, or in extremis do an initdb in a temporary directory and take the file from there. Of course, if the data loss extended to more than just index(es) you're still hosed ... regards, tom lane
On Wed, Apr 23, 2008 at 10:44 PM, "PontoSI - Consultoria, Informática e Serviços LDA" <geral@pontosi.pt> wrote: > > Hi, > I've had a server crash on a machine running FreeBSD 6 and PG 8.2.5. The > database was running at the time of the crash, and probably there was some > lost data. When I try to start PG in single mode (and width -P) he complains > that he "could not open relation with OID 2661". Because the file is > missing, I've tried to create a zeroed file with the size of a page too see > if we would eat it so I can dump the data, but without success. I've also > tried pgfsck without any luck. > Is there any way to get around the missing files and dump directly the > values stored inside the actual table files? You're getting a lot of useful on how to get your database up and running again. I'll leave that to the folks you're chatting with. What you need to do next is figure out how it happened. On a machine with solid disk hardware and a properly configured OS this should NOT ever happen. The most common cause of this is an IDA / SATA drive with its write cache enabled in write back mode. There are other possibilities. A properly configured database server should be able to survive having the power cord yanked out in the middle of the busiest period of the day.