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. PostgreSQL is installed from the PGDG RPMs (yum.postgresql.org). Our only non-default postgresql.conf options are the typical tunables: shared_buffers, checkpoint_segments, effective_cache_size, default_statistics_target, etc. WAL settings are defaults (save checkpoint_segments). No clustering, no addons. We've tuned autovacuum to be more aggressive in an attempt to address some bloat issues; this didn't seem to have any impact on the frequency of index corruption. The databases are unloaded nightly. We do a weekly pg_dumpall as an integrity check. We've only seen the problem in our many weekly batch processes. Typically, these processes DELETE a large subset of data from a table and then repopulate with the same values in indexed fields. Depending on the dataset, they run from 30 minutes to 8 hours. These processes are scheduled so that only one program (which is single threaded) is updating any given table at a time. We have 12 database servers and we usually have about 1 incident per week. Sometimes we'll go for weeks without any occurrences, and then we'll have a flurry of them. My primary question: is this normal? There isn't an overwhelming amount of messages in the archives about index corruption, which leads me to think that there's something with our configuration or our processes that is making us more susceptible. Is there something we should be doing to make index corruption less likely? Is there anyway to do an index integrity check so we can be more proactive with REINDEXing? Thanks, dylan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
