destroyed db/index (corruption)

Поиск
Список
Период
Сортировка
От Michael Monnerie
Тема destroyed db/index (corruption)
Дата
Msg-id 200702051500.19196@zmi.at
обсуждение исходный текст
Ответы Re: destroyed db/index (corruption)  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Список 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 it doesn't
look like a hardware problem.

I could believe it's a software problem. But which? 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'

log_filename = 'postgresql.%Y'

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

Вложения

В списке pgsql-admin по дате отправления:

Предыдущее
От: Michael Monnerie
Дата:
Сообщение: destroyed db/index
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: destroyed db/index (corruption)