Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D3F5C9FA34A for ; Mon, 5 Feb 2007 09:20:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 35682-04 for ; Mon, 5 Feb 2007 09:20:04 -0400 (AST) X-Greylist: delayed 00:27:24.365921 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 3399E9FB2F1 for ; Mon, 5 Feb 2007 09:19:34 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from power2u.goelsen.net (mail.goelsen.net [195.202.170.130]) by svr4.postgresql.org (Postfix) with ESMTP id C6C9C5AF043 for ; Mon, 5 Feb 2007 12:52:08 +0000 (GMT) X-Envelope-From: michael.monnerie@it-management.at Received: from localhost (localhost [127.0.0.1]) by power2u.goelsen.net (Postfix) with ESMTP id D8EDE7749B for ; Mon, 5 Feb 2007 13:51:54 +0100 (CET) Received: from power2u.goelsen.net ([127.0.0.1]) by localhost (power2u.goelsen.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18325-02 for ; Mon, 5 Feb 2007 13:51:45 +0100 (CET) X-Envelope-From: michael.monnerie@it-management.at Received: from mailsrv.i.zmi.at (h081217116113.dyn.cm.kabsi.at [81.217.116.113]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "mailsrv.i.zmi.at", Issuer "mailsrv.i.zmi.at" (not verified)) by power2u.goelsen.net (Postfix) with ESMTP id 6BC9B609A for ; Mon, 5 Feb 2007 13:51:45 +0100 (CET) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: destroyed db/index Date: Mon, 5 Feb 2007 13:51:43 +0100 User-Agent: KMail/1.9.5 MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart7806753.egB77E39bu"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200702051351.43567@zmi.at> X-Virus-Scanned: amavisd-new at goelsen.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200702/54 X-Sequence-Number: 24519 --nextPart7806753.egB77E39bu Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline dear list, I've found database corruptions occuring with openSUSE 10.1, postgreSQL=20 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)=20 SESSTRT=3D2007-02-04 05:01:15 CET FEHLER: duplizierter Schl=FCssel=20 verletzt Unique-Constraint =BBbayes_seen_pkey=AB translated it says "duplicate key violates unique constraint", and it's=20 true, I have 2 rows with the same Primary Key in that table (make dump,=20 then reload fails on it). The DB is a bayes DB for spamassassin. I've had this error before, and=20 dumped/edited dump/reloaded the db. But it keeps coming again. So there=20 might be hardware or software problems. The hardware is a server running one AMD Opteron dual core 2210, with=20 8GB RAM, and VMware. We have www, db, mailserver and mailrelays running=20 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=20 cause such problems? I've set this in postgresql.conf: listen_addresses =3D '*' port =3D 5432 max_connections =3D 200 shared_buffers =3D 50000 =20 max_prepared_transactions =3D 0 =20 work_mem =3D 128000 =20 maintenance_work_mem =3D 128000 =20 max_fsm_pages =3D 60000 =20 max_fsm_relations =3D 1000 =20 max_files_per_process =3D 5000 =20 vacuum_cost_delay =3D 100 =20 vacuum_cost_page_hit =3D 1 =20 vacuum_cost_page_miss =3D 10 =20 vacuum_cost_page_dirty =3D 20 =20 vacuum_cost_limit =3D 10000 =20 bgwriter_delay =3D 1000 =20 bgwriter_lru_percent =3D 5.0 =20 bgwriter_lru_maxpages =3D 100 =20 bgwriter_all_percent =3D 1.333 =20 bgwriter_all_maxpages =3D 100 =20 fsync =3D off =20 wal_buffers =3D 8 =20 commit_delay =3D 3000 =20 commit_siblings =3D 25 =20 checkpoint_segments =3D 4 =20 checkpoint_timeout =3D 300 =20 checkpoint_warning =3D 30 =20 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 =20 geqo =3D on log_destination =3D 'stderr' =20 redirect_stderr =3D on =20 log_directory =3D '/var/log/' =20 log_filename =3D 'postgresql.%Y'=20 log_truncate_on_rotation =3D off=20 log_rotation_age =3D 0 =20 log_rotation_size =3D 0 =20 log_min_duration_statement =3D 5000 =20 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 ' = =20 log_statement =3D 'ddl' =20 stats_start_collector =3D on stats_block_level =3D on stats_row_level =3D on autovacuum =3D on =20 autovacuum_naptime =3D 3600 =20 autovacuum_vacuum_threshold =3D 5000 =20 autovacuum_analyze_threshold =3D 2500 =20 autovacuum_vacuum_scale_factor =3D 0.4 =20 autovacuum_analyze_scale_factor =3D 0.2 =20 autovacuum_vacuum_cost_delay =3D -1 =20 autovacuum_vacuum_cost_limit =3D -1 =20 lc_messages =3D 'de_DE.UTF-8' =20 lc_monetary =3D 'de_DE.UTF-8' =20 lc_numeric =3D 'de_DE.UTF-8' =20 lc_time =3D 'de_DE.UTF-8' =20 mfg zmi =2D-=20 // 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 --nextPart7806753.egB77E39bu Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQBFxyhfui0GDxxv5rARAvhnAKDMPW003OIzZRhqNjDvqHAOz9JHgACg0cOE AvUprd6Wm5kuiQj4sBSN68c= =XYxt -----END PGP SIGNATURE----- --nextPart7806753.egB77E39bu--