Thread: Table corruption and other questions

Hi:
    I'm using postgresql 6.5.1 with Linux Mandrake 6.1. I have two cases
of table corruption related to hardware failure. I have a server with
several clients generating nearly 10000 insertions a day.  In both cases
one row of the table got corrupted, and caused the backend to crash when
trying to vacuum or execute pg_dump. SELECT * crashed the backend, too,
but we could do SELECTs on specific columns. The only way we could
recover the data was to do SELECT * FROM table WHERE id > n;
where n is the id of the corrupted row and was found by trial and error.

    Is there a tool to repair a corrupted table?


    Another question:
    I'm using rules to log changes in several tables. I need this
because I have several servers in different locations, and I need to
synchronize them off line. The documentation isn't clear about the
relation between rules and permissions. The documentation i read implies
that a rule has the permissions of the user who is triggering its
execution. But it seems that this is not true, because a rule can insert
on a table over which the user has no privileges. This is exactly what i
need, but i'm not sure if this will be maintained in future versions. On
the other side, if a rule uses a function, it losts all its privileges,
and it can not insert in other tables if the user has no rights over
them.
    Does anyone know if this is true, or i'm misunderstanding something?

    TIA
    Miguel Montes

Miguel Montes wrote:
>     I'm using postgresql 6.5.1 with Linux Mandrake 6.1. I have two cases
> of table corruption related to hardware failure.

If the table itself is really messed up it can be tough to fix.
Sometimes it is possible to do a pg_dump of the table and reload --
otherwise it is a restore from the back-ups.

However, if you have indexes on the table, try dropping them all and
re-building them. I have never actually seen a corrupted table, but
index corruption with Postgres does occur. Also check for inidexes that
you may not have knowingly created, such as *_pkey or *_key ffor
primary_key or unique fields.

Hope this helps and good luck,

Adriaan