Hi-
I have a few specific questions about wraparound that I'm not finding answers for in the Docs or list archives. We're a few versions behind (sorry!) at 7.4.7. Our database is large (100 GB), with most of the rows concentrated in 5 tables, the largest containing rows.
1) Do we need to do a *full* vacuum or just a "lazy" vacuum to take care of the xid wraparound issues? The documentation at
http://www.postgresql.org/docs/7.4/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND implies that a regular vacuum is all that is needed but is not explicit about it. We vacuum nightly, but due to availability requirements almost never do a full vacuum.
2) If a regular (non-full) vacuum will not reset the XID. Will a dump/restore take care of wraparound? We have done this in the past for space reclamation because we seem to be able to dump/restore more quickly than we can do a full vacuum.
3) How can we tell if our attempts to take care of wraparound have worked? I found a note that this select should tell me if we're in trouble:
SELECT datname, age(datfrozenxid) FROM pg_database;
But after a vaccum of both our prod and the template1 database, I get this result:
datname | age
-----------+------------
prod | 1074324475
template1 | 1073742599
template0 | 363178963
(3 rows)
From the docs, I learned that after a vacuum, I should see this number at one billion and get alarmed as I near 2 billion. This results is after about 3 hours of normal activity, and I'm almost one-tenth of the way to 2 billion. This scares me a bit, as it implies that after about 36 hours, I would hit the wall if I don't run vacuum again.
Or... is it the case that by one billion, the docs really mean 2^30 (1073741824) ?
If the select above is not right, what should I be using to track how close we are to wraparound problems?
4) The documentation implies that I need to vacuum *every* database, even if I'm not using them all, so I vacuumed template1, but can't connect to template0. Do I really need to vacuum databases where no transactions are occurring? If so, how to I deal with template0?
Thanks.
-Nick
--
------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com http://www.doxpop.com765.965.7363
765.962.9788 (Fax)
Doxpop - Public Records at Your Fingertips.