Re: Oldest xmin is far in the past

From: Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com>
To: Jerry Sievers <gsievers19(at)comcast(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Oldest xmin is far in the past
Date: 2012-06-30 14:22:14
Message-ID: CAMgLkJNg4kncZ3NaTOryzeAPzqZ5oCmNKOaSfC=6dkUAwvSutw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Sat, Jun 30, 2012 at 3:55 PM, Jerry Sievers <gsievers19(at)comcast(dot)net>wrote:

> Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com> writes:
>
> > Hi,
> >
> > Coming back from vacation, I find a PostgreSQL database cluster (Pg
> version: 9.0.4)
> > with 5 databases, which for the past 5 days have logged these messages:
> >
> > ? WARNING:? oldest xmin is far in the past
> > ? HINT:? Close open transactions soon to avoid wraparound problems.
> >
> > The cluster has been running continously for over 9 months with
> autovacuum enabled.
>
> <snip>>
>
> > I cannot find any 'idle in transaction' connected, and I can see one
> 'idle' connection
> > (but not 'idle in transaction') which is almost 24 hours old, the rest
> is from early this morning.
> >
> See if there is a prepared transaction still open. Query
> pg_prepared_xacts.
>
>
Hi,

Many thanks for your reply.

Doing a:

psql -U postgres -d postgres -c "select * from pg_prepared_xacts;"

I see that there are 72 transactions (across the 5 databases in this
cluster) with the 'prepared' values listed as various dates in april 2012,
ie. over 2 months old.

I guess the solution could that for each of the 'gid' values listed from
the query above, do a:

psql -U postgres -d postgres -c "ROLLBACK PREPARED '<gid>';"

And then do a "vacuumdb -a" to see if it fixes the problem.

Thank again,

Christian

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Christian Rosnes 2012-06-30 15:27:34 Re: Oldest xmin is far in the past
Previous Message Jerry Sievers 2012-06-30 13:55:05 Re: Oldest xmin is far in the past