Hi !

Well, on an old 7.4 postgres the vacuum failed for some time without
us noticing it, and now some data is hidden because the XID wrapped.

We would like to recover these data, and we plan to do this :

- first, vacuum freeze to freeze all but future XID
- then, artificially advance current XID by executing 1G times
"begin;end;".
- then, vacuum freeze again
- again, advance current XID by 1G and vacuum freeze.

As I understand it, we should then have recovered all rows that were
in future.

What do you think of this plan ?

Also, why am I unable to find a command that freeze _all_ rows which
XID is >=3 ? This would be helpfull in cases like this. But maybe I
misunderstand how XID works ?

rixed (AT) happyleptic (DOT) org wrote:
I have no experience with 7.4 or transaction wraparound, but as a
general advice I recommend that you first of all make a file system
backup of the data directory after shutting down the server.

Other than that, I did a quick archive search and came up with this:
http://archives.postgresql.org/pgsql...2/msg01163.php

Maybe a simple VACUUM is enough...

This also looked interesting:
http://archives.postgresql.org/pgsql...2/msg00412.php

Yours,
Laurenz Albe