Re: Database 'xxxx', OID yyyyy, has disappeared from pg_database - Mailing list pgsql-admin

It worked!

This is a few days after success but I wanted to test out the databases
before I declared victory.

Many thanks Tom. This incident has spurred me to vacuum more often,
backup every day, and RTFM.

See "The Postgres Incident" http://www.mglenn.com/archives/2002/05/01/
for a synopsis.

Thanks again, I'm in your debt.

Tom Lane wrote:

>Michael Glenn <mike@mglenn.com> writes:
>
>
>>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.
>>
>>
>
>If you haven't vacuumed in a long time then it could be pretty messy;
>you will have a problem with not being able to tell aborted transactions
>from committed ones.  If you didn't abort transactions very often then
>maybe the headaches won't be too bad, but otherwise ...
>
>What I would do at this point is:
>
>1. Make a tarball copy of your entire $PGDATA tree, if at all possible.
>This will let you start over when you want to.
>
>2. Use a binary-file editor to insert some very large value (4 billion
>minus a few hundred, perhaps) into nextXid, which is the second 4 bytes
>of the pg_variable file.  (You might want to take note of what's there
>now, too.)
>
>3. Replace pg_log with a file containing 1Gb worth of 0xAA bytes.  This
>will make it look like all your transactions committed.
>
>4. Start postmaster --- it should start happily at this point.
>
>5. Without doing anything else, pg_dumpall.  You have a few hundred
>transactions before everything goes to hell again, so don't waste 'em.
>(You don't want to give yourself too much headroom here, because you
>are certainly losing the effects of every transaction after the nextXid
>you chose.)
>
>6. initdb a fresh installation (might as well move up to 7.2.1 here...),
>try to restore pg_dumpall script into it, settle down to a lot of
>cross-checking to try to validate data.
>
>The reason you will have consistency problems is that some aborted
>transactions will be taken as committed --- maybe in only some of their
>effects, not all.  As a rule of thumb, any tuple that was read by
>another transaction before the wrap happened will be marked with
>the correct commit state.  Anything that hadn't been examined will
>be taken to be committed, which might be wrong.  So the newer and
>more seldom-examined the update, the riskier it will be.  It's very
>likely the pgdump script will not even restore (due to unique-key
>violations) until it's hand-edited, so you might want to dump schema
>and data separately to ease editing.
>
>It might be useful to try this procedure with a few different nextXid
>selections --- that will give you snapshots further and further back
>in the past, with hopefully correspondingly fewer inconsistencies.
>In any case you're in for a lot of no-fun.  Sorry the news is not
>better.
>
>            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-----