From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Recovering a database in danger of transaction wrap-around |
Date: | 2008-01-26 18:10:49 |
Message-ID: | 8232.1201371049@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Monnerie | 2008-01-26 18:50:17 | Syslog to postgresql - need peaks of 5.000/s |
Previous Message | Scott Marlowe | 2008-01-26 02:22:58 | Re: backup including symbolic links? |