Re: Oldest xmin is far in the past

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