Skip site navigation (1) Skip section navigation (2)

Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database

From: Michael Glenn <mike(at)mglenn(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database
Date: 2002-04-25 23:12:46
Message-ID: 3CC88D6E.9010701@mglenn.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

Thanks

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,
>for instance:
>
>  
>
>> 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?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>  
>


-- 
Michael Glenn
http://www.mglenn.com
416.544.9904

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

mQGiBDi1W3gRBADgkh1Qvms9Qz1zpStGDdSs36K0KGX8mhuXMn21C6cE9DQ4V6d7
S8yQo+fFwos0lV+iIy0vWomq2LI1tiniV46v8cn7OyM0QI2c4IVgNn7h5hwq8Yue
RmdSk9Nc1B8FJjWXZKkAxrZ9UtBRpl1USUnh8NnN0uQ+4pB9QoRK77OFnQCg/8hh
KqMCe7y3FF5jHtSK/pHT308D/ifQNxg97Sp1BQxrOGA07Jj4MbWwZDUZH3h0loSJ
TmqEZU8dykPFu3+MCgiUP6en2b5Qk0r/ayHiS7cOwCMUnsmI2Ys4TC36w2CXIhUh
yt8xWqR8uGJDw3OsRr4bOgOm2rhcBtsCx4CSCZ80ysWcDT2KERg4l9kgP1poI27x
l38MA/9cJiLDhT84+ktmTpwkzDIbBWqBEa9avd28fMLjziJMw6Ak2a5B3lONDBWD
ymd0UHjuuHm3aXDA2xAiZtQRvYpEFKsIOoL6+eMjkDC2VfEjoYOACqSlPxO5lxb4
oINcoxBWHjPMWIjS29LydC+OyVZW0RfCqLyANEZFqZT0lkJPI7QfTWljaGFlbCBH
bGVubiA8bWlrZUBtZ2xlbm4uY29tPokATgQQEQIADgUCOLVbeAQLAwIBAhkBAAoJ
EG/6eNodQfh3VAQAoOiLvTGsq59pmWUh9XdjjU4SS3aUAJ4ywgkjUkmUJ5ImThp2
3k1rEax3i7kCDQQ4tVt4EAgA9kJXtwh/CBdyorrWqULzBej5UxE5T7bxbrlLOCDa
AadWoxTpj0BV89AHxstDqZSt90xkhkn4DIO9ZekX1KHTUPj1WV/cdlJPPT2N286Z
4VeSWc39uK50T8X8dryDxUcwYc58yWb/Ffm7/ZFexwGq01uejaClcjrUGvC/RgBY
K+X0iP1YTknbzSC0neSRBzZrM2w4DUUdD3yIsxx8Wy2O9vPJI8BD8KVbGI2Ou1WM
uF040zT9fBdXQ6MdGGzeMyEstSr/POGxKUAYEY18hKcKctaGxAMZyAcpesqVDNmW
n6vQClCbAkbTCD1mpF1Bn5x8vYlLIhkmuquiXsNV6TILOwACAgf9H57D7zhr/7t6
IxbxBMCFy4EHkD5awkfpxSjv0kLV4AQsXfdk1LsEJLFp8WLquo2ftPpSsMR2vYMa
qtyK6FB9I+wq8h76m2RsHoAYVeXYpxckbXtNstz/9qENctdYn2f190v46FCxun3c
IghoP4rBnJQNOQIcSQWSEWyOfFjCSlEv/7RSS3cL19r7OE0m7yJprBvAmav+TuHM
UmG4Etdi89VEiMYXeFV08CmT12xt+Lel7/YpDlcFBQEjy810nb19w2HCOxh/JMEO
C5gXXKMMgtWUTfY/gw9oZOXSy5EffcLjk4jJ2J3FJSwqhOQgBKi0l8I4r1QMmOdE
9WPpnDrHrYkARgQYEQIABgUCOLVbeAAKCRBv+njaHUH4d547AJ9ze+4zXlsv/NdX
GhsLTWp+BOQEvACgqEN5l2RiBVEYJDx1ktz9cSuvcrI=
=KXHB
-----END PGP PUBLIC KEY BLOCK-----




In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2002-04-26 00:20:21
Subject: Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database
Previous:From: Tom LaneDate: 2002-04-25 22:34:43
Subject: Re: Avoiding transaction ID wrap

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group