A J <s5aly(at)yahoo(dot)com> wrote:
> I am running a test to find the impact of corrupt indexes on
> queries. I corrupted the index file but observed that the queries
> continued to perform without issues. It was only on restart of the
> database that postgres realized that the indexes are corrupt and
> my queries started failing (with error like: ERROR: invalid page
> header in block 0 of relation base/16384/16399)
It sounds like you corrupted the disk image of the index, but the
index was already cached in PostgreSQL shared memory, so the problem
didn't appear. You restarted PostgreSQL, so it had to read from the
OS cache or the actual disk, which caused it to see the bad pages.
> 1. How do I remove the indexes from memory ? I wish to stimulate
> the condition when indexes are no longer in database but the
> server has not restarted and see the effects of corrupt indexes.
You would need to create a workload which would need the shared
memory of other purposes, so that the index pages eventually got
evicted. Using a small shared_buffers setting would make this
easier.
> 2. Is there a more proactive and realtime way to detect corruption
> of indexes ?
I suppose that if PostgreSQL didn't cache data, but always read from
disk it would notice sooner when something mangled the disk image,
but performance would be horrible. Without reading the disk, you
can't tell what's on the disk, so you can't tell if it is wrong.
-Kevin