On Sat, Jun 30, 2012 at 3:55 PM, Jerry Sievers <gsiever...@comcast.net>wrote:
> Christian Rosnes <christian.ros...@gmail.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
