Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 2C01037BFDE
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  3 Oct 2008 09:42:32 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 59425-02 for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  3 Oct 2008 09:42:24 -0300 (ADT)
X-Greylist: delayed 00:13:15.294384 by SQLgrey-1.7.6
Received: from smtp182.iad.emailsrvr.com (smtp182.iad.emailsrvr.com
	[207.97.245.182])
	by postgresql.org (Postfix) with ESMTP id 1705737BFD3
	for <pgsql-admin@postgresql.org>; Fri,  3 Oct 2008 09:42:25 -0300 (ADT)
Received: from relay8.relay.iad.mlsrvr.com (localhost [127.0.0.1])
	by relay8.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 97DBD1C6C07;
	Fri,  3 Oct 2008 08:29:06 -0400 (EDT)
Received: from witch-spirit.com (webmail15.webmail.iad.mlsrvr.com
	[192.168.1.39])
	by relay8.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 913091C704D;
	Fri,  3 Oct 2008 08:29:06 -0400 (EDT)
Received: by webmail.mailtrust.com
	(Authenticated sender: antonis@witch-spirit.com,
	from: antonis@witch-spirit.com)
	with HTTP; Fri, 3 Oct 2008 15:29:06 +0300 (EEST)
Date: Fri, 3 Oct 2008 15:29:06 +0300 (EEST)
Subject: Try to recover data
From: antonis@witch-spirit.com
To: pgsql-admin@postgresql.org
Cc: ygolde@gmail.com
MIME-Version: 1.0
Content-Type: text/plain;charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
X-Priority: 3 (Normal)
X-Type: plain
Message-ID: <1223036946.581829536@192.168.1.202>
X-Mailer: webmail6.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: 200810/12
X-Sequence-Number: 30468

Hi,=0A=0A=0AWe had sata raid controller failure causing one dead harddrive =
and one=0Athat we managed to get all the data from in some experts lab.=0AW=
e were running postgres 8.2 using the debian (codename lenny) package 8.2=
=0ASince the data is much more important to us then its integrity we=0Adeci=
ded not to use a 2 months old dump, and try to somehow recover it.=0A=0AWe =
did the following:=0A(lets assume that the database name was "xx")=0A=0A1. =
dumped only the schema of the 2 month old database we had working,=0A2. ins=
talled postgres 8.2.10=0A3. initialized the database with initdb=0A4. creat=
ed superuser "xx" - with createuser=0A5. created database "xx" with user "x=
x" - with createdb=0A6. imported the schema to database "xx" -  psql xx -Ux=
x <=0A/var/only_schema.sql=0A (there were no errors while importing the sch=
ema)=0A7. next thing we did was renaming the new directory in PGDATA/base/1=
7355=0Aof database "xx" to /base/17355_old=0A8. we copied the entire databa=
se from the recovery we had (the one from=0Athe harddisk failure) to /base/=
17355=0A=0Aat this point we started postgres, and it gave an error that sai=
d we=0Aneed to run pg_resetxlog.=0Ain order to find the last transaction fo=
r it, we found the lsat=0Atransaction id entry in the syslog:=0ASep 30 14:1=
2:26 xx3 postgres[20848]: [5-1] 2008-09-30 14:12:26 GMT LOG:  =0Anext trans=
action ID: 0/2306892955; next OID: 10414384=0A=0Awe did as postgres user:=
=0A# /usr/local/pgsql/bin/pg_resetxlog -x 2306892956 -f /var/db6/data=0Aa s=
uccess message was written, and we started postgres:=0Apsql -Uxx xx=0Anow t=
he funny things started to happen-=0Axx=3D>\d=0ANo relations found.=0Axx=3D=
>\d players=0A(gives us the structure of the table).=0A=0AWe can see some r=
ecords from the tables,=0Axx=3D>select * from players limit 10;=0A<correct =
data is given, except that for some reason the timestamp became=0A2000-01-0=
 for all the records, but we can live with that for now>=0Abut when doing=
=0Axx=3D>select * from players;=0AERROR:  invalid page header in block 2885=
 of relation "players"=0Awe tried to use copy to get the data out, but at s=
ome point it reached=0Athat record and died.=0Athis thing happens in most o=
f tables.=0A=0Ais there anyway to resolve this page header issue, tell post=
gres to=0Aignore it, or anything like that?=0A=0AMany Thanks=0A=0A