Why after the database suffers transaction ID wraparound would
transactions that were in the past appear to be in the future ?
They don't. The quoted text just explains why postgres needs to use modulo 231 arithmatic (which means transactions can wrap around as long as old transactions are 'frozen' early enough):
Normal XIDs are compared using modulo-2^31 arithmetic. This means that
for every normal XID, there are two billion XIDs that are "older" and
two billion that are "newer"
to be specific:
old row versions must be reassigned the XID FrozenXID sometime before
they reach the two-billion-transactions-old mark
or wrapping the XID around would cause things to break. To prevent that, postgres will start to emit warnings, and eventually shut down and refuse to start new transactons if necessary:
If for some reason autovacuum fails to clear old XIDs from a table,
the system will begin to emit warning messages like this when the
database's oldest XIDs reach ten million transactions from the
wraparound point:
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
(A manual VACUUM should fix the
problem, as suggested by the hint; but note that the VACUUM must be
performed by a superuser, else it will fail to process system catalogs
and thus not be able to advance the database's datfrozenxid.) If these
warnings are ignored, the system will shut down and refuse to start
any new transactions once there are fewer than 1 million transactions
left until wraparound
In other words "transactions that were in the past appear to be in the future" and "data loss" are entirely theoretical and will not be caused by transaction ID wraparound in practice.
First, if you haven't done the first two that vyegorov suggests do it now. If you have already vacuumed, it is not clear what sort of damage you may have already done in this case. This error should not be possible on new versions of PostgreSQL due to a number of checks, and had you upgraded some time ago, this wouldn't have happened. I highly recommend trying to stay on supported branches in the future.
I want to take a moment and describe what likely caused the problem and what it means. The prognosis IMO is not good and recovering the data, if it is even possible, is likely to be expensive and time consuming. I sincerely hope you have a good backup from before the transaction wraparound. If not, ouch....
What Went Wrong
PostgreSQL uses something called MVCC, which means that old versions of rows are kept around until they are clearly no longer used. MVCC as practiced by PostgreSQL stamps each row with a minimum transaction and a maximum transaction, and uses these for visibility management. When a transaction rolls back those rows entered are no longer visible and those rows deleted remain visible. Transaction ID's are 32-bit integers.
Periodically you are supposed to vacuum PostgreSQL instances. This, among other things, manages MVCC so that fewer transaction id's need to be checked, manages free space in tables, and can reset the transaction id sequence.
When the transaction ID wraps around, bad things happen but they boil down to the fact that PostgreSQL can no longer be sure what rows are visible and what rows are not. Note thais applies not only to rows in your own tables but in the system catalogs as well. This is a very, very bad thing. The best approach, if you can do it, is to restore from a backup before the wraparound occurred. This is why databases and tables are not showing up and why vacuuming full duplicates them.
Why Upgrading Would have Fixed This
Modern versions of PostgreSQL come with autovacuum automatically enabled which runs vacuum processes in the background to help prevent this sort of problem. More recent versions also will refuse to start new transactions for non-superusers once wraparound is approaching. This gives you a chance to detect and correct the problem before you suffer possibly catastrophic data loss.
PostgreSQL 7.4 hasn't been supported in nearly three years. I don't know when the last time anything was vacuumed but it must have been billions of transactions ago. This is not good.
Best Answer
It wraps around to FirstNormalTransactionId (which is 3), rather than to zero. And then it continues to increment and be stored into xmin and xmax like normal.