Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

Next:From: Michael MonnerieDate: 2008-01-26 18:50:17
Subject: Syslog to postgresql - need peaks of 5.000/s
Previous:From: Scott MarloweDate: 2008-01-26 02:22:58
Subject: Re: backup including symbolic links?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group