Re: Oldest xmin is far in the past

[Christian Rosnes <christian.rosnes@xxxxxxxxx>]

On Sat, Jun 30, 2012 at 4:32 PM, Jerry Sievers <jerry.sievers@xxxxxxxxxxx> wrote:

Christian Rosnes <christian.rosnes@xxxxxxxxx> 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