Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database - Mailing list 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@mglenn.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-----