Thread: 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