Re: after using pg_resetxlog, db lost - Mailing list pgsql-performance

"Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca> writes:
> The pg_resetxlog was run as root. It caused ownership problems of
> pg_control and xlog files.
> Now we have no access to the data now through psql.  The data is still
> there under /var/lib/pgsql/data/base/17347  (PWFPM_DEV DB name).  But
> there is no reference to 36 of our tables in pg_class.  Also the 18
> other tables that are reported in this database have no data in them.
> Is there anyway to have the database resync or make it aware of the data
> under /var/lib/pgsql/data/base/17347?
> How can this problem be resolved?

What this sounds like is that you reset the transaction counter along
with the xlog, so that those tables appear to have been created by
transactions "in the future".  This could be repaired by doing
pg_resetxlog with a more appropriate initial transaction ID, but
figuring out what that value should be is not easy :-(

What I'd suggest is grabbing pg_filedump from
http://sources.redhat.com/rhdb/
and using it to look through pg_class (which will be file
$PGDATA/base/yourdbnumber/1259) to see the highest transaction ID
mentioned in any row of pg_class.  Then pg_resetxlog with a value
a bit larger than that.  Now you should be able to see all the rows
in pg_class ... but this doesn't get you out of the woods yet, unless
there are very-recently-created tables shown in pg_class.  I'd suggest
next looking through whichever tables you know to be recently modified
to find the highest transaction ID mentioned in them, and finally doing
another pg_resetxlog with a value a few million greater than that.  Then
you should be okay.

The reason you need to do this in two steps is that you'll need to look
at pg_class.relfilenode to get the file names of your recently-modified
tables.  Do NOT modify the database in any way while you are running
with the intermediate transaction ID setting.

> Jun 22 13:38:23 murphy postgres[1460]: [2-1] ERROR:  xlog flush request
> 210/E757F150 is not satisfied --- flushed only to 0/2074CA0

Looks like you also need a larger initial WAL offset in your
pg_resetxlog command.  Unlike the case with transaction IDs, there's
no need to try to be somewhat accurate in the setting --- I'd just
use a number WAY beyond what you had, maybe like 10000/0.

Finally, the fact that all this happened suggests that you lost the
contents of pg_control (else pg_resetxlog would have picked up the right
values from it).  Be very sure that you run pg_resetxlog under the same
locale settings (LC_COLLATE,LC_CTYPE) that you initially initdb'd with.
Otherwise you're likely to have nasty index-corruption problems later.

Good luck.  Next time, don't let amateurs fool with pg_resetxlog (and
anyone who'd run it as root definitely doesn't know what they're doing).
It is a wizard's tool.  Get knowledgeable advice from the PG lists
before you use it rather than after.

            regards, tom lane