psql: FATAL 1: Index pg_class_relname_index is not a btree

I came in this morning to find my network monitor reporting a problem
with one of our databases.

The error was:

FATAL 1: Index pg_class_relname_index is not a btree

I was able to access all the other databases, so I dumped them each
(pg_dumpall conects first to template1, so that was not an option).

Then I shut down and restarted the database. It came back up clean. But
when I tried to do pg_dumpall on that, there were no tables present.
Sure enough, a select on pg_database revealed only one -- template1.

I can connect to the other database:

template1=> select * from pg_database;
datname | datdba | encoding | datpath
-----------+--------+----------+-----------
template1 | 10002 | 0 | template1
(1 row)

template1=> \connect biobase
You are now connected to database biobase.
biobase=>

But I can't do anything.

I should also note that the data directory is a symlink - the OS was
updraded over the weekend onto a new disk. The database directory itself
was then mounted from the old disk. Not sure this is relevant though -
the DB was fine for more than 48 hours after the switchover.

This is RedHat Linux 6.2 with the PostgreSQL 7.0.2-2 RPM. If the
hardware is relevant, it is a dual processor (Intel Xeon) machine -- the
OS now lives on an IDE drive, while the data is on a SCSI drive. Prior
to Saturday, the OS and data both lived on the SCSI drive, and the OS
was RedHat 6.0 -- the database has been 7.0.2 for some time now, that
was not upgraded over the weekend (although the bits were freshened, the
version is exactly the same).

Has anyone seen this before? Any ideas?

--
Karl DeBisschop kdebisschop(at)alert(dot)infoplease(dot)com
Family Education Network/Information Please http://www.infoplease.com
Netsaint Plugin Developer kdebisschop(at)users(dot)sourceforge(dot)net

[ Sorry for slow response, but this just arrived here. Are you
subscribed to pgsql-general? It looks to me like Marc just got round
to approving a week or so's worth of nonmember submissions to the
lists... ]

Karl DeBisschop <kdebisschop(at)range(dot)infoplease(dot)com> writes:
> I came in this morning to find my network monitor reporting a problem
> with one of our databases.
> The error was:
> FATAL 1: Index pg_class_relname_index is not a btree

Yipes! That message indicates that the page-zero header of this
index got clobbered, which is bad enough in any case, but especially
so on a critical system index like this one. The other symptoms
you mention sound like pg_database has been clobbered as well.

I haven't heard of anything like that before. Considering that you
also mention recent hardware upgrades, I have to wonder if the hardware
dropped the ball. I don't like blaming hardware problems (for a
software guy, it feels too much like passing the buck) but it seems
like a pretty likely bet here.

If you haven't given up hope and reloaded in the past week, I'd be
willing to help poke into the remains, on the chance that we
might discover something useful. Let me know off-list.

regards, tom lane

Karl DeBisschop wrote:

> I came in this morning to find my network monitor reporting a problem
> with one of our databases.
>
> The error was:
>
> FATAL 1: Index pg_class_relname_index is not a btree
>
> I was able to access all the other databases, so I dumped them each
> (pg_dumpall conects first to template1, so that was not an option).
>
> Then I shut down and restarted the database. It came back up clean. But
> when I tried to do pg_dumpall on that, there were no tables present.
> Sure enough, a select on pg_database revealed only one -- template1.
>
> I can connect to the other database:
>
> template1=> select * from pg_database;
> datname | datdba | encoding | datpath
> -----------+--------+----------+-----------
> template1 | 10002 | 0 | template1
> (1 row)
>
> template1=> \connect biobase
> You are now connected to database biobase.
> biobase=>
>
> But I can't do anything.
>
> I should also note that the data directory is a symlink - the OS was
> updraded over the weekend onto a new disk. The database directory itself
> was then mounted from the old disk. Not sure this is relevant though -
> the DB was fine for more than 48 hours after the switchover.
>
> This is RedHat Linux 6.2 with the PostgreSQL 7.0.2-2 RPM. If the
> hardware is relevant, it is a dual processor (Intel Xeon) machine -- the
> OS now lives on an IDE drive, while the data is on a SCSI drive. Prior
> to Saturday, the OS and data both lived on the SCSI drive, and the OS
> was RedHat 6.0 -- the database has been 7.0.2 for some time now, that
> was not upgraded over the weekend (although the bits were freshened, the
> version is exactly the same).
>
> Has anyone seen this before? Any ideas?
>
> --
> Karl DeBisschop kdebisschop(at)alert(dot)infoplease(dot)com
> Family Education Network/Information Please http://www.infoplease.com
> Netsaint Plugin Developer kdebisschop(at)users(dot)sourceforge(dot)net

This reminds me of something : are there any plans to include roll forward
support on postgreSQL ?
Jurgen