pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Rosenstein <srosenst@us.ibm.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Recovering a database in danger of transaction wrap-around
Date: Sat, 26 Jan 2008 13:10:49 -0500
Message-ID: <8232.1201371049@sss.pgh.pa.us> (raw)
In-Reply-To: <OF6B73AADC.D70D7474-ON852573DC.00041879-852573DC.0005AE00@us.ibm.com>
References: <OF9BE59CFF.E7484997-ON852573DB.006729F4-852573DB.0067D0FC@us.ibm.com>
	<3757.1201288206@sss.pgh.pa.us>
	<OF0106AD58.4DA91085-ON852573DB.0070DE7B-852573DB.007213D8@us.ibm.com>
	<19119.1201295646@sss.pgh.pa.us>
	<OF6B73AADC.D70D7474-ON852573DC.00041879-852573DC.0005AE00@us.ibm.com>

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



view thread (8+ messages)

Message-ID: <8232.1201371049@sss.pgh.pa.us>
Permalink:  ../8232.1201371049@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/8232.1201371049@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, srosenst@us.ibm.com
  Subject: Re: Recovering a database in danger of transaction wrap-around
  In-Reply-To: <8232.1201371049@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox