Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C340A2E0141 for ; Wed, 20 Feb 2008 15:15:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 70101-01-9 for ; Wed, 20 Feb 2008 15:15:35 -0400 (AST) Received: from out3.smtp.messagingengine.com (out3.smtp.messagingengine.com [66.111.4.27]) by postgresql.org (Postfix) with ESMTP id CEED22E00EB for ; Wed, 20 Feb 2008 15:14:51 -0400 (AST) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id E621A931F4; Wed, 20 Feb 2008 14:14:48 -0500 (EST) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Wed, 20 Feb 2008 14:14:48 -0500 X-Sasl-enc: /Kf8+5TeIzCvBmqAF9SV1v9J3E8ju8cbFjp+6SZnOZ8W 1203534887 Received: from [192.168.1.235] (76-191-150-176.dsl.dynamic.sonic.net [76.191.150.176]) by mail.messagingengine.com (Postfix) with ESMTPSA id 14D181C138; Wed, 20 Feb 2008 14:14:46 -0500 (EST) Message-ID: <47BC7C24.9040606@fastmail.net> Date: Wed, 20 Feb 2008 11:14:44 -0800 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: Power outage borked things (8.1.10)... References: <47BBF298.4010108@fastmail.net> <8625.1203523828@sss.pgh.pa.us> In-Reply-To: <8625.1203523828@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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: 200802/232 X-Sequence-Number: 28306 Tom Lane wrote: > Darren Reed writes: > > Starting up postgres, I get the log contents below. > > Is it really as bad as it suggests, namely that I > > need to recover from backup? > > Probably :-( I've started a new db in parallel with the old data and I'm rebuilding, and if I can rebuild quicker than I can recover old data, I'll do that. > pg_resetxlog would let you into the database, but I do not have high > hopes about the consistency/correctness of what you'll find. The best > advice would be: > > 1. pg_resetxlog # su postgres -c "/usr/pkg/bin/pg_resetxlog -n /data/db" pg_control values: pg_control version number: 812 Catalog version number: 200510211 Database system identifier: 5138205682483264479 Current log file ID: 2 Next log file segment: 103 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 4570963 Latest checkpoint's NextOID: 24576 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 Maximum length of identifiers: 64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name: 128 LC_COLLATE: C LC_CTYPE: C # su postgres -c "/usr/pkg/bin/pg_resetxlog -f /data/db" Transaction log reset And a start is greated with: LOG: database system was shut down at 2008-02-20 11:04:51 PST LOG: checkpoint record is at 2/6A00001C LOG: redo record is at 2/6A00001C; undo record is at 2/6A00001C; shutdown TRUE LOG: next transaction ID: 4570963; next OID: 24576 LOG: next MultiXactId: 1; next MultiXactOffset: 0 PANIC: could not access status of transaction 4570963 DETAIL: could not read from file "pg_clog/0004" at offset 90112: Undefined error: 0 LOG: startup process (PID 29662) was terminated by signal 6 LOG: aborting startup due to startup process failure > > 2. pg_dumpall > > 3. initdb, restore from backup > > 4. compare dump from step 2 to backup dump, apply any changes that seem > sane > > And don't forget > > 5. Figure out why a simple power failure was able to do this to you, > and fix it. The most likely bet is that your disk drives are lying > about write completion ... see the PG archives for discussion. Yes, that I can believe. Darren