Обсуждение: PITR for postgresql-7.3
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) -- Thanks Mary Ellen
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