I haven't had any issues with pg_log that I know of. Until this incident
I didn't even know what it did. I did lose a few databases a little over
a year ago but didn't persue it a agressively as this situation because
it wasn't as dire.
The scarry thing is I almost never use vacuum becuase I just plain
forgot a long time ago about it. I didn't realise that it could lead to
such corruption and was under the impression that it was more for
performance than anything else.
I have an old database that was very high in transactions but has been
dormant for over a year now. I thought that 98K seemed way too small for
a transaction log file. Perhaps it was damaged.
Well, it will probably be a few months worth of restoration if there
isn't any other solution, but I guess it serves me right for not reading
the docs more closely. What is the procedure for bumping up the
current-XID counter in pg_variable? Is it theoretically possible to
restore a database from all of it's related files.
Also, are the source code modifications for pg_filedump useful to anyone?
Tom Lane wrote:
>Michael Glenn <mike(at)mglenn(dot)com> writes:
>>[ pg_filedump output ]
>Looking at this, I'm kind of wondering whether you didn't have a
>transaction ID wrap after all. You've got a number of rows here that
>appear to have been touched by quite large transaction numbers,
>> Item 8 -- Length: 80 Offset: 7508 (0x1d54) Flags: USED
>> OID: 109529120 CID: min(0) max(0) XID: min(24597178) max(0)
>> Item 9 -- Length: 89 Offset: 6896 (0x1af0) Flags: USED
>> OID: 133213920 CID: min(0) max(0) XID: min(34149469) max(0)
>and they're marked committed too, which means that some other
>transaction agreed that that XID had gotten committed. You sure
>that there's not anything you've forgotten to tell us about past
>sins with pg_log? There's no way that XID 34149469 could have
>been marked committed unless pg_log were at least 8.5 megabytes.
>What I think you might be able to do as a band-aid solution is to force
>up the current-XID counter, which lives in, hmm, $PGDATA/pg_variable in
>7.0.*. Without the former contents of pg_log this will not give you a
>completely accurate reconstruction of your data, but it should be good
>at least back to the last vacuum, which is a lot better than nothing
>(assuming you were more religious about vacuuming than backups ;-)).
>What do you get from "od -x pg_variable"?
> regards, tom lane
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>
-----END PGP PUBLIC KEY BLOCK-----
In response to
pgsql-admin by date
|Next:||From: Tom Lane||Date: 2002-04-26 00:20:21|
|Subject: Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database |
|Previous:||From: Tom Lane||Date: 2002-04-25 22:34:43|
|Subject: Re: Avoiding transaction ID wrap |