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

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