Re: Oldest xmin is far in the past

On Sat, Jun 30, 2012 at 4:32 PM, Jerry Sievers <jerry(dot)sievers(at)comcast(dot)net>wrote:

> Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com> writes:
> > 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.
>
> It should resolve the problem of your DB moving towards wrap-around.
>
>
Hi again,

Removing the prepared transactions from the various databases in the
cluster,
and then doing 'vacuumdb -a' , have reduced the xid age:

"SELECT datname, age(datfrozenxid) FROM pg_database;"

datname | age
------------+-----------
template1 | 15111324
template0 | 15110823
postgres | 117559433
db1 | 50012676
db2 | 50018385
db3 | 50010002
db4 | 125206156
db5 | 123204503

> That said; your system may be very bloated and require some of the
> aggressive measures that you already highlighted in your original memo
> to solve for that.
>
>
I could schedule a service window where a logical backup/restore
is combined with a PostgreSQL major version update.

Again, thanks so much for your help in resolving our wraparound situation.

Christian