Обсуждение: FATAL: could not open relation xxx: No such file or directory

Hello all

my struggle with the database continues (see earlier thread titled "too many trigger records found for relation xyz").

Today, I created yet another to table to the same database. Everything went ok, no errors or anything, but when I checked pg_tables -view I saw two tables with the same name. Instantly I queried pg_class and yes there was again two tables with same oid. I dropped the table before anything more serious could happen, but then postgres started to complain of "cache lookup failed for relation ...". I disconnected my psql session and tried to reconnect but failed to do so:

2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL:  could not open relation 1663/16386/544592: No such file or directory

Indeed, there is no such file in that directory. I'm guessing that file is connected to the table I just dropped. Now, is there anything to do to get the database back online? I can still connect to other databases in the same instance

On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio <mpartio@gmail.com> wrote:

Hello all

my struggle with the database continues (see earlier thread titled "too many trigger records found for relation xyz").

Today, I created yet another to table to the same database. Everything went ok, no errors or anything, but when I checked pg_tables -view I saw two tables with the same name. Instantly I queried pg_class and yes there was again two tables with same oid. I dropped the table before anything more serious could happen, but then postgres started to complain of "cache lookup failed for relation ...". I disconnected my psql session and tried to reconnect but failed to do so:

2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL:  could not open relation 1663/16386/544592: No such file or directory

Indeed, there is no such file in that directory. I'm guessing that file is connected to the table I just dropped. Now, is there anything to do to get the database back online? I can still connect to other databases in the same instance

The cure was to create file 1663/16386/54459 8K in size with dd. The file in question was in fact the oid index on pg_class -- I had issued a REINDEX on pg_class just a moment before and apparantly something went wrong and the system lost track of the index. There was also two entries in pg_index for index pg_class_oid_index. After I removed the other entry and reindexed pg_class and pg_index, everything seems to be working ok. All the symptoms indicate that perhaps a xid wraparound had happened, but there is no such warning in logs and age(datfrozenxid) went never higher than say 250,000,000. Does anybody have a clue what might have happene

On Thu, Apr 17, 2008 at 3:38 PM, Mikko Partio <mpartio@gmail.com> wrote:

>
> 2008-04-17 13:05:30 EEST [8435]: [32-1] ERROR:  could not open relation
> 1663/16386/359232: No such file or directory
>

Looks like a corrupt index to me. DId you try REINDEX on the table ?

"Mikko Partio" <mpartio@gmail.com> writes:
> Seems like the whole db is falling apart.

I think you've got really serious filesystem-level problems.  Have you
tried running any hardware diagnostics?  Are you sure you're using a
stable kernel version?

On Thu, Apr 17, 2008 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Mikko Partio" <mpartio@gmail.com> writes:
> Seems like the whole db is falling apart.

I think you've got really serious filesystem-level problems.  Have you
tried running any hardware diagnostics?  Are you sure you're using a
stable kernel version?

I run fsck on the filesystem (gfs) -- no problems found. The disks are from a san and the diagnostic programs say there's nothing wrong. I also have other db clusters running on different filesystems (also gfs) and I have never had any problems with them.

"Mikko Partio" <mpartio@gmail.com> writes:
> On Thu, Apr 17, 2008 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> I think you've got really serious filesystem-level problems.  Have you
>> tried running any hardware diagnostics?  Are you sure you're using a
>> stable kernel version?

> I run fsck on the filesystem (gfs) -- no problems found. The disks are from
> a san and the diagnostic programs say there's nothing wrong. I also have
> other db clusters running on different filesystems (also gfs) and I have
> never had any problems with them.

Some RAM checks wouldn't be out of place either.

On Thu, Apr 17, 2008 at 7:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Mikko Partio" <mpartio@gmail.com> writes:
> On Thu, Apr 17, 2008 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> I think you've got really serious filesystem-level problems.  Have you
>> tried running any hardware diagnostics?  Are you sure you're using a
>> stable kernel version?

> I run fsck on the filesystem (gfs) -- no problems found. The disks are from
> a san and the diagnostic programs say there's nothing wrong. I also have
> other db clusters running on different filesystems (also gfs) and I have
> never had any problems with them.

Some RAM checks wouldn't be out of place either.

What I had twice (on different customers, once SCSI once SATA) is that a
broken hard disk reports no errors, but delivers different data than
what was written before. Very nasty, as the RAID controller doesn't see
any problem, and destroys even the good harddisks data after the next
write, because the read data is already broken.