Re: destroyed db/index (corruption)

Στις Δευτέρα 05 Φεβρουάριος 2007 16:00, ο/η Michael Monnerie έγραψε:
> 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).

This ERROR is normally thrown when you *try* to violate a unique constraint.
Can you demonstrate the presence of the UNIQUE constraint on your table
and the pair of identical key values rows?
Post a description (\d) of your table, and then the select stmt which return
the 2 bad rows.

I might be wrong, but i would bet the whole issue began because
some rows were inserted by a client (other than the reloading psql) during
reload but before the UNIQUE KEY DDL
was executed, or smth like that, or simply because this bayes_seen value is
attempted to be inserted again
(for reasons beyond our scope)
>
> 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

--
Achilleas Mantzios