Hi,
We had sata raid controller failure causing one dead harddrive and one
that we managed to get all the data from in some experts lab.
We were running postgres 8.2 using the debian (codename lenny) package 8.2
Since the data is much more important to us then its integrity we
decided not to use a 2 months old dump, and try to somehow recover it.
We did the following:
(lets assume that the database name was "xx")
1. dumped only the schema of the 2 month old database we had working,
2. installed postgres 8.2.10
3. initialized the database with initdb
4. created superuser "xx" - with createuser
5. created database "xx" with user "xx" - with createdb
6. imported the schema to database "xx" - psql xx -Uxx <
/var/only_schema.sql
(there were no errors while importing the schema)
7. next thing we did was renaming the new directory in PGDATA/base/17355
of database "xx" to /base/17355_old
8. we copied the entire database from the recovery we had (the one from
the harddisk failure) to /base/17355
at this point we started postgres, and it gave an error that said we
need to run pg_resetxlog.
in order to find the last transaction for it, we found the lsat
transaction id entry in the syslog:
Sep 30 14:12:26 xx3 postgres[20848]: [5-1] 2008-09-30 14:12:26 GMT LOG:
next transaction ID: 0/2306892955; next OID: 10414384
we did as postgres user:
# /usr/local/pgsql/bin/pg_resetxlog -x 2306892956 -f /var/db6/data
a success message was written, and we started postgres:
psql -Uxx xx
now the funny things started to happen-
xx=>\d
No relations found.
xx=>\d players
(gives us the structure of the table).
We can see some records from the tables,
xx=>select * from players limit 10;
<correct data is given, except that for some reason the timestamp became
2000-01-0 for all the records, but we can live with that for now>
but when doing
xx=>select * from players;
ERROR: invalid page header in block 2885 of relation "players"
we tried to use copy to get the data out, but at some point it reached
that record and died.
this thing happens in most of tables.
is there anyway to resolve this page header issue, tell postgres to
ignore it, or anything like that?
Many Thanks