Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6381E9FB9EA for ; Wed, 8 Aug 2007 13:13:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06617-01-5 for ; Wed, 8 Aug 2007 13:13:49 -0300 (ADT) X-Greylist: delayed 00:06:32.003577 by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.238]) by postgresql.org (Postfix) with ESMTP id 755E39FC030 for ; Wed, 8 Aug 2007 13:13:48 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so78196nzf for ; Wed, 08 Aug 2007 09:13:47 -0700 (PDT) Received: by 10.142.50.15 with SMTP id x15mr418704wfx.1186589234159; Wed, 08 Aug 2007 09:07:14 -0700 (PDT) Received: by 10.142.101.19 with HTTP; Wed, 8 Aug 2007 09:07:14 -0700 (PDT) Message-ID: Date: Wed, 8 Aug 2007 12:07:14 -0400 From: "Nick Fankhauser" To: pgsql-admin@postgresql.org Subject: Specific questions about wraparound and vacuum MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4137_26890713.1186589234149" X-Google-Sender-Auth: 360dc3fb4fee59ae X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/78 X-Sequence-Number: 26409 ------=_Part_4137_26890713.1186589234149 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)