Thread: REINDEX "is not a btree"
Hello!
I got into a situation I don't know how the get out ..
First, I could not access to my biggest database in postgre anymore because it suddenly gave the error (after long time working with no problems)
ERROR: could not open relation 1663/392281/530087: No such file or directory
After trying with several backups with no success, I did a vacuum and I tried to REINDEX the database (in the standalone back-end).
Unfortunately the process was interrupted, and when I tried to start postgres again I got the error:
'SQL select * from pg_database order by datname failed : index "pg_authid_rolname_index" is not a btree"
I connected as a standalone mode again to REINDEX the database:
pg_ctl stop -D /data/pgsql/data
/usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes
REINDEX database dbpedia_infoboxes
The REINDEX was successful this time but I was still having the "is not a btree" problem, so I tried again with:
pg_ctl stop -D /data/pgsql/data
/usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes
REINDEX SYSTEM dbpedia_infoboxes
The process finish, but I was still having the "is not a btree" problem.
And even more, now not only the same problem "is not a btree" is still there, but also I can not connect in the standalone mode anymore:
bash-3.2$ /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes
FATAL: index "pg_database_datname_index" is not a btree
(I tried with other databases as well and the same)
I don't know much about postgre, I have no clue what else I can do.
Please, please any help is very very much appreciated I have lots of databases and months of work in postgre (also lots of backups for the data in /data) but I don't know how to make postgres to work again.
(it is working in unix red hat).
Millions of thanks in advance, solving this problem is crucial for me.
Vanessa
On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: > I don't know much about postgre, I have no clue what else I can do. > Please, please any help is very very much appreciated I have lots of > databases and months of work in postgre (also lots of backups for the > data in /data) When you say "in /data", do you mean the directory that contains the directories "pg_xlog", "base", "global", "pg_clog", etc ? Did you back up and restore the WHOLE data directory at once? Or did you restore only parts of it? When restoring, did you: - Stop PostgreSQL - Check with "ps" to ensure no 'postgres' or 'postmaster' instances were still running - Move the old data directory out of the way - Copy the backup data directory from your backups - start PostgreSQL ? Have you checked the file system and disk to make sure they're OK? -- Craig Ringer
On Jul 4, 2009, at 8:06 AM, Craig Ringer wrote: > On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: >> I don't know much about postgre, I have no clue what else I can do. >> Please, please any help is very very much appreciated I have lots of >> databases and months of work in postgre (also lots of backups for the >> data in /data) > > When you say "in /data", do you mean the directory that contains the > directories "pg_xlog", "base", "global", "pg_clog", etc ? > > Did you back up and restore the WHOLE data directory at once? Or > did you > restore only parts of it? And how exactly did you make the backups? You can't simply take a filesystem copy of a running database; that won't work. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Hello, Thanks for all your answers! I discovered the table that was causing the error, delete it and create it again (I miss some data but at least everything else is working now) Yes, for the backup we copy everything we had under /data (the directory containing "base", "global", and so on ... we do backups every day from the server), and then we restore the whole /data directory at once ... but it did not solve the problem .. What do you mean by we can't simply take a filesystem copy of a running database? :-O ... How should we then do the backups (so next time I will not have the same problem again) ? Millions of thanks again! Vanessa On 10 Jul 2009, at 04:06, decibel wrote: > On Jul 4, 2009, at 8:06 AM, Craig Ringer wrote: >> On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: >>> I don't know much about postgre, I have no clue what else I can do. >>> Please, please any help is very very much appreciated I have lots of >>> databases and months of work in postgre (also lots of backups for >>> the >>> data in /data) >> >> When you say "in /data", do you mean the directory that contains the >> directories "pg_xlog", "base", "global", "pg_clog", etc ? >> >> Did you back up and restore the WHOLE data directory at once? Or >> did you >> restore only parts of it? > > > And how exactly did you make the backups? You can't simply take a > filesystem copy of a running database; that won't work. > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > >
Vanessa Lopez <v.lopez@open.ac.uk> writes: > What do you mean by we can't simply take a filesystem copy of a > running database? :-O ... How should we then do the backups (so next > time I will not have the same problem again) ? Read the fine manual ... http://www.postgresql.org/docs/8.3/static/backup.html Section 24.2 explains the pitfalls of trying to use a filesystem-level backup. It is possible to do, but you have to be very very careful to get a consistent snapshot. regards, tom lane
On Friday 10 July 2009, Vanessa Lopez <v.lopez@open.ac.uk> wrote: > What do you mean by we can't simply take a filesystem copy of a > running database? :-O ... How should we then do the backups (so next > time I will not have the same problem again) ? There is extensive documentation on how to do backups. For filesystem backups, see PITR. You might also want to examine all your backup strategies - most running applications are not happy about being backed up without taking special steps to ensure data consistency. -- Anyone who believes exponential growth can go on forever in a finite world, is either a madman or an economist.
On Jul 10, 2009, at 6:47 AM, Vanessa Lopez wrote: > I discovered the table that was causing the error, delete it and > create it again (I miss some data but at least everything else is > working now) > > Yes, for the backup we copy everything we had under /data (the > directory containing "base", "global", and so on ... we do backups > every day from the server), and then we restore the whole /data > directory at once ... but it did not solve the problem .. Given the problems you've had, I strongly suggest you take a pg_dump of the database, restore that dump, and use the restored copy. I bet there's probably other problems lurking in your database. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828