Re: Corrupted index on 9.0.3 streaming hot standby

2011/2/25 Jakub Ouhrabka <kuba(at)comgate(dot)cz>:
> Hi,
>
> we've found that we have corrupted index on 9.0.3 streaming hot standby.
> Master works ok. There is one more non-streaming standby which is ok as
> well. Platform is 64bit Linux.
>
> Database cluster and this database were created on 9.0.2 and than upgraded
> to 9.0.3. We are not aware of any crash on either master or streaming
> standby but we didn't investigate it deeply yet.
>
> For details about corrupted index see below. The table and index in question
> are mostly read-only (several queries per second) writes happen only few
> times a day.
>
> We've backed up whole cluster and recreated it.
>
> Shall we investigate it further? How? Is it possible that we make some
> mistake when doing initial backup which caused corruption? Is there a way to
> check other indexes?

The obvious way this could happen is if there were a system crash on
the standby. In theory that should be OK too, but if fsync isn't
working properly due to a settings or disk controller configuration
problem or similar, then it might not be.

See also http://wiki.postgresql.org/wiki/Reliable_Writes

If there hasn't been a system crash on the standby, then it's harder
to explain. It'd be interesting to compare the disk blocks in the
index on the standby with the disk blocks in the index on the master
and figure out which ones are different and in what way. pg_filedump
might be useful.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company