destroyed db/index

From: Michael Monnerie <michael(dot)monnerie(at)it-management(dot)at>
To: pgsql-admin(at)postgresql(dot)org
Subject: destroyed db/index
Date: 2007-02-05 12:51:43
Message-ID: 200702051351.43567@zmi.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

dear list,

I've found database corruptions occuring with openSUSE 10.1, postgreSQL
version # rpm -qa|grep postg
postgresql-libs-8.1.4-1.2
postgresql-server-8.1.4-1.2
postgresql-8.1.4-1.2

I found this in database logs:
2007-02-05 13:15:51 CET DB=bayes_pg3 HOST=195.202.170.130(54789)
SESSTRT=2007-02-04 05:01:15 CET FEHLER: duplizierter Schlüssel
verletzt Unique-Constraint »bayes_seen_pkey«

translated it says "duplicate key violates unique constraint", and it's
true, I have 2 rows with the same Primary Key in that table (make dump,
then reload fails on it).

The DB is a bayes DB for spamassassin. I've had this error before, and
dumped/edited dump/reloaded the db. But it keeps coming again. So there
might be hardware or software problems.

The hardware is a server running one AMD Opteron dual core 2210, with
8GB RAM, and VMware. We have www, db, mailserver and mailrelays running
within a VM, and the only problem I see is with the db.

So I could believe it's a software problem. But what? Could a bad config
cause such problems? I've set this in postgresql.conf:

listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 50000
max_prepared_transactions = 0
work_mem = 128000
maintenance_work_mem = 128000
max_fsm_pages = 60000
max_fsm_relations = 1000
max_files_per_process = 5000
vacuum_cost_delay = 100
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 10000
bgwriter_delay = 1000
bgwriter_lru_percent = 5.0
bgwriter_lru_maxpages = 100
bgwriter_all_percent = 1.333
bgwriter_all_maxpages = 100
fsync = off
wal_buffers = 8
commit_delay = 3000
commit_siblings = 25
checkpoint_segments = 4
checkpoint_timeout = 300
checkpoint_warning = 30
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
effective_cache_size = 64000
geqo = on
log_destination = 'stderr'
redirect_stderr = on
log_directory = '/var/log/'
log_filename = 'postgresql.%Y'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 0
log_min_duration_statement = 5000
log_connections = off
log_disconnections = off
log_duration = off
log_line_prefix = '%t DB=%d HOST=%r SESSTRT=%s '
log_statement = 'ddl'
stats_start_collector = on
stats_block_level = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 3600
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 2500
autovacuum_vacuum_scale_factor = 0.4
autovacuum_analyze_scale_factor = 0.2
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi4.asc | gpg --import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0
// Keyserver: www.keyserver.net Key-ID: 1C6FE6B0

Browse pgsql-admin by date

  From Date Subject
Next Message Michael Monnerie 2007-02-05 14:00:14 destroyed db/index (corruption)
Previous Message Simon Kinsella 2007-02-05 00:52:55 Re: Cannot initdb for 8.2.1 - "..../postgres.bki" does not exist