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

Incrementing pg_database.datfrozenxid sounded like an excellent idea.
Before I made any changes I dumped the contents of pg_database to see what
it contained. Here is that dump:

backend> select * from pg_database;
WARNING: database "vsa" must be vacuumed within 997398 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"vsa".
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: datdba (typeid = 26, len = 4, typmod = -1, byval = t)
3: encoding (typeid = 23, len = 4, typmod = -1, byval = t)
4: datistemplate (typeid = 16, len = 1, typmod = -1, byval =
t)
5: datallowconn (typeid = 16, len = 1, typmod = -1, byval =
t)
6: datconnlimit (typeid = 23, len = 4, typmod = -1, byval =
t)
7: datlastsysoid (typeid = 26, len = 4, typmod = -1, byval =
t)
8: datvacuumxid (typeid = 28, len = 4, typmod = -1, byval =
t)
9: datfrozenxid (typeid = 28, len = 4, typmod = -1, byval =
t)
10: dattablespace (typeid = 26, len = 4, typmod = -1, byval =
t)
11: datconfig (typeid = 1009, len = -1, typmod = -1, byval = f)
12: datacl (typeid = 1034, len = -1, typmod = -1, byval = f)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod =
-1, byval = f)
2: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval =
t)
3: encoding = "8" (typeid = 23, len = 4, typmod = -1, byval =
t)
4: datistemplate = "f" (typeid = 16, len = 1, typmod = -1, byval =
t)
5: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval =
t)
6: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval =
t)
7: datlastsysoid = "10792" (typeid = 26, len = 4, typmod = -1,
byval = t)
8: datvacuumxid = "2146484345" (typeid = 28, len = 4, typmod = -1,
byval = t)
9: datfrozenxid = "1072742522" (typeid = 28, len = 4, typmod = -1,
byval = t)
10: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1,
byval = t)
----
1: datname = "vsa" (typeid = 19, len = 64, typmod = -1, byval
= f)
2: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval =
t)
3: encoding = "0" (typeid = 23, len = 4, typmod = -1, byval =
t)
4: datistemplate = "f" (typeid = 16, len = 1, typmod = -1, byval =
t)
5: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval =
t)
6: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval =
t)
7: datlastsysoid = "10792" (typeid = 26, len = 4, typmod = -1,
byval = t)
8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
10: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1,
byval = t)
----
1: datname = "template1" (typeid = 19, len = 64, typmod =
-1, byval = f)
2: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval =
t)
3: encoding = "8" (typeid = 23, len = 4, typmod = -1, byval =
t)
4: datistemplate = "t" (typeid = 16, len = 1, typmod = -1, byval =
t)
5: datallowconn = "t" (typeid = 16, len = 1, typmod = -1, byval =
t)
6: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval =
t)
7: datlastsysoid = "10792" (typeid = 26, len = 4, typmod = -1,
byval = t)
8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
10: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1,
byval = t)
12: datacl = "{postgres=CT/postgres}" (typeid = 1034, len = -1,
typmod = -1, byval = f)
----
1: datname = "template0" (typeid = 19, len = 64, typmod =
-1, byval = f)
2: datdba = "10" (typeid = 26, len = 4, typmod = -1, byval =
t)
3: encoding = "8" (typeid = 23, len = 4, typmod = -1, byval =
t)
4: datistemplate = "t" (typeid = 16, len = 1, typmod = -1, byval =
t)
5: datallowconn = "f" (typeid = 16, len = 1, typmod = -1, byval =
t)
6: datconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval =
t)
7: datlastsysoid = "10792" (typeid = 26, len = 4, typmod = -1,
byval = t)
8: datvacuumxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
9: datfrozenxid = "499" (typeid = 28, len = 4, typmod = -1,
byval = t)
10: dattablespace = "1663" (typeid = 26, len = 4, typmod = -1,
byval = t)
12: datacl = "{postgres=CT/postgres}" (typeid = 1034, len = -1,
typmod = -1, byval = f)
----

The database which contains all the working schemas and tables is the one
called "vsa". If you look at datvacuumxid and datfrozenxid, they both seem
perfectly reasonable in the vsa database. However, the same values in the
"postgres" database are approaching what appear to be the hard limits.
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. My intuition says the
value should be decreased so that that the absolute value is more than
1,000,000 less than 2^31, but of course I might be wrong.

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. 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.
___________________________________________________________________________________

Steven Rosenstein
IT Architect/Developer | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com

"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

To: Steven Rosenstein/New York/IBM(at)IBMUS

Cc: pgsql-admin(at)postgresql(dot)org

Date: 01/25/2008 04:14 PM

Subject: Re: [ADMIN] Recovering a database in danger of transaction wrap-around

Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com> writes:
> I used plain old VACUUM. Do you think VACUUM FULL might be faster or
more
> effective?

No. I think you probably want to do a dump and reload, but first you
have to get past the anti-wraparound check.

One possibility I hadn't thought of before is to use a standalone
backend to increment the pg_database.datfrozenxid values by a few
thousand transactions. This would be a bad idea if you intended
to keep using the DB, but if you're just trying to get to a state
where you can run pg_dump, it seems acceptable.

regards, tom lane