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