Обсуждение: index "pg_authid_rolname_index" is not a btree

Hard disk containing PostgreSql 8.1 database on Windows crashes and there
was no new
backup copy.

I installed 8.1.9 to new computer and copied data directory from crashed
disk to it.
data directory contains a lot of files with a lot of data.

Trying to connect to template0 or any other database in this cluster causes
error

Error connecting to the server: FATAL:  index "pg_authid_rolname_index" is
not a btree

How to recover data from this cluster ?

Andrus.

Andrus Moor wrote:
> Hard disk containing PostgreSql 8.1 database on Windows crashes and
> there was no new
> backup copy.
>
> I installed 8.1.9 to new computer and copied data directory from
> crashed disk to it.
> data directory contains a lot of files with a lot of data.
>
> Trying to connect to template0 or any other database in this cluster causes
> error
>
> Error connecting to the server: FATAL:  index "pg_authid_rolname_index" is
> not a btree

You can get around that particular problem by reindexing the pg_authid
table.  But my guess is that you'll find that there's corruption
elsewhere that's not so easily recoverable ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro,

> You can get around that particular problem by reindexing the pg_authid
> table.  But my guess is that you'll find that there's corruption
> elsewhere that's not so easily recoverable ...

Thank you.
reindexing system tables and whole database succeeds.
After that I can connect to database containing data to recover.

However pg_dump fails:

bin\pg_dump -f recover.backup -i -v -F c -h localhost -p 5433 -U postgres
mydb
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  could not identify an ordering
operator for type name
HINT:  Use an explicit ordering operator or modify the query.
pg_dump: The command was: SELECT tableoid, oid, conname,
pg_catalog.pg_get_constraintdef(oid) AS consrc FROM pg_catalog.pg_constraint
WHERE contypid = '10635'::
pg_catalog.oid ORDER BY conname
pg_dump: *** aborted because of error

How to recover data from this database ?
Should I re-create ordering operator in some way ?

Andrus.

"Andrus Moor" <kobruleht2@hot.ee> writes:
> Alvaro,
>> You can get around that particular problem by reindexing the pg_authid
>> table.  But my guess is that you'll find that there's corruption
>> elsewhere that's not so easily recoverable ...

> Thank you.
> reindexing system tables and whole database succeeds.
> After that I can connect to database containing data to recover.

> However pg_dump fails:

> pg_dump: Error message from server: ERROR:  could not identify an ordering
> operator for type name

Alvaro was right --- you've got damage in the system catalogs, not just
their indexes.  This looks like missing entries in pg_amop.  (You did
say you reindexed all the system catalogs, right?  If not it's possible
this is only index damage, but I'm not very hopeful.)

I suspect that if you did get to the point of being able to run pg_dump
without error, you'd find just as much damage to the user data.  I'm
afraid this database is toast and you should write it off as a learning
experience.  Hardware fails, you need backups.

            regards, tom lane

Tom,

Thank you.

> Alvaro was right --- you've got damage in the system catalogs, not just
> their indexes.  This looks like missing entries in pg_amop.

postgres -D data mydb

PostgreSQL stand-alone backend 8.1.9
backend> select * from pg_amop
         1: amopclaid   (typeid = 26, len = 4, typmod = -1, byval = t)
         2: amopsubtype (typeid = 26, len = 4, typmod = -1, byval = t)
         3: amopstrategy        (typeid = 21, len = 2, typmod = -1, byval =
t)
         4: amopreqcheck        (typeid = 16, len = 1, typmod = -1, byval =
t)
         5: amopopr     (typeid = 26, len = 4, typmod = -1, byval = t)
        ----

pg_amop in mydb contains 5 rows.
pg_amop in  template1 database contains large number of rows.
mydb does not contain user-defined operators.

How to repair pg_amop in mydb ?

> (You did
> say you reindexed all the system catalogs, right?  If not it's possible
> this is only index damage, but I'm not very hopeful.)

reindex system mydb
reindex database mydb

complete without errors.

> I suspect that if you did get to the point of being able to run pg_dump
> without error, you'd find just as much damage to the user data.  I'm
> afraid this database is toast and you should write it off as a learning
> experience.  Hardware fails, you need backups.

Backup is 4.2 GB and is corrupted after 2 GB as I described in other thread.
Also, backup is too old.
Most of backup size contains few big tables which are not required to
recover.
I ran truncate commands for those tables. This reduces whole data
directory size to 1.2 GB in uncompressed form.

I know which tables contain data to be recovered.

How to dump those tables out ?

Andrus.

"Andrus Moor" <kobruleht2@hot.ee> writes:
> pg_amop in mydb contains 5 rows.
> pg_amop in  template1 database contains large number of rows.
> mydb does not contain user-defined operators.

> How to repair pg_amop in mydb ?

Well, you could try copying the physical file for pg_amop from template1
to mydb (and then reindexing it again).  I am not holding out a lot of
hope though.  I think you're most likely going to run into a dead end,
unfixable problem before you get any data out.

> Most of backup size contains few big tables which are not required to
> recover.

Maybe you should forget about pg_dump and just see if you can COPY
the tables you care about.

            regards, tom lane