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:
Show quoted text
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