Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DE6F8633061 for ; Tue, 17 Feb 2009 12:03:45 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14176-08 for ; Tue, 17 Feb 2009 12:03:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198]) by mail.postgresql.org (Postfix) with ESMTP id 5E51363303A for ; Tue, 17 Feb 2009 12:03:43 -0400 (AST) Received: from webmail.zmi.at (websrv1.zmi.at [212.69.162.199]) by mailsrv1.zmi.at (Postfix) with ESMTP id 03B3D405E; Tue, 17 Feb 2009 17:03:42 +0100 (CET) Received: from phpmailer ([195.202.170.131]) by webmail.zmi.at with HTTP (PHPMailer); Tue, 17 Feb 2009 17:03:42 +0100 Date: Tue, 17 Feb 2009 17:03:42 +0100 To: Achilleas Mantzios , pgsql-admin@postgresql.org From: Michael Monnerie Subject: Re: 8.3.5 broken after power fail Message-ID: X-Priority: 3 X-Mailer: PHPMailer [version 1.71] X-Mailer: FeLaMiMail MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/121 X-Sequence-Number: 31342 "Achilleas Mantzios" schrieb: >> That runs now since 33 CPU minutes, using 100% with no visible I/O >> happening. top says: >> 29687 postgres 20 0 605m 187m 3024 R 100 15.4 32:58.65 postmaster >>=20 >> And "strace -p 29687" displays nothing. Could it be the process hangs? = It >> shouldn't take that long I guess, but I'm not sure how to find out if it's >> borked or really working... ideas? >=20 > iostat systat vmstat report anything? Sorry, I wasn't detailed enough. With "no visible I/O" I meant I looked = with iostat. After 45 CPU minutes I tried to stop it - no chance. Stop postgres - no chance. Wow, a really hard problem. Had to reboot the VM. Can I somehow dump that table without using an index? Here's the info about \d dbmail_messageblks Tabelle = =C2=BBpublic.dbmail_messageblks=C2=AB Spalte | Typ | Attribute -----------------+----------+---------------------------------------------= --------------------- messageblk_idnr | bigint | not null default nextval('dbmail_messageblk_idnr_seq'::regclass) physmessage_id | bigint | messageblk | bytea | not null blocksize | bigint | not null default (0)::bigint is_header | smallint | not null default (0)::smallint Indexe: =C2=BBdbmail_messageblks_pkey=C2=AB PRIMARY KEY, btree = (messageblk_idnr) =C2=BBdbmail_messageblks_1=C2=AB UNIQUE, btree (physmessage_id, = messageblk_idnr) CLUSTER =C2=BBdbmail_messageblks_physmessage_is_header_idx=C2=AB btree = (physmessage_id, is_header) Fremdschl=C3=BCssel-Constraints: =C2=BBdbmail_messageblks_physmessage_id_fkey=C2=AB FOREIGN KEY = (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE This works: COPY public.dbmail_messageblks (messageblk_idnr, physmessage_id, = blocksize, is_header) TO stdout; This does NOT work: COPY public.dbmail_messageblks (messageblk_idnr, physmessage_id, = messageblk, blocksize, is_header) TO stdout; So I'm sure the "messageblk" toast got toasted too much. Now I need a way = to get out all values that are readable and piss on the rest. I looked into = the output of the first and second COPY, it should be = messageblk_idnr=3D3904492 that's broken. I tried select * from dbmail_messageblks where messageblk_idnr not in (3904492); That helps. Slowly I may be able to find all broken entries. Is there a = more automated way? mfg zmi