On Sat, Jun 30, 2012 at 4:32 PM, Jerry Sievers
<jerry.sievers@comcast.net> wrote:
> ? 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
