[GENERAL] REINDEX "is not a btree"

[Vanessa Lopez]

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

[Craig Ringer]

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

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[decibel]

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 dec...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

[Vanessa Lopez]

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

[Tom Lane]

Vanessa Lopez <v.l...@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

[Alan Hodgson]

On Friday 10 July 2009, Vanessa Lopez <v.l...@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.

[decibel]

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.