In a PostgreSQL 8.0.4 environment, does dropping the database and reloading it
completely reset the XID information that causes transaction ID wraparound? (I
am assuming that it does, but wanted to double-check before doing this.)
From what I can tell, I am just about to hit the XID wraparound problem, given
the following results:
SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
-----------+------------
datadb | 2002912692
template1 | 1034177089
template0 | 1034177089
(3 rows)
I know that a VACUUM FULL will fix this, but on this database a VACUUM FULL
usually takes more than 48 hrs to complete and [more importantly], a VACUUM FULL
usually crashes the PostgreSQL engine. (We _very_ frequently crash with a '
could not rename "d:/pgsql/data/pg_xlog/0000000100000504000000D5" to
"d:/pgsql/data/pg_xlog/0000000100000504000000E8", continuing to try ' error just
with our normal volume of database activity; running any decent-sized VACUUM is
almost always guaranteed to crash at some point with this error or with one of
the "could not fsync" or "could not unlink" errors.)
So ... I'm looking for a viable alternative to a VACUUM FULL to prevent the
impending transaction wraparound. (Note that I _have_ been doing vacuums on all
of the individual tables in this database every week but I don't think this
helps prevent the XID wraparound problem in a PG 8.0.x environment? [By
vacuuming the tables one at a time we've managed to get a 30-40% success rate of
getting through vacuuming all the tables without the database crashing.])
I do understand that this version of PostgreSQL is very old; however, at the
moment I am just looking for an immediate solution to this problem. (The reasons
for it still being at this old version are long and at some point need to be the
subject of another posting. We are looking at moving it to PG 8.3.x since the
"rename" problem appears to finally be fixed; however, we need to wait until
8.3.2 or later because much of the database access is via JDBC.)
Thanks in advance for any insights...
- Bill
