Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E16F22E02ED for ; Sat, 26 Jan 2008 14:10:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 06280-04 for ; Sat, 26 Jan 2008 14:10:38 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 630472E032E for ; Sat, 26 Jan 2008 14:10:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m0QIAnGg008233; Sat, 26 Jan 2008 13:10:49 -0500 (EST) To: Steven Rosenstein cc: pgsql-admin@postgresql.org Subject: Re: Recovering a database in danger of transaction wrap-around In-reply-to: References: <3757.1201288206@sss.pgh.pa.us> <19119.1201295646@sss.pgh.pa.us> Comments: In-reply-to Steven Rosenstein message dated "Fri, 25 Jan 2008 20:02:02 -0500" Date: Sat, 26 Jan 2008 13:10:49 -0500 Message-ID: <8232.1201371049@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/257 X-Sequence-Number: 28031 Steven Rosenstein writes: > 1: datname = "postgres" (typeid = 19, len = 64, typmod = > 8: datvacuumxid = "2146484345" (typeid = 28, len = 4, typmod = -1, > byval = t) > 9: datfrozenxid = "1072742522" (typeid = 28, len = 4, typmod = -1, > byval = t) > ---- > 1: datname = "vsa" (typeid = 19, len = 64, typmod = -1, byval > = f) > 8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) > 9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) > ---- > 1: datname = "template1" (typeid = 19, len = 64, typmod = > -1, byval = f) > 8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) > 9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) > ---- > 1: datname = "template0" (typeid = 19, len = 64, typmod = > -1, byval = f) > 8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) > 9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1, > byval = t) Apparently, "postgres" is the only one of these that has ever had a database-wide VACUUM done on it :-(. A look at an 8.1 database here confirms that 499 is what would be in those fields immediately after initdb, so it's never been changed. > If you look at datvacuumxid and datfrozenxid, they both seem > perfectly reasonable in the vsa database. No, they aren't, at least not for an installation that's existed awhile. > However, the same values in the > "postgres" database are approaching what appear to be the hard limits. "postgres" is approaching the wraparound point, which is by no means a hard limit. The internal transaction counter (which you could check on with pg_controldata) is presumably even closer to the wrap point. > Before I charged ahead and made any changes I wanted to confirm that it was > "datfrozenxid" in the "postgres" database which I should *increment* by a > couple of thousand, and not *datvacuumxid" in the "postgres" database which > should be *decrement* by a couple of thousands. No, you need to leave "postgres" alone and increment the other ones, to make it look like they got vacuumed sometime closer to current time. > I have no idea what the "postgres" database is, where it came from, or why > the transaction IDs are so out of skew. I don't think it is created at > database creation. http://www.postgresql.org/docs/8.1/static/manage-ag-createdb.html > The person responsible for installing Postgres left the > company a few weeks ago and is not available to ask. I checked on another > server with a similar configuration. It has a "postgres" database, but the > values for datvacuumxid and datfrozenxid is the same as the other three > databases: 499 each. Then it's not being managed properly either ... regards, tom lane