Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2EB8F9FA177 for ; Mon, 5 Feb 2007 10:23:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 80373-04 for ; Mon, 5 Feb 2007 10:22:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 178D39F942B for ; Mon, 5 Feb 2007 10:22:59 -0400 (AST) Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=ip216.internal.net) by matrix.gatewaynet.com with esmtp (Exim 4.62) (envelope-from ) id 1HE4kC-0002OE-9L for pgsql-admin@postgresql.org; Mon, 05 Feb 2007 16:22:56 +0200 From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: pgsql-admin@postgresql.org Subject: Re: destroyed db/index (corruption) Date: Mon, 5 Feb 2007 16:22:55 +0200 User-Agent: KMail/1.9.3 References: <200702051500.19196@zmi.at> In-Reply-To: <200702051500.19196@zmi.at> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200702051622.55632.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/56 X-Sequence-Number: 24521 =CE=A3=CF=84=CE=B9=CF=82 =CE=94=CE=B5=CF=85=CF=84=CE=AD=CF=81=CE=B1 05 =CE= =A6=CE=B5=CE=B2=CF=81=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=82 2007 16:00,= =CE=BF/=CE=B7 Michael Monnerie =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: > 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=3Dbayes_pg3 HOST=3D195.202.170.130(54789) > SESSTRT=3D2007-02-04 05:01:15 CET FEHLER: duplizierter Schl=C3=BCssel > verletzt Unique-Constraint =C2=BBbayes_seen_pkey=C2=AB > > 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= =20 reload but before the UNIQUE KEY DDL was executed, or smth like that, or simply because this bayes_seen value is= =20 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 =3D '*' > port =3D 5432 > max_connections =3D 200 > shared_buffers =3D 50000 > max_prepared_transactions =3D 0 > work_mem =3D 128000 > maintenance_work_mem =3D 128000 > max_fsm_pages =3D 60000 > max_fsm_relations =3D 1000 > max_files_per_process =3D 5000 > vacuum_cost_delay =3D 100 > vacuum_cost_page_hit =3D 1 > vacuum_cost_page_miss =3D 10 > vacuum_cost_page_dirty =3D 20 > vacuum_cost_limit =3D 10000 > bgwriter_delay =3D 1000 > bgwriter_lru_percent =3D 5.0 > bgwriter_lru_maxpages =3D 100 > bgwriter_all_percent =3D 1.333 > bgwriter_all_maxpages =3D 100 > fsync =3D off > wal_buffers =3D 8 > commit_delay =3D 3000 > commit_siblings =3D 25 > checkpoint_segments =3D 4 > checkpoint_timeout =3D 300 > checkpoint_warning =3D 30 > enable_bitmapscan =3D on > enable_hashagg =3D on > enable_hashjoin =3D on > enable_indexscan =3D on > enable_mergejoin =3D on > enable_nestloop =3D on > enable_seqscan =3D on > enable_sort =3D on > enable_tidscan =3D on > effective_cache_size =3D 64000 > geqo =3D on > log_destination =3D 'stderr' > redirect_stderr =3D on > log_directory =3D '/var/log/' > log_filename =3D 'postgresql.%Y' > log_truncate_on_rotation =3D off > log_rotation_age =3D 0 > log_rotation_size =3D 0 > log_min_duration_statement =3D 5000 > log_connections =3D off > log_disconnections =3D off > log_duration =3D off > log_line_prefix =3D '%t DB=3D%d HOST=3D%r SESSTRT=3D%s ' > log_statement =3D 'ddl' > stats_start_collector =3D on > stats_block_level =3D on > stats_row_level =3D on > autovacuum =3D on > autovacuum_naptime =3D 3600 > autovacuum_vacuum_threshold =3D 5000 > autovacuum_analyze_threshold =3D 2500 > autovacuum_vacuum_scale_factor =3D 0.4 > autovacuum_analyze_scale_factor =3D 0.2 > autovacuum_vacuum_cost_delay =3D -1 > autovacuum_vacuum_cost_limit =3D -1 > lc_messages =3D 'de_DE.UTF-8' > lc_monetary =3D 'de_DE.UTF-8' > lc_numeric =3D 'de_DE.UTF-8' > lc_time =3D 'de_DE.UTF-8' > > log_filename =3D 'postgresql.%Y' > > mfg zmi =2D-=20 Achilleas Mantzios