Date: Thu, 17 Jul 2014 14:16:35 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: John Scalia <jayknowsunix@gmail.com>
Cc: pgsql-admin@postgresql.org
Message-ID: 
 <984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com>
In-Reply-To: <53C81F98.9070405@gmail.com>
References: <53C81F98.9070405@gmail.com>
Subject: Re: Trying to recover a corrupted database
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----=_Part_512123_1538703047.1405624595838"
Thread-Topic: Trying to recover a corrupted database
Thread-Index: re6OJrUJR5xjCdyDHuRP15+Ig7B3Gw==
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

------=_Part_512123_1538703047.1405624595838
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

a) How many databases do you have? 
b) Have you tried to pg_dump the database(s) to see if that succeeds? 

If you _can_ pg_dump (which you might or might not be able to do), you could re-init the cluster and restore. 

----- Original Message -----

> Hi all,

> You may have seen my post from yesterday about our production database
> getting corrupted. Well, this morning we brought the system down to single
> user and ran an fsck which did
> report some drive errors. We repeated until no additional errors were
> reported. Then, we brought the system back to multi-user status and ran a
> successful pg_basebackup on the
> broken database. Since then we restarted the database and a ps -ef result
> looks like:

> /usr/pgsql-9.2/bin/postmaster -D /opt/datacenter -o -c
> zero_damaged_pages=true -i -N 384 -p 5431

> After the Db started up, we ran a VACUUM FULL ANALYZE which ran for about 3
> hours, but the database is still showing the same type of errors in its log:
> invalid page header in
> block 29718... etc. What disturbed me a little, is that I don't think the
> zero_damaged_pages got applied. Checking the pg_settings table, we got:

> select name, setting, boot_val, reset_val from pg_settings where name =
> 'zero_damaged_pages';
> name | setting | boot_val | reset_val
> ---------------------------------------------------------------------------------
> zero_damaged_pages | on | off | on

> Now, my colleague ran this after he tried running some operations again after
> I told him how to set zero_damaged_pages again. He swears that that it was
> on when the first VACUUM
> FULL ANALYZE was run, but I'm not as sure. Plus, I don't understand why the
> boot_val shows as off. In any event, as we're still getting log errors like
> before, I don't really know
> what to try next other than rerunning the VACUUM FULL again. Help?
> --
> Jay

> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

------=_Part_512123_1538703047.1405624595838
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: times new roman, new york, times, se=
rif; font-size: 12pt; color: #000000"><div><div>a) How many databases do yo=
u have?</div><div>b) Have you tried to pg_dump the database(s) to see if th=
at succeeds?</div><div><br></div><div>If you _can_ pg_dump (which you might=
 or might not be able to do), you could re-init the cluster and restore.</d=
iv></div><div><br></div><hr id=3D"zwchr"><blockquote style=3D"border-left:2=
px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:no=
rmal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,san=
s-serif;font-size:12pt;">Hi all,<br><div><br></div>You may have seen my pos=
t from yesterday about our production database getting corrupted. Well, thi=
s morning we brought the system down to single user and ran an fsck which d=
id <br>report some drive errors. We repeated until no additional errors wer=
e reported. Then, we brought the system back to multi-user status and ran a=
 successful pg_basebackup on the <br>broken database. Since then we restart=
ed the database and a ps -ef result looks like:<br><div><br></div>/usr/pgsq=
l-9.2/bin/postmaster -D /opt/datacenter -o -c zero_damaged_pages=3Dtrue -i =
-N 384 -p 5431<br><div><br></div>After the Db started up, we ran a VACUUM F=
ULL ANALYZE which ran for about 3 hours, but the database is still showing =
the same type of errors in its log: invalid page header in <br>block 29718.=
.. etc. What disturbed me a little, is that I don't think the zero_damaged_=
pages got applied. Checking the pg_settings table, we got:<br><div><br></di=
v>select name, setting, boot_val, reset_val from pg_settings where name =3D=
 'zero_damaged_pages';<br>&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp; &nbsp; &nbsp; name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp;=
setting &nbsp;| &nbsp;boot_val &nbsp;| reset_val<br>-----------------------=
----------------------------------------------------------<br>&nbsp;&nbsp;z=
ero_damaged_pages &nbsp;| &nbsp;on &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| off =
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| on<br><div><br></div>Now,=
 my colleague ran this after he tried running some operations again after I=
 told him how to set zero_damaged_pages again. He swears that that it was o=
n when the first VACUUM <br>FULL ANALYZE was run, but I'm not as sure. Plus=
, I don't understand why the boot_val shows as off. In any event, as we're =
still getting log errors like before, I don't really know <br>what to try n=
ext other than rerunning the VACUUM FULL again. Help?<br>--<br>Jay<br><div>=
<br></div><br><div><br></div>-- <br>Sent via pgsql-admin mailing list (pgsq=
l-admin@postgresql.org)<br>To make changes to your subscription:<br>http://=
www.postgresql.org/mailpref/pgsql-admin<br></blockquote><div><br></div></di=
v></body></html>
------=_Part_512123_1538703047.1405624595838--