Thread: Transaction id wraparound questions
It seems that we have not been vacuuming our production database properly. We have been explicitly vacuuming all tables individually but have not vacuumed the entire database. A recent vacuum of the entire database gave us the dreaded "You may have already suffered transaction-wraparound data loss." warning. We have so far encountered no problems but I am wondering about the safest course of action right now. We cannot easily take an outage to perform a full dump and restore. Questions: 1) What is likely to happen if we encounter transaction id wraparound? 2) Will a full database vacuum fix the problem? 3) Can it make things worse? 4) Other than dump and restore, what options do we have? Information: This query: select datname, datvacuumxid, datfrozenxid from pg_database; returns this: "Production",1173213507,2246955329 "template1", 938856359, 938856359 "template0", 427, 427 All responses welcomed. __ Marc Munro
Attachment
Marc Munro <marc@bloodnok.com> writes:
> A recent vacuum of the entire database gave us the dreaded
> "You may have already suffered transaction-wraparound data loss."
> warning.
If you have in fact been vacuuming *every* table including all the
system catalogs, then you don't need to panic; this message just
indicates that the system doesn't know you're safe.
On the other hand, if you haven't ...
> 2) Will a full database vacuum fix the problem?
If it's fixable. I would recommend that you do this IMMEDIATELY,
rather than dithering, as every transaction you execute is taking
you one step closer to disaster.
> 3) Can it make things worse?
> 4) Other than dump and restore, what options do we have?
If you can conveniently take a full dump, that might give you some
measure of protection, but I'm not sure. I think that if there is
anything that's already wrapped around, pg_dump will not see it :-(
regards, tom lane