PITR for postgresql-7.3

On 8/10/07, Mary Ellen Fitzpatrick <mfitzpat@bu.edu> wrote:

Hi,

We are running postgresql-7.3.3 and we had a hardware controller and
disk failure on the system. And of course the database does not appear
to be backup anywhere.

I was reading about PITR and was wondering if that is applicable to my
version. We do have pg_xlog files and I am wondering if there is anyway
to recover the data.

The error we get when trying to access a very important table is

"Invalid page header in block 51 of vuser"

Any help would be appreciated.
(upgrades and backups lessons learned)

PITR feature was introduced in PostgreSQL 8.0 No help there. You are
looking at data loss unfortunately. How complete data loss depends on
how relatively intact the structures are and how much time and effort
you are willing to spend fixing problems causing errors like the
above.

* did you recover part or all of the database volume?
* were you running with fsync off or on?

merlin

On 8/10/07, Mary Ellen Fitzpatrick <mfitzpat@bu.edu> wrote:

Merlin,

I am willing to spend the time, as it is an important table. I am a
newbie at this and it has fallen into my lap.
From what the user tells me, it is only the one table.
Not sure if fsync was running, how can I tell?

check postgresql.conf in the database folder.

merlin

I am trying to run pg_dump on the database with the corrupt table, and
try to restore the database. I also tried to vacuumdb the database and
get the same error.
I get the following error.

pg_dump <database>
pg_dump: query to obtain list of data types failed: PANIC: read of clog
file 100, offset 81920 failed: Success
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Do I have any options for getting around this error?

Thanks
Mary Ellen

Scott Marlowe wrote:

On 8/10/07, Mary Ellen Fitzpatrick <mfitzpat@bu.edu> wrote:

Hi,

We are running postgresql-7.3.3 and we had a hardware controller and
disk failure on the system. And of course the database does not appear
to be backup anywhere.

Ouch, I feel your pain.

I was reading about PITR and was wondering if that is applicable to my
version. We do have pg_xlog files and I am wondering if there is anyway
to recover the data.

Sadly, no. First off you need a backup of the database from some
point in the past, then you need to ship it's wal logs off to some
other machine for storage. Then you need to be running a version of
pgsql that supports replaying those logs, which 7.3 definitely
doesn't.

The error we get when trying to access a very important table is

"Invalid page header in block 51 of vuser"

Sounds like a bad page. Is this access going through an index? If
so, then you might be able to access parts of the table with a where
clause and get most of the database out one select statement at a
time.

Any help would be appreciated.
(upgrades and backups lessons learned)

Just a quick point on the upgrades, there's also updates, which are
even more important. 7.3 is up to 7.3.19 now, and I'm sure there are
some very nasty bugs in a version as old as 7.3.3

It might be worth backing up the db from the file system level and
trying to upgrade your install to 7.3.19 and seeing if that fixes the
problems. Probably won't, but worth a try.

also, I don't recall if 7.3 had the zero damaged pages option, but if
it does, you can use that to get the db more or less up and running
again.

Good luck, and report back on your progress to see if there's anything
else we can do to help.

--
Thanks
Mary Ellen

Mary Ellen Fitzpatrick <mfitzpat@bu.edu> writes:

I am trying to run pg_dump on the database with the corrupt table, and
try to restore the database. I also tried to vacuumdb the database and
get the same error.
I get the following error.

pg_dump <database>
pg_dump: query to obtain list of data types failed: PANIC: read of clog
file 100, offset 81920 failed: Success
server closed the connection unexpectedly

That looks pretty bad --- it implies that there's corruption in the
pg_type system catalog, not only in your user table. Which in turn
suggests that it's not a localized problem but might affect many
different data blocks. I'm afraid that database is probably toast :-(.
An expert might be able to salvage something from it, but only with
great effort, and there'd be no guarantees of getting complete or
consistent data.

regards, tom lane