Re: Recovering a database in danger of transaction wrap-around - Mailing list pgsql-admin

Steven Rosenstein <srosenst@us.ibm.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