Quick Links

Re: Missing pg_clog files

From:	Carol Walter <walterc(at)indiana(dot)edu>
To:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu>, pgsql-admin(at)postgresql(dot)org
Subject:	Re: Missing pg_clog files
Date:	2008-09-24 13:41:28
Message-ID:	F6EE2346-B706-43CC-947D-B3E27F34B7E4@indiana.edu
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-admin

Yes, as an experienced dba, there ist no excuse for not having the
routine backups. I do it for the other instances but not this one.
It's student contains student databases. The students are required
to do there own backups. The problem is, the database got used for
some non-student data. I should have anticipated that since the
resource is there someone will use it. =) There are three small
databases that I need.

I'm going to create the files again with "0x55". I need 256K hex
characters?

I did try to dump individual databases. Since I need these three
that was the first thing I did.

If I upgrade to the highest version of 8.2, do I still have to do the
dump and restore? That may be a dumb question, but I've seen some
emails that seem to say that I don't.

Here is the results of the query you asked for.

Thank you for all your help.

Carol

On Sep 24, 2008, at 9:10 AM, Tom Lane wrote:

> Carol Walter <walterc(at)indiana(dot)edu> writes:
>> I tried creating the files 0000 through 002F. Pg_dump still will not
>> run. The error was as follows:
>
>> -bash-3.00$ pg_dump -U postgres ebiz > ebiz_bk.sql
>> pg_dump: SQL command failed
>> pg_dump: Error message from server: ERROR: could not access status
>> of transaction 20080015
>> DETAIL: Could not read from file "pg_clog/0013" at offset 32768:
>> Error 0.
>> pg_dump: The command was: COPY ebizd.products_categories
>> (category_id, product_id) TO stdout;
>
> You need to make the files the right size (256K of zeroes).
> A suitable "dd" from /dev/zero will accomplish this on modern
> Unixen (ie, anything that has /dev/zero).
>
> Note that this is by no means a fix, it simply allows pg_dump to
> complete. What you are really doing by filling those files with
> zeroes is saying "assume all these old transactions aborted".
> You *will* have data loss. It will only affect rows that haven't
> been accessed in a very long time (since at least June, looks like)
> but gone is gone.
>
> Another possibility that might be better is to fill the files with
> 0x55, though this is harder since /dev/zero won't help. That would
> force all the old transactions to be considered committed rather than
> aborted. This isn't really better from a consistency standpoint, but
> if you feel that most of your data-altering commands succeed then
> this might give you a closer approximation to the state you want.
>
> The whole thing is pretty troubling because 8.2.x is supposed to
> contain defenses against this type of problem. Could we see
> the contents of "select datname, datfrozenxid from pg_database"?
> Also, have you tried dumping individual databases instead of
> pg_dumpall? (It would be good to experiment with that before
> you start making bogus pg_clog files; once you do that there's
> no going back in terms of recovering the true state of your data.)
>
> regards, tom lane
>
> PS: Since you've evidently got a lot of rows that haven't been
> accessed in months, I conclude that you have not been running
> routine backups. Tut tut. I trust you'll remedy that oversight
> as soon as you get out of the immediate problem.

In response to

Re: Missing pg_clog files at 2008-09-24 13:10:21 from Tom Lane

Responses

Re: Missing pg_clog files at 2008-09-24 15:53:37 from Tom Lane

Browse pgsql-admin by date

	From	Date	Subject
Next Message	Napolean Periathambi	2008-09-24 14:23:02	Postgres client Configuration
Previous Message	Tom Lane	2008-09-24 13:10:21	Re: Missing pg_clog files