Re: Corrupted index on 9.0.3 streaming hot standby

On Thu, Mar 3, 2011 at 11:39 AM, Jakub Ouhrabka
<jakub(dot)ouhrabka(at)comgate(dot)cz> wrote:
> Hi Robert,
>
>> 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.
>
> I think this is the case. We can even reproduce it: take another backup of
> uncorrupted master and the slave is again corrupted.

Well, in that case, I'd *really* like to see you compare the two
files. Maybe you could reproduce the problem, ideally stop both
servers (or at least CHECKPOINT), and then for each block in the
affected index run:

pg_filedump -i -R $BLOCKNUMBER $FILE > b.$BLOCKNUMBER

...on the master and on the standby. Then diff the master version of
each file with the standby version and see what pops out.

> The strange thing is that this only affects streaming replication standby,
> not wal files shipping standby. Maybe we're doing something wrong...

Maybe, but I can't think what would cause this.

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