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-WRAPAROUNDimplies 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 [EMAIL PROTECTED] http://www.doxpop.com 765.965.7363 765.962.9788 (Fax) Doxpop - Public Records at Your Fingertips.
