Обсуждение: REINDEX "is not a btree"

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