On Mon, Sep 12, 2011 at 8:03 AM, Dylan Adams <dylan.adams.work@gmail.com> wrote:
> We moved to PostgreSQL about 2 years ago and have been very happy with it
> overall. The only major issue that we've had is intermittent index corruption.
> This manifests itself as either "duplicate key value violates unique constraint"
> or "could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192
> bytes." REINDEXing the table solves the problem. We do sometimes have bugs that
> cause unique index violations, so the first error is especially annoying.
>
> We've encountered the problem with both JDBC and libpq clients. The problem has
> persisted through upgrades to our database servers (from 32bit CentOS 5.3 with
> PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock
> kernels).
>
> Our database servers are fully virtualized, running under VMware Server on Dell
> PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6),
> configured for RAID 10. We also experienced the problem when we had physical
> database servers. Servers all have 2 CPUs.
Are you sure you aren't having either server or RAID problems of some
kind? Single bit memory errors or bad sectors not getting remapped
before corrupting data etc? Have you torture tested your hardware to
ensure it's rock solid stable? Dell's insistence on using non
buffered memory has cause me untold problems with single bit errors in
the past. I don't know if they still use unbuffered memory in their
servers or not as I gave up on Dell three or four years ago for
servers and support.