index "pg_authid_rolname_index" is not a btree

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