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