Re: Recovering a database in danger of transaction wrap-around

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

In response to

Browse pgsql-admin by date

  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?