Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 5A7F3644370
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed,  9 Nov 2011 09:05:01 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 93607-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed,  9 Nov 2011 13:04:55 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com
	[209.85.220.174])
	by mail.postgresql.org (Postfix) with ESMTP id E641F64436F
	for <pgsql-admin@postgresql.org>; Wed,  9 Nov 2011 09:04:53 -0400 (AST)
Received: by vcbf1 with SMTP id f1so1345443vcb.19
	for <pgsql-admin@postgresql.org>; Wed, 09 Nov 2011 05:04:53 -0800 (PST)
Received: by 10.52.116.237 with SMTP id jz13mr4416965vdb.90.1320843893324;
	Wed, 09 Nov 2011 05:04:53 -0800 (PST)
Received: from ayaki.localdomain (124-169-167-77.dyn.iinet.net.au.
	[124.169.167.77])
	by mx.google.com with ESMTPS id bu10sm7232561vdb.3.2011.11.09.05.04.50
	(version=SSLv3 cipher=OTHER); Wed, 09 Nov 2011 05:04:51 -0800 (PST)
Message-ID: <4EBA7A6F.50602@ringerc.id.au>
Date: Wed, 09 Nov 2011 21:04:47 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: "Ruslan A. Bondar" <fsat@list.ru>
CC: pgsql-admin@postgresql.org
Subject: Re: How to deal with corrupted database?
References: <20111109150225.076c2f38@list.ru>
In-Reply-To: <20111109150225.076c2f38@list.ru>
Content-Type: multipart/alternative;
	boundary="------------050601090404080002030700"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201111/24
X-Sequence-Number: 38618

This is a multi-part message in MIME format.
--------------050601090404080002030700
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 11/09/2011 07:02 PM, Ruslan A. Bondar wrote:
> Hello all.
>
> This is a first time I receive such an issue.
> My database was corrupted some way.
Before you do ANYTHING else, make a copy of your database somewhere 
safe. See:

http://wiki.postgresql.org/wiki/Corruption
> When I'm trying to access the database via psql I receive:
>
> root@udb:/etc/bacula# psql -U bacula
> psql: FATAL:  could not read block 0 in file "base/16387/86057840": read only 0 of 8192 bytes
>
>
> So I want to drop it, and recover from backup. But when I'm trying to drop the database I see:
>
> postgres=# drop database bacula;
> ERROR:  could not access status of transaction 15892843
> DETAIL:  Could not open file "pg_subtrans/00F2": No such file or directory.
>
>
> Is there any way to recover the database to working state, or drop it?
>
*ONLY* once you've made a full backup copy, you may be able to set 
zero_damaged_pages to get a usable dump.

Do you know what caused this? The missing files suggest it was probably 
file system corruption - was there a disk failure? fsck run with errors? 
Unexpected reboot on a RAID controller with a dead backup battery?

--
Craig Ringer

--------------050601090404080002030700
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 11/09/2011 07:02 PM, Ruslan A. Bondar wrote:
    <blockquote cite="mid:20111109150225.076c2f38@list.ru" type="cite">
      <pre wrap="">Hello all.

This is a first time I receive such an issue.
My database was corrupted some way.</pre>
    </blockquote>
    Before you do ANYTHING else, make a copy of your database somewhere
    safe. See:<br>
    <br>
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    <a href="http://wiki.postgresql.org/wiki/Corruption">http://wiki.postgresql.org/wiki/Corruption</a><br>
    <blockquote cite="mid:20111109150225.076c2f38@list.ru" type="cite">
      <pre wrap="">
When I'm trying to access the database via psql I receive:

root@udb:/etc/bacula# psql -U bacula
psql: FATAL:  could not read block 0 in file "base/16387/86057840": read only 0 of 8192 bytes


So I want to drop it, and recover from backup. But when I'm trying to drop the database I see:

postgres=# drop database bacula;
ERROR:  could not access status of transaction 15892843
DETAIL:  Could not open file "pg_subtrans/00F2": No such file or directory.


Is there any way to recover the database to working state, or drop it?

</pre>
    </blockquote>
    *ONLY* once you've made a full backup copy, you may be able to set
    zero_damaged_pages to get a usable dump.<br>
    <br>
    Do you know what caused this? The missing files suggest it was
    probably file system corruption - was there a disk failure? fsck run
    with errors? Unexpected reboot on a RAID controller with a dead
    backup battery?<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------050601090404080002030700--